Date Conversions

[shawnwmorris]

Hello All,
I have a VFP program that will dump some data to an XLS file. When I open the XLS files all of the Dates are set to DD-MM-YY format which causes excel to read the field as a text field instead of a date field. When I export the data to a CSV file the dates look correct and are correctly identified as a date field by excel.

Is there a way to change the date format in the SQL Command in VFP 9.0 to allow the data to be displayed and read like: mm/dd/yyyy?

Thanks
Shawn

 

[JRB-Bldr]

When it comes to Dates, Excel does its own thing in regards to formatting and, as a consequence, how they appear.

As long as you get the date into the CSV correctly for Exporting, you really don't need to worry about it.
But if you wanted to change how Excel shows the Dates, you can use VFP Automation of Excel to change the format of the Date column(s).

Good Luck,
JRB-Bldr

 

[TamarGranor]

If you're using COPY TO, make sure you're specifying TYPE XL5, not TYPE XLS.

Tamar

 

[Olaf Doschke]

What are you doing, when you don't go through CSV?

SELECT workare && having an export cursor or DBF
COPY TO some.XLS TYP XL5

This is the simplest way to export, converting to new XLSX then is possible by opening the file in Excel and saving as the new standard format. All types go over as they are, there's no date to text conversion unless your dates already are just text in the VFP DBF. So what query do you use to prepare your export cursor or DBF? If you do anything like DTOC(), then that's your fault.

To determine how Excel displays dates, use Excel settings, I'm not even talking about setting the formatting of a column within the sheet, but telling Excel what display format to use in general. It'll typically display dates as Windows is configured in the locale about many things like clock 12(am/pm)/24 hours, money currency, decimal point, 1000s separator for numbers etc. and, of course, dates. They all have a culture specific locale.
When you keep data at their native type, the COPY TO export works. You're only needing a text formatting in your export SQL query, if you want to go through CSV, as CSV by definition means all data types are converted to text and put in a text file.

In your thread thread184-1777988 I told you when it comes to appending CSV into a Foxpro DBF, Foxpro expects yyyymmdd. That's documented in the help for the SDF format in both the help chapters on APPEND and COPY TO, but also valid for CSV. You also get CSV imported correctly if you format to US format on US Windows, both FoxPro and Excel don't limit CSV import to need that yyyymmdd format, but in case you don't you depend on current settings. When you make a query converting date columns into any textual format, you have a string column and then exporting to excel via COPY TO TYPE XLS/XL5 or also EXPORT is not looking at your char column as a date anymore, brings over the textual value as textual value. Just keep it at a date type column in VFP and it gets transferred into a date type cell in the Excel sheet.

Finally, to make the point clear and address your post subject "Date Conversions": That's already your error. You DON'T convert a FoxPro date to text before Excel export, you only do so before or at SDF/CSV export. There is only harm in converting dates to text here. No matter, if you use COPY TO TYPE XLS/XL5, EXPORT TYPE XLS/XL5 or automate Excel and fill in cells, you don't convert to text in that steps in the hope Excel then converts back to date. COPYTO/EXPORT has VFP runtime knowledge about building an XLS binary file containing the date as Excel needs it in his binary native format, using OLE automation that type conversion is done by OLE via using the intermediate OLE date type (OLE is used to mediate between different programming languages and applications, called type marshalling, it happens for example when setting an excels Cell(row,col).Value = DATE(), for example, the OLE server takes in VFPs native DATE as OLE date and puts it in as Excel date, for text properties SYS(3101) can help making the correct codepage conversion, but native types like date don't need that), as OLE is the link between any languages own native binary date type. This is not your concern. This only get's a conversion temporarily as text again, if you're using the dreadful _vfp.DatatoClip(). To make that work, you need to set the Excel sheet column format before pasting, as you then paste a fully textual tab delimited _cliptext. It's often seen as advanced and performant, but it's backward, it's totally unelegant, imitating what you manually do. Whenever I'm bound to manually copy&paste data from one applications grid into excel sheets I already expect to need to fix some columns, the clipboard is the least effective transfer medium to transport different types. There are different types clipboard formats, but all of them only are for the whole clipboard, eg images can be copy and pasted, but DataToClip is not keeping your VFP data as native type single values all just somehow magically separated by tabs. Conversion to text always risks any data you want to arrive as what it was to arrive as text instead.

