Import Txt Data

[JICGreg]

I'm importing from a txt file that is a data export from another program. It is a list of mutual funds. Three of the columns are Return3Year, Return5year, and Return10Year. 20,000 plus records.

Now, if there is no data for a particular column the default export is to put a single hyphen in the cell. When I import into foxpro (numeric field) that hyphen gets translated into a 0.00.

I have done a search and replace on the hypehn and have replaced it with both NULL and .NULL. Each time, it gets imported as a 0.00.

What should I replace the hyphen with so that foxpro would insert a .NULL. instead of a 0.00?

Thanks
Greg

 

[Olaf Doschke]

I think you're out of luck. If I test the other way around, an export from DBF to TXT of 0 vs NULL into SDF or CSV or Delimited text output formats, .NULL. does export as 0.000.

Your chance is to import into a temporary cursoir with a text field instead and convert from there to numeric via CAST(IIF(Alltrim(txtfield)=="-",.NULL.,Eval(txtfield)) AS B)

Bye, Olaf.

 

[DSummZZZ]

You may be able to use BLANK after the import. Something like this:

SCAN
   IF Return3Year = 0
      BLANK FIELDS Return3Year
   ENDIF
   IF Return5year= 0
      BLANK FIELDS Return5year
   ENDIF
   IF Return10Year= 0
      BLANK FIELDS Return10Year
   ENDIF
ENDSCAN

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Mike Lewis]

Greg,

You easily can change the 0.00 to NULL after the import:

REPLACE Return3Year WITH NULL FOR EMPTY(Return3Year)

The trouble is that will replace all zero values with NULL, not just those that originally contained the dash. If the text file contains genuine zeroes, these too will be converted to NULL.

If that's not what you want, I can't immediately see a better solution.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

Having said that, I suppose you could first replace the dashes in the text file with some unused numeric value, then replace that value with NULL.

For example, if Return3Year can never go above, say, 100:

lcFile = FILETOSTR("Myfile.TXT")
lcFile = STRTRAN(lcFile, ",=,", ",101,")
  && replaces dashes with 101
STRTOFILE(lcFile, "MyNewFile.TXT")

* Now do the import as usual

* Replace the 101s with NULLs
REPLACE Return3Year WITH NULL FOR Return3Year = 101

Not sure if that would do the trick, but worth a try.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[IlyaRabyy]

Greg,
I hope you have access to VFP and/or to the source code. Apparently, your "receiving" table does not accept NULL values by design (which is default in VFP).

However:

Whatever the table you're importing to is (just a cursor or a permanent file on disk), you can alter its column to accept NULL, like this (I did it from the VFP's Command window):

m.Fld = 0.00
CREATE CURSOR C_TEST (Fld1 N(10,2) NULL)
INSERT INTO C_TEST FROM MEMVAR 
REPLACE Fld1 WITH .null.
BROWSE LAST

I am just giving you the idea, but HTH.


Regards,

Ilya

 

[Mike Lewis]

Ilya,

I didn't see anything in Greg's post that suggested his table doesn't accept NULLs. What he said was that, when he replaced the hyphens with the word NULL (in the import file), it was imported as zero. That's not surprising, since it was being imported into a numeric field.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[IlyaRabyy]

Mike,
the idea I'm suggesting is to ensure (modify if necessary) the field does accept the null value.
Like I said, it's just the idea, the starting point. Where Greg will go from there depends on specifics of what he's got.
For instance, utilizing your own idea (to an extent), create a temp cursor that takes these fields (accepting NULLs) as Char type, then replace dashes with NULLs, then append from that cursor to the production table (which, again, has to accept NULLs in those fields). I just ran a quick test - and this scenario does work. Here's what I did:

CREATE CURSOR C_TEST (Fld1 N(10,2) NULL)
CREATE CURSOR C_TEST_2 (Fld1 C(10) NULL)
=STRTOFILE("-","C:\Temp\NullTest.CSV")
APPEND FROM "C:\Temp\NullTest.CSV" TYPE SDF
APPEND FROM "C:\Temp\NullTest.CSV" TYPE SDF
APPEND FROM "C:\Temp\NullTest.CSV" TYPE SDF
APPEND FROM "C:\Temp\NullTest.CSV" TYPE SDF
REPLACE fld1 WITH .NULL. all
SELECT C_TEST
APPEND FROM (DBF("C_TEST_2"))
BROWSE LAST

Again, it's just the idea.

Regards,

Ilya