Saving to CSV in Excel loses regional date format

Question

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

User · Answer

Place an apostrophe in front of the date and it should export in the correct format  Just found it out for myself  I found this thread searching for an answer

User · Answer

You need to do a lot more work than 1  click export 2  Open file   I think that when the Excel CSV documentation talks about OS and regional settings being interpreted  that means that Excel will do that when it opens the file  which is in their  special  csv format    See this article   Excel formatting and features are not transferred to other file formats   Also  Excel is actually storing a number  and converting to a date string only for display   When it exports to CSV  it is converting it to a different date string   If you want that date string to be non-default  you will need to convert your Excel cells to strings before performing your export   Alternately  you could convert your dates to the number value that Excel is saving   Since that is a time code  it actually will obey OS and regional settings  assuming you import it properly   Notepad will only show you the 10-digit number  though

User · Answer

You can save your desired date format from Excel to  csv by following this procedure  hopefully an excel guru can refine further and reduce the number of steps    Create a new column DATE TMP and set it equal to the  TEXT  oldcolumn   date-format-arg    formula    For example  in your example if your dates were in column A the value in row 1 for this new column would be    TEXT  A1   dd mm yyyy      Insert a blank column DATE NEW next to your existing date column  Paste the contents of DATE TMP into DATE NEW using the  paste as value  option  Remove DATE TMP and your existing date column  rename DATE NEW to your old date column  Save as csv

User · Answer

Change the date and time settings for your computer in the  short date  format under calendar settings   This will change the format for everything yyyy-mm-dd or however you want it to display  but remember it will look like that even for files saved on your computer   At least it works

User · Answer

If you use a Custom format  rather than one of the pre-selected Date formats  the export to CSV will keep your selected format  Otherwise it defaults back to the US format

User · Answer

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

User · Answer

Change the date range to  General  format and save the workbook once  and change them back to date format  eg  numberformat    d m yyyy   before save  amp  close the book  savechanges parameter is true

User · Answer

A not so scalable fix that I used for this is to copy the data to a plain text editor  convert the cells to text and then copy the data back to the spreadsheet

[excel] Saving to CSV in Excel loses regional date format

Examples related to excel

Examples related to csv

Examples related to export-to-csv