Saving to CSV in Excel loses regional date format

excel csv export-to-csv
upshake picture upshake · Feb 8, 2013 · Viewed 113.3k times · Source

I have a .xls I need to convert to .csv

The file contains some date columns. The format on the date is "*14/03/2001" which, according to Excel means the date responds to regional date and time settings specified for the OS.

Opening in Excel you see:

20/01/2013
01/05/2013

Save as... CSV

Open in notepad:

01/20/2013
05/01/2013

I have temporarily fixed by setting date formats to "14/03/2001" (no *) but even some other custom formats with no *, like "d/mm/yyyy h:mm" get mangled when saved to CSV.

Answer

upshake picture upshake · May 8, 2014

Although keeping this in mind http://xkcd.com/1179/

In the end I decided to use the format YYYYMMDD in all CSV files, which doesn't convert to date in Excel, but can be read by all our applications correctly.

Related questions

Java - Writing strings to a CSV file

I am trying to write data to a csv file with java, however when I try opening the produced file with excel I am getting an error saying the file is corrupt. Upon opening the file in notepad it looks …

Read More
xls to csv converter

I am using win32.client in python for converting my .xlsx and .xls file into a .csv. When I execute this code it's giving an error. My code is: def convertXLS2CSV(aFile): '''converts a MS Excel file to csv …

Read More
Excel: macro to export worksheet as CSV file without leaving my current Excel sheet

There are a lot of questions here to create a macro to save a worksheet as a CSV file. All the answers use the SaveAs, like this one from SuperUser. They basically say to create a VBA function like this: …

Read More