Dates when converting from xlsx to CSV

Topics: Developer Forum
Jul 12, 2013 at 12:27 AM

seem to be running into a problem.
I have a simple XLSX document with these two columns:
01/01/2013 31/12/2013
(if you show the format both are defined as dates in the Euro format, i.e. DD\/MM\/YYYY)

When I run a simple conversion on it:
$file = "test1.xlsx";
$reader = PHPExcel_IOFactory::createReader('Excel2007');
$obj = $reader->load($file);
$writer = PHPExcel_IOFactory::createWriter($obj, 'CSV');
$writer->save(str_replace('.xlsx', '.csv', $file));

the result is:
user@home$ cat test1.csv

Any tips/suggestions would be most appreciated.
Jul 12, 2013 at 12:35 AM
So, I removed the setReadOnly(true) as I
and this seems to work iff I save as xlsx within LibreOffice
But the format of xlsx I am getting from the users is exported from some other source, I'm guessing with post processing within MSFT Excel.
And for that I still get the non date data fields?

Anyone run into this? very puzzling as if I save the data I open in LibreOffice and save as to a different file with the "same" format it works, but since I desire to do this programitcally that solution is not really tenable.
Jul 12, 2013 at 1:10 AM
If you use
when reading a file, then there is no way of differentiating between a date and a number.

But if you're getting xlsx files from other sources where the date isn't recognised, I have no ideas without seeing an example of such a file
Jul 12, 2013 at 2:35 PM
So, I played around some more, and it appears as though the date field is really classified as a custom field with a format of "dd\/mm\/yyyy"
I tried to replicate this in Excel 2010 I have and it still was able to convert to CSV correctly, maintaining the proper date.

So, I just took the original sheet I got and simplified and mad the data anonymous.
Hopefully this link to the xlsx will work:

Any feedback would be most helpful!