Inputting a date from a .csv file 2

[AndrewMozley]

I would like to read some dates in from a .csv file, and the user supplies these as e.g. “31/07/2012” which I would like to interpret as 31st July 2012. However I find that the yyyy field is interpreted as yy, so 2012 is being interpreted as the 20th year in this century. So I am reading these dates as 31st July 2020.

While investigating this, I have created a test csv file, date1.csv, and a test table, date1.dbf.

I execute these commands at the VFP prompt

USE date1.dbf
APPEND FROM date1.csv TYPE CSV
BROWSE LAST​

My date1.csv file looks like this

Date_1
"31/07/2012"
"03/04/2023"

and this is the result I get in date1.dbf (I have SET DATE LONG just for display purposes) :

31 July 2020
04 March 2023

So I am finding that the first (data) line in date1.csv is being interpreted as dd/mm/yy and the second is being interpreted as mm/dd/yyyy – which is different!

Any idea why this is happening? I have got SET DATE BRITISH, SET CENTURY ON, and on my Control Panel | Regional options, I am using English, United Kingdom, with short date format dd/mm/yyyy.

It almost seems that VFP is only prepared to interpret an input date as British when it is really desperate, because the mm field is greater than 12 !

Any help greatly appreciated.

 

[GriffMG]

This is a common problem - Excel does something similar.
US products I'm afraid.

I would import into a string field and then process the data manually.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

Andrew,

Instead of this:

APPEND FROM date1.csv TYPE CSV

Do this:

APPEND FROM date1.csv TYPE [b]Delimited[/b]

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[GriffMG]

Mike

That's a neat distinction that works - have a star

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

Thanks, Griff. I've battled with this sort of thing often enough, but I still have to do a bit of trial-and-error every time it comes up.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

The help on append says you can import dates, if SET DATE has the proper format and test it with CTOD(). But even when you SET CENTURY ON and SET DATE DMY and CTOD("31/07/2012") turns to the 31rd July 2012, APPEND does append with the year 2020.

Well, the only explanaition is, that this paragraph about the date import is written in the section, which explains the DELIMITED format, so it's perhaps only valid for that type:

DELIMITED
Specifies that the source file from which data is appended to the current Visual FoxPro table is a delimited file. A delimited file is an ASCII text file in which each record ends with a carriage return and line feed. Field contents are by default assumed to be separated from each other by commas (do not include extra spaces before or after the commas), and character field values to be additionally delimited by double quotation marks. For example:

Copy Code
"Smith",9999999,"TELEPHONE"


The file extension is assumed to be .txt for all delimited files.

You can import dates from delimited files if the dates are in proper date format. The date format defaults to mm/dd/yy. Including the century portion of a date is optional. Visual FoxPro will import a date, such as 12/25/95, that doesn't include the century and assumes the date is in the twentieth century. Date delimiters can be any non-numeric character except the delimiter that separates the fields in the delimited file.

Dates in other formats can be imported if their formats match date formats available in SET DATE. To import dates that are not in the default format, issue SET DATE with the proper date format before using APPEND FROM. 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.

So for future reference, this kind of date imports work only for TYPE DELIMITED.

The section for SDF for example, expects dates in the text file to be formatted as YYYYMMDD without any date mark/separator. The CSV paragrpah only says the first line of the CSV file will be taken as field names, why 2012 turns to 2020 and 2023 is coming in correctly is a mystery.

Bye, Olaf.

 

[AndrewMozley]

Thank you everyone, particularly Mike; you are the source of an amazing fund of VFP trivia (which has been a great help to me!); I shall go for the DELIMITED option.

I am grateful for the very prompt and accurate replies you have all given And thanks, Olaf for reminding me that SET DATE is indeed relevant for input (in this case - I had thought that it was all to do with the regional setings in the control panel).