appending dates from a text file

[gypsy247]

I receive a weekly file that contains dates and needs to be appended into a table.
Currently this is done by appending into a temporary table that has all the date fields defined as character (10) and then appending this into the final table that has the date fields. This works but,as these are very big files, I'm trying to by-pass the 1st step and append directly into the structure with the date fields. The dates in the flat file are formatted as follows :
mm/dd/yyyy, date is set to MDY and Century is set to on., but when I issue the command &quot;Append from <textfile> sdf&quot;, the date in the date field has two zeroes inserted in front of the century and the year is in the next field

i.e. &quot;01/01/2003&quot; ==> {01/01/0020} and &quot;03&quot; is in the next field.

I ha ve tried this with VFP 6.0 and VFP 6.0 wnd the results are the same.
Anybody have a solution for this ?
Julie

 

[IRABYY]

gypsy247 (Programmer)
The dates in the flat file are formatted as follows :
mm/dd/yyyy, date is set to MDY and Century is set to on., but when I issue the command &quot;Append from <textfile> sdf&quot;, the date in the date field has two zeroes inserted in front of the century and the year is in the next field

i.e. &quot;01/01/2003&quot; ==> {01/01/0020} and &quot;03&quot; is in the next field.

I ha ve tried this with VFP 6.0 and VFP 6.0 wnd the results are the same.

First and foremost: obtain and install SP5 for MS Visual Studio 6 if it's not been done yet.

Second: if the above does not help - sorry, colleague, you will have to write a small program that reads your flat file (I hope it's not bigger than 2 Gb) and converts dates from type String into type Date (FGETS(), SUBSTR() - or STREXTRACT() - and CTOD() functions, basically). I take it that flat file contains columnar data, doesn't it?

Regards,

Ilya

 

[Mike Gagnon]

gypsy247

If I create a text file and put in &quot;01/01/2003&quot; in it and use the following

CREATE CURSOR myCursor (date1 d)
APPEND FROM c:\test.txt delimited

It works for me.
Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[DSummZZZ]

Mike,

Delimited will behave differently than SDF though.


gypsy247,

From the MSDN, APPEND FROM

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SDF
Include SDF to import data from a System Data Format file. An SDF file is an ASCII text file in which records have a fixed length and end with a carriage return and line feed. Fields are not delimited. The file name extension is assumed to be .txt for SDF files.
Effective conversion of date data from SDF files to Visual FoxPro tables requires data to be stored in YYYYMMDD format.

If date information is stored in ambiguous formats, you should map the date column to a character column of appropriate width so you can inspect the value then apply the correct conversion routine to create correctly formatted date data.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

You could create a separate columns the character date, and another for the 'date' date. Then do a replace of the 'date' date with the character date. It may be better than separate files.
Dave S.

 

[gypsy247]

I installed the SP5 this afternoon but it didn't fix it.
The data is in fixed length(columnar) format and is not delimited and is appended as SDF (sorry, I forgot to mention that) and I have no control over how it is formatted.
Looks I'm stuck with the 2-step approach. The files are to big to add separate fields for all the dates (there are 5 date fields). We'd be pushing the 2GB limit within a couple of months.

Thanks for all your suggestions

 

[gypsy247]

Does anyone know if this has been fixed in VFP 8.0 ?
Does a date in line sequential file formatted as mm/dd/yyyy append correctly into a date field when exexuting an &quot;append from ........ type sdf&quot;

Julie

 

[rgbean]

Julie,
&quot;Fixed&quot; implies something is broke - it isn't, you just can't do what you want to do the way you want to do it. There are a number of other ways to read an SDF file and append the data into your table WITHOUT using APPEND FROM - they just take a bit more work.

Look at LLIO - Low-Level IO - FOPEN(), FREAD(), FGETS(), etc. Also look at using FILETOSTR(). Both of these will require a little parsing of the data, but you'll have complete control of how you process the data and how you then insert it into your table.

Rick