Bye, Olaf.

 

[Koen Piller]

Hi Morris,

Unfortunately you dont inform us how you convert your DBF to an Excel file.
The wrongly transformation of date fields could be the cause of your convert procedure.
Maybe it is also a good idea to study the procedures constructed by Vilhelm doing this job.
Please read his blog:

http://praisachion.blogspot.nl/2017/04/export-dbf-to-excel-2007.html

Regards,

Koen

 

[SkipVought]

Excel has rules that it imposes on any value ENTERED in an unformatted (General Number Format).

Examples:
[pre]
what you enter what you get What you see
1/2 42737 2-Jan
[/pre]
In this case, using either - or / delimiter, Excel assumes a date, month-day-current year.

[pre]
what you enter what you get What you see
1/2/17 42737 1/2/2017
[/pre]

[pre]
what you enter what you get What you see
1e2 100 1.00E+02
[/pre]

[pre]
what you enter what you get What you see
20170102 20170102 20170102
[/pre]
No date at all!!!



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Olaf Doschke]

Yes and no, Skip.

The number 42737 is Excels date format, internally. It's a number of days since some reference date and this means excel recognizes this as date and displays it as a date. You can't inversely expect a number 42737 to be taken as date, though. By the way, when I type in 1/2 in german excel I get 1st February instead of 2nd January, even though German date format would be 1.2. not 1/2. I have to type in 1.2.2017 so Excel infers this means a date, even though German numbers would use comma as the decimal point.

20170102 does indeed not convert when typed in and also not when reading in from a CSV, VFP converts that to a date from both SDF and CSV, but Excel not. So forget that aspect, the rest is still true. Especially, to bring over cursor data via _vfp.DataToClip I get mixed results. Just tried copying over a cursor with one date column only (field type D=Date) and that comes over as date via pasting what _vfp.datatoclip copies to the clipboard. What I see from the clipboard is german date format, but when changing SET DATE BRITISH I get the British format into the clipboard with _vfp.DataToClip. Excel also takes this in and displays it in german format.

Your results might differ depending on language packs installed (I have German and English) and settings of VFP and Excel.Therefore still the easiest way to convert to excel is COPY TO ... TYPE XL5 or EXPORT TO TYPE XL5, as that doesn't depend on any settings of windows, FoxPro or excel and generates the binary data necessary in an XLS file. A slow but also automatically correctly converting way is to set excel cell values via automation. Both these ways need a date column in a VFP DBF or cursor as the source. And that's why I still recommend to NOT convert anything on the VFP side when you don't want to go through text formats like CSV.

If you manage to get data over correctly by CSV, then use that. Just be aware your software might only work on the US or UK market and depends on other users to include the English language pack into their Windows location, perhaps. I can't cover what behavior you get with your specific windows, excel versions and settings via DataToClip, but I can ensure COPY TO TYPE XLS works.

Bye, Olaf.

 

[SkipVought]

Yes and no, Olaf.

All my references are to what happens in Excel.

The OP is in New York.

If you enter a 1 and then format as Date, you see 1/1/1900 or 1.1.1900

If you use DMY in your regional settings, yes 1/2 will be assumed to be a date and will be converted to Feb 1 of the current year.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Olaf Doschke]

