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.

Dressmaker

I would like the dates to be preserved in their original format. – Dressmaker

You can try to set the last parameter (Local) of SaveAs to be true; this should keep your original excel date format. – Unhallowed

Answer 1

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.

Dressmaker

This is what I'm trying to achieve. The CSV keeps changing it back the other way around – Platas

Answer 2

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.

Farouche

I cannot believe that this is the solution to this problem. Thanks – Coonan

I don't see "Calendar Settings" in Windows 7. – Menander

Answer 3

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:

1. 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" )

2. Insert a blank column DATE_NEW next to your existing date column.

3. Paste the contents of DATE_TMP into DATE_NEW using the "paste as value" option.

4. Remove DATE_TMP and your existing date column, rename DATE_NEW to your old date column.

5. Save as csv.

Inadvertency

This didn't work for me. Changed format to YYYY-MM-DD and the column type Text but on reopening it still appears as DD/MM/YYYY. – Langobardic

@Langobardic - the question was how to get Excel to save a particular date format, not how to prevent it from messing up the date format each time it opens. The approach above does not prevent Excel from messing up the date each time it reopens, but it at least allows you to save it. – Inadvertency

You're right, my bad. I checked it in a text editor and the values are converted correctly. – Langobardic

Answer 4

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.

Narrate

Answer 5

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

Boydboyden

Confirmed that this works. Open the .csv file in notepad after you safe it to confirm you see the expected date formatting. Opening the .cvs file in Excel after you save it will format the date using the default US format. – Adkinson

It's worth noting that the date will then be delimited by double-quotation marks, e.g. "May 10, 1960". – Sciolism

...in the CSV file. – Sciolism

Answer 6

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.

Draftee

Answer 7

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.

Member

Answer 8

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 & close the book. savechanges parameter is true.

Renitarenitent

Answer 9

You can send your date to excel not like the "date" but like just a "string" with empty space at the start and end of the string.

Your problem is happening due to excel see your '11/02/2023' like a date, but you can hack it and change this date to a simple string like ' 11/02/2023 '.

Old: '11/02/2023'

New (solution): ' 11/02/2023 '

Ainsley

Answer 10

Had a similar issue with reading data from a recordset to process and then output as a CSV. The following seemed to have fixed it:

Imps.Cells(ImpRow, 17).Value = **"'" & Format(srst(2).Value, "dd/mm/yyyy")**

With the relevant column (17) formatted as Text.

srst is the recordset data, Imps and ImpRow are just Sheet and Row pointers.

Apathy