Appending dates form text file into a date field 2

[mm0000]

I have a text file in which dates are stored in YYYY-MM-DD format. After setting date to ansi + century on and then importing the text file it does not import the date values into the date field. All imported date fields are empty. What am i missing out?

 

[baltman]

You need to remove those '-' characters. Easy to do if there aren't any in the rest of the file using chrtran(), otherwise the easiest thing to do is make a copy of the structure, change the date fields to char (10), append the data in, remove the "-", copy back to a new text file and then append into your original structure.

There are faster, more efficient ways, but if it is a one time thing, the above should work for you.

Brian

 

[mm0000]

According to the VPF help file (version 8) dates can if imported if the dates are in proper date format. It says to test whether a date format can be successfully imported use it with ctod(). If the date is acceptable to CTOD(), the date will import properly. It passes the ctod test but i get blanks values in the dbf file.

 

[torturedmind]

isn't it that the ANSI date format is yy.mm.dd and not yy-mm-dd? just a thought...

kilroy
philippines
"and that's what we call creativity..."

 

[mm0000]

VFP does not seem too fussy on the separator ctod('2004/04/03') or ctod('2004.03.03') or ctod('2004/04/03') all seem to be acceptable to VFP.

 

[baltman]

mm0000,

I don't seem to have as much flexibility in 7, those examples don't work. The standard text date is YYYYMMDD.

Brian

 

[Stella740pl]

Try to SET DATE TO YMD .
In VFP6, if date format is YMD, it accepts CTOD('2004-04-03') just fine.

If it still doesn't work when you append from your text file, then you should go with Brian's first suggestion.

 

[Stella740pl]

If it still doesn't work when you append from your text file, then you should go with Brian's first suggestion.
I meant, append it to a cursor with C(10) field instead of a date, then just work with this string to get it into date format while SELECTing or APPENDing it to your table.

 

[Stella740pl]

Try this example, it worked for me (in VFP6):

#DEFINE CRLF CHR(13)+CHR(10)

SET DATE TO YMD
SET CENTURY ON

STRTOFILE('','D:\TEMP\DeleteMe.txt',.F.)
STRTOFILE('2004-01-25'+CHR(9)+'Start Date '+CRLF, ;
   'D:\TEMP\DeleteMe.txt',.T.)
STRTOFILE('2004-02-12'+CHR(9)+'Revisit Date'+CRLF, ;
   'D:\TEMP\DeleteMe.txt',.T.)
STRTOFILE('2004-03-28'+CHR(9)+'End Date    '+CRLF, ;
   'D:\TEMP\DeleteMe.txt',.T.)

CREATE CURSOR tmp (theDate D(8), comments C(25))

APPEND FROM ('D:\TEMP\DeleteMe.txt') TYPE DELIMITED WITH TAB 
   && put your text file's type here;
   && it won't work correctly with incorrect type.

BROWSE LAST

Stella

 

[Stella740pl]

mm0000,

The example I posted above worked fine also with SET DATE TO ANSI.

When you import the file, are all other fields, before and after the date, appended correctly?

Can you post a sample from you text file, structure of the table and a few lines of code where you deal with it?

I think, you should check the type of file you specify when appending and possibly SET STRICTDATE settings. Date formatting seems to be OK on itself.

 

[mm0000]

Thanks Stella, I had left out the type delimited part of the command and it now works fine.

 

[baltman]

I wasn't aware that 'set date' had an effect on import.

Thanks for pointing that out

Brian

 

[Stella740pl]

Thanks for the stars, guys. I am glad everything works fine now.

I wasn't aware that 'set date' had an effect on import.
It sure does. I import dated information from text file almost every week, and the source changes the formatting every once in a while.

Stella