OK, I still think this is vague and has many dependencies and woes which could arise. COPY TO creates a binary xls without any such problems, because whatever locale is set, the 1st February is stored as 1st February binary VFP date in a DBF and gets over to Excel in its day number (assumed that's the number stored in the binary xls format.

You might also try Vilhelms XLSX generation, he generates Open Office XML and that will turn dates to XML dates, but also that is done from the source DBF file with the VFP date type.

We still don't know from shawn, what I initially asked: What are you doing, when you don't go through CSV? How are you creating your XLS files?

Bye, Olaf.

 

[shawnwmorris]

Hello Everyone,
Thanks so much for all of the help so far. I truly appreciate it. I apologize for the late response I am quite busy.

I am exporting the results of a query. When I don't create a CSV i am exporting to XLS. I then have to open the XLS file and save it as an XLSX file to get the file to current version of excel. The downfall of this is that any dates are not formatted correctly in Excel.

I have found that exporting to CSV is the same process (oepn in Excel save as XLSX file) but the dates are formatting correctly.

Shawn Morris

 

[Olaf Doschke]

The big question is, what are your dates when you query and export to XL5, if your query is the same as you use for CSV export, then you most likely do a conversion of date to string, which is deadly for the export. That should be clear by now.

You can query in the sense of getting the wanted records to output, but shouldn't do any conversions of dates to strings within VFP within that query, when you then really make use of the EXPORT command, the dates must be correct. Or what are you referring to, if you say "exporting to XLS". Post your code.

By the way, the manual step to load the XLS and savce as XLSX can easily be automated as aftermath of the export, you start Excel.Application, laod the XLS and SaveAs XLSX and are done with that:

LOCAL lcFile
lcFile = Addbs(GetEnv("TEMP"))+"foxuser.xls"
Use Sys(2005) Again Alias foxuser
Export to (lcFile) TYPE XL5
oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(Addbs(GetEnv("TEMP"))+"foxuser.xls")
oExcel.ActiveWorkbook.SaveAs(ForceExt(lcFile,"xlsx"))
oExcel.Quit

Now look into temp and you have foxuser.xlsx

When you have say "dates are not formatted correctly in Excel.", then you merely have a problem with system setting or excel settings. The data can't be wrong. Excel will display the dates as configured in Windows, unless you configure it differently. What's wrong is your idea dates have to be displayed in a certain way from Excel. Your VFP date display settings don't get set system wide. The system wide date display settings are within control panel.

What you can do, for example to specify dates with or without century, datetimes with am/pm or 24 hours is a column formatting. VFP generaes an XLS with has the general date type set for date columns, no more no less, everything else is an excel topic and an OS topic, not a conversion problem.

Bye, Olaf.

 

[Olaf Doschke]

OK, I dug into Excel settings and didn't find an override default date format, so what Excel displays merely depends on OS control panel Regional settings.

What I find in Excel advanced settings in my English notebook is this:

So you can override how numbers are displayed when unchecking "Use system separators". I don't find a date specific overloading of system settings, though I thought I once configured this for Excel.

Anyway, the dates are surely the correct dates and the regional settings of Windows will determine how your dates are displayed.

If CSV works, then maybe just because your CSV dates are NOT detected as dates by excel and it keeps them as they were output to the csv. Can you go to a cell right beside a date cell and enter the formula =previouscell+1? If that gives an error you know your date column hasn't come over as a date, really. Then it's no reason to be happy with the looks of it being "correct", you can't compute with strings in Excel.

If you send over the XLS or XLSX to someone else, the display will depend on his systems regional date format settings anyway, you stay with the native date type and a) the dates are real dates and Excel can compute them, b) they are displayed in a manner the user of the system should be used to, as he can configure this in control panel and is asked for region and keyboard layout in the Windows installation already, that also sets number and date format. You shouldn't enforce a specific formatting, even though that doesn't alter the quality of dates as dates, you can override with Excel cell(column formatting options and every user looking at the sheet will see the American date, for example. And you still can compute with that date. If you absolutely want that, then do so, but don't convert before EXPORT/COPY TO.

Bye, Olaf.

 

[SkipVought]

Shawn,

You might consider running your query IN EXCEL, in MS Query (a built-in)

You can, from this master workbook, set up a query ONE TIME, format your result table ONE TIME, and then REFRESH your query as needed to return current data from your db. Do a SaveAs to spin off the results in a variety of file types.

For help with this process, post your question in forum68.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}