Setting date format when exporting CSV

Topics: User Forum
Oct 27, 2009 at 12:34 PM

Hi, I'm having a problem with using PHPExcel to convert from .xls and .xlsx to csv, which is that dates often end up being written like 12/10/09 or something equally ambigious. This then causes problems when reading the CSV file as the program reading it isn't able to determine exactly what date it is.

So I'm wondering, is there a (preferably easy) way of setting what format is used for dates when writing CSV files? I'd really like all dates to be written in ISO-format YYYY-MM-DD in the CSV file.

I suppose iterating over the entire file and setting the cell to text, converting the value from Excel's timestamp format to a Unix timestamp and then setting the cell's value manually using date() could do the trick, but is there perhaps an easier way?

And my apologies if this has been answered somewhere else, I was unable to find something that was quite the same as this.

Oct 29, 2009 at 3:00 AM

Question 1:

Do you know in advance which columns in the xls/xlsx file contain dates? Or are they scattered all around in the worksheet?

Question 2:

Do you have a recipe how you to achieve this using MS Office Excel? That is how to save the desired CSV file by using functionality in MS Office Excel. If so, maybe we can better find the correct solution within PHPExcel.

Oct 29, 2009 at 10:02 AM
Edited Oct 29, 2009 at 10:21 AM

Answer 1:

Yes, the file has to be according to our specification, with only certain columns containing dates.

Answer 2:

At least in OpenOffice Calc (don't have MS Office available right now) I can select the whole column, choose "format cells", and then select category "date" and the correct date format YYYY-MM-DD. If I then save this file as .xls and send it through my xls2csv PHP script the dates are formatted correctly in the CSV file. So it's clearly possible to save the date format in the xls file somehow, and PHPExcel does treat it right as far as I can see.

Formatting cells:

XLS file:

Oct 30, 2009 at 6:30 PM

Let's say the dates are in column F. Can you try like this?

$file = 'read.xls';
$excel = PHPExcel_IOFactory::load($file);

$worksheet = $excel->getActiveSheet();

$highestRow = $worksheet->getHighestRow();


$writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');


Note: In the future it should be possible to do like this instead:


but there are still a couple of details that need fixing before column styles are working 100% in PHPExcel

Aug 13, 2010 at 11:48 PM

Thanks Koyoma,

I applied your sample and it worked. I did some date formatting like this:


Thanks Again