CSV Import in Fox 8.0 distorts date fields

[STEVEWADDY]

We process several CSV extracts from our main business software through foxpro to generate daily and weekly management reports.The original code was written for VF6

We now find that when we run the same code in VF8 some dates are being imported correctly e.g. 24/12/2003 while some are imported as 06/05/0002.
It would appear that all the dates that are correct are ones where the day is greater than 12 and the day / month could not be interchanged i.e. 24/12/2003 or 31/07/2003

If any one has any ideas would be most grateful

 

[Mike Lewis]

Steve,

This sounds like a simple case of the wrong date format being selected. If the dates are in European format (day before month), issue SET DATE BRITISH before the import. If American format, do SET DATE AMERICAN.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[ramani]

Hi

From your writing, it appears you have DDMMYY format for the date. SO in your code..
SET DATE BRITISH
OR
SET DATE DMY
and then proceed with your code. AT the end again SET DATE to whatever you want.



____________________________________________
Ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[STEVEWADDY]

Thanks for such a fast response Mike - its appreciated.

We already have the date set to British - thing i cant understand is if we run the exact same code through V8 we get the wrong dates - when we then run the same code through V6 we get the right dates.
This is without changing any of the code at all - we evn hard coded the SET DATE BRITISH into the code to make sure it was being applied?

 

[Mike Lewis]

Steve,

This is very interesting. I've got a sample CSV file with a large number of datese -- some valid British, some valid American, some both, some neither.

I've just appended it into a table, and found everything works exactly as expected. I did this in both VFP 6.0 and 8.0. I used APPEND FROM ... TYPE DELIMITED.

I don't understand why it isn't working for you. Is there any more information you can give us to help track this down?

For what it's worth, I have just finished a project which involved importing dates in different formats from different types of files. I developed it in 8.0, but the client is running it under 6.0. I didn't notice any differences between the versions in this area.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[STEVEWADDY]

Looks like you've found the solution on this one - our original code was using APPEND FROM.......TYPE CSV
I thought i would try it with TYPE DELIMITED and the dates came through OK - although it has thrown out a few NULL dates which i'm not sure about - possibly some data corruption in the download?
Still not sure why the changed command makes all the difference?
For info the data is extracted from an application written in Magic code and run through Btrieve - CSV generated through standard report generator.

At least i can move forward now - thanks for your help on this one.

 

[Mike Lewis]

Glad you got it working, Steve. Looking back at my project code, I see I have used several variants of TYPE DELIMITED, but never TYPE CSV. Sounds like something to stay away from in VFP 8.0.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[baltman]

APPEND FROM ... TYPE CSV has several issues in VFP 7 as well. CSV is a great format to COPY TO, but the APPEND FROM was not well implemented at all.

Brian

 

[Mike Lewis]

Thanks for confirming that, Brian. Worth remembering.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[dbMark]

Steve, a curiosity question:
On those records with the problem dates appending wrong, did you look at them in the source CSV file itself? What were the actual original values that appended wrong? I don't think I ever saw the pre-append structure mentioned for the one which resulted in "06/05/0002". I wonder if it was "06/05/2002" or a shortened format "06/05/02" or "6/5/02". Somehow I think you'll say it was a shortened format...

Maybe from that we can see what the APPEND/CSV limitations are. (A simple test in VFP6 worked fine for me.)

 

[baltman]

Besided the fact that TYPE CSV skips the 1st record (assumes a field name), only dates formatted as YYYYMMDD import correctly directly into a DATE type field. This is the same as a TYPE SDF.

The best solution for dates in a CSV file with formats such as MM/DD/YY or MM/DD/YYYY is to utilize the logic in my faq184-4275 "Transform a Partially Formatted Delimited Text File Into a Standard Delimited File"

Brian

 

[STEVEWADDY]

The format of all the original dates was "06/02/02" (6th February 2002)

We have been running the code using APPEND.....DELIMITED for several days now & (fingers crossed) everything has worked OK.

Thanks to all contributors for your help