Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
IMPORTING FROM EXCEL - DATE FORMAT
- Thread starter spysab
- Start date
Mike Gagnon
Programmer
What is your set('DATE') setting?
Mike Gagnon
If you want to get the best response to a question, please check out FAQ184-2483 first.
Mike Gagnon
If you want to get the best response to a question, please check out FAQ184-2483 first.
TamarGranor
Programmer
I'll bet you're specifying TYPE XLS, which is Excel 2.0 format. Try XL5 instead.
Tamar
Tamar
- Thread starter
- #5
atlopes
- im using csv format, but any format comes out the same result tho.
APPEND FROM ? TYPE CSV
i've tried
APPEND FROM ? TYPE CSV
APPEND FROM ? TYPE XL5 ** another problem here is this didnt upload all rows. i have 71,000 rows. only csv covers all
Mike Gagnon
- date setting, how? sorry im new in foxpro. just relying on tweaking codes.
- im using csv format, but any format comes out the same result tho.
APPEND FROM ? TYPE CSV
i've tried
APPEND FROM ? TYPE CSV
APPEND FROM ? TYPE XL5 ** another problem here is this didnt upload all rows. i have 71,000 rows. only csv covers all
Mike Gagnon
- date setting, how? sorry im new in foxpro. just relying on tweaking codes.
Olaf Doschke
Programmer
Append can only be used for the type the file is, you can't try CSV on an XLS or XLS on a CSV file. Often CSV files also have the ending TXT, they may even have no file ending. It still only matters what's inside the file.
That's also a reason the APPEND command has the TYPE option, VFP doesn't detect what file type it is when reading the file, you specify it. So please, simply look into your source files via Notepad or Notepad++. Don't just double click and rely on the file extension association.
APPEND TYPE XLS or XL5 only work for legacy binary excel formats, but when you say it's CSV, I assume it is. Then trying XLS won't help.
The other problem is with the way Excel generates CSV. As American, you have no problems, as regional settings are ok, but Excel is a) using the character specified for lists in regional settings, which can also be a semicolon. Instead, because CSV means Comma Separated Values, it should always use commas. And b) Excel uses regional date format, whereas CSV asks for US-American format. Then c) Excel does not delimit text with quotation marks. And on the other side, VFP does not import multi-line text columns. What VFP does correct and Excel not is, that VFP only accepts commas and US formatted dates, which even is true, if your date settings in VFP differ.
So the errors of Excel CSV generation and VFP's insisting on specifications, albeit not the one about multi-lines, is making CSV problematic. If your CSV is saved from Excel, that's easily having more weight in the aspects not working.I f you got the CSV from download, well, that still can have all these differences, when it originally was produced by Excel or who knows what export mechanism not being pedantic on CSV specs.
You have a few more detail options with [tt]APPEND FROM Getfile("CSV") DELIMITED WITH '"' WITH CHARACTER ','[/tt] - as you can see you can change the string delimiter and the value separator character as needed. So please, first really take a text editor and open the CSV file from it. After you look into your file, see what you need, of course. The quotation marks can stay, even if the file has none, if a column is CHAR, it'll just take in the text as is anyway.
Using DELIMITED has another problem with not expecting a header captions line, but you may simply disregard record 1. Also, in that APPEND mode, VFP does import both American formatted dates and the format you configured in VFP. See SET DATE, SET MARK, and SET CENTURY. That also means month and day can be swapped out if your config has the day before the month (eg British/French), as that is prioritized. It's even worse if a date is with the American slash as mark character between the date parts but the data itself fits British/German/French DMY ordering, this is read in as DMY date, though American format is MDY.
So you have all kinds of problems with CSV files using APPEND FROM file DELIMITED, too.
The simplest thing you can do, as VFP then does no interpretation itself, define the DBF or Cursor you append to with all character fields and then program the conversion yourself. For example, CTOD() or CTOT() functions are predictably using the current output format setting for input (conversion from string to date or to datetime). It's more tedious, as you can't simply use a single line APPEND, but that's the way that is. Even if you get near with DELIMITED, it's a good idea to append to a empty cursor and then after maybe making some further data conversions and corrections and deleting record 1 move it to a DBF from there.
XML instead of CSV is even harder, but you'd have better chances getting that to work consistently as XML at least can be more specific with format data type information. So another route is maybe getting the original data in XML format.
Also, if your original files actually are Excel and you only save to CSV as you thought that's easiest, there are good libraries for VFP and modern Excel formats: Look out, if there is a newer version.
Bye, Olaf.
Olaf Doschke Software Engineering
That's also a reason the APPEND command has the TYPE option, VFP doesn't detect what file type it is when reading the file, you specify it. So please, simply look into your source files via Notepad or Notepad++. Don't just double click and rely on the file extension association.
APPEND TYPE XLS or XL5 only work for legacy binary excel formats, but when you say it's CSV, I assume it is. Then trying XLS won't help.
The other problem is with the way Excel generates CSV. As American, you have no problems, as regional settings are ok, but Excel is a) using the character specified for lists in regional settings, which can also be a semicolon. Instead, because CSV means Comma Separated Values, it should always use commas. And b) Excel uses regional date format, whereas CSV asks for US-American format. Then c) Excel does not delimit text with quotation marks. And on the other side, VFP does not import multi-line text columns. What VFP does correct and Excel not is, that VFP only accepts commas and US formatted dates, which even is true, if your date settings in VFP differ.
So the errors of Excel CSV generation and VFP's insisting on specifications, albeit not the one about multi-lines, is making CSV problematic. If your CSV is saved from Excel, that's easily having more weight in the aspects not working.I f you got the CSV from download, well, that still can have all these differences, when it originally was produced by Excel or who knows what export mechanism not being pedantic on CSV specs.
You have a few more detail options with [tt]APPEND FROM Getfile("CSV") DELIMITED WITH '"' WITH CHARACTER ','[/tt] - as you can see you can change the string delimiter and the value separator character as needed. So please, first really take a text editor and open the CSV file from it. After you look into your file, see what you need, of course. The quotation marks can stay, even if the file has none, if a column is CHAR, it'll just take in the text as is anyway.
Using DELIMITED has another problem with not expecting a header captions line, but you may simply disregard record 1. Also, in that APPEND mode, VFP does import both American formatted dates and the format you configured in VFP. See SET DATE, SET MARK, and SET CENTURY. That also means month and day can be swapped out if your config has the day before the month (eg British/French), as that is prioritized. It's even worse if a date is with the American slash as mark character between the date parts but the data itself fits British/German/French DMY ordering, this is read in as DMY date, though American format is MDY.
So you have all kinds of problems with CSV files using APPEND FROM file DELIMITED, too.
The simplest thing you can do, as VFP then does no interpretation itself, define the DBF or Cursor you append to with all character fields and then program the conversion yourself. For example, CTOD() or CTOT() functions are predictably using the current output format setting for input (conversion from string to date or to datetime). It's more tedious, as you can't simply use a single line APPEND, but that's the way that is. Even if you get near with DELIMITED, it's a good idea to append to a empty cursor and then after maybe making some further data conversions and corrections and deleting record 1 move it to a DBF from there.
XML instead of CSV is even harder, but you'd have better chances getting that to work consistently as XML at least can be more specific with format data type information. So another route is maybe getting the original data in XML format.
Also, if your original files actually are Excel and you only save to CSV as you thought that's easiest, there are good libraries for VFP and modern Excel formats: Look out, if there is a newer version.
Bye, Olaf.
Olaf Doschke Software Engineering
Spysab,
If you're importing from a CSV file, then the format of the date in the CSV file will determine how FoxPro will interpret the value. If Fox fails to recognize the value as a date, then your table or cursor will get empty values.
If you can't get the CSV file to be changed, you may try to set the date in VFP to adjust to the incoming date. My experience says that it may be tricky, but you might have a shot with SET DATE configuration or ask us to help you sort it out (we would need a sample of your CSV to test, a single line would suffice).
Alternatively, you can use the CSVProcessor class at to import or append the CSV data. The date format may be configured at the import side using patterns like "%Y-%M-%D", for instance.
In any case, it might be helpful if you could share a sample of your data, as I said.
If you're importing from a CSV file, then the format of the date in the CSV file will determine how FoxPro will interpret the value. If Fox fails to recognize the value as a date, then your table or cursor will get empty values.
If you can't get the CSV file to be changed, you may try to set the date in VFP to adjust to the incoming date. My experience says that it may be tricky, but you might have a shot with SET DATE configuration or ask us to help you sort it out (we would need a sample of your CSV to test, a single line would suffice).
Alternatively, you can use the CSVProcessor class at to import or append the CSV data. The date format may be configured at the import side using patterns like "%Y-%M-%D", for instance.
In any case, it might be helpful if you could share a sample of your data, as I said.
- Status
Similar threads
