Forum Discussion
Excel changes custom format to a date when saving as a csv. File. How to get itnto stop?
I was running into this same issue. We needed the date field to be mm/dd/yyyy and when saving as "CSV (Comma delimited) (*.csv)" the date field would go back to m/d/yyyy which is not an acceptable value for the system I was uploading it into (Cornerstone CSX). Well, it turns out that it might have been seeing it as a special character when Excel saved. I changed the file type option to "CSV UTF-8 (Comma delimited) (*.csv)" and the date remained in the format I needed and uploaded perfectly into the system I was needing it for. So, people on the thread if you haven't found a solution give that a try.
A CSV file is a plain text file, not an Excel workbook. It doesn't "know" about Excel formats.
The problem probably occurs when you open the CSV file in Excel again.
To avoid it, change the extension from .csv to .txt.
When you open a .txt file, Excel displays a wizard in which you can specify how to interpret each column.
HansVogelaar The problem is a conversion problem from Excel to CSV, not the other way around! When converting a Text formatted Excel file to CSV, the date changes form a custom date of mm-dd-yyyy to mm/dd/yyyy which fails to upload in a governmental program that requires mm-dd-yyyy. It is my belief, along with many others using this governmental program that Excel does not have a standard mm-dd-yyyy date format, so, even though it is formatted as text, the conversion changes the file as what it recognizes as a date.
Jackie64 wrote: ``The problem is a conversion problem from Excel to CSV, not the other way around!``
As Hans explained, that is not correct. But the "debate" can be resolved very simply.
Jackie, please attach the __original__ Excel file and the __saved__ CSV file.
Of course, the "original" Excel file does not have to contain any proprietary information. Simply create an __example__ Excel file that demonstrates the problem.
According to your description, it should require only one cell with a value displayed in the form mm-dd-yyyy.
Contrary to what Hans implies, it does not matter if the data is text or a numeric date. All that should matter is how the date appears in Excel before saving as CSV.
-----
Also, please clarify....
You wrote: the CSV file ``fails to upload in a governmental program that requires mm-dd-yyyy. It is my belief, along with many others using this governmental program that Excel does not have a standard mm-dd-yyyy date format``.
If another application ("a government program") is reading the CSV file, what difference does it make that Excel does not have a standard date format?
Is the "government program" an Excel file, or does the government application otherwise rely on Excel to open / read / interpret the CSV file?
This answer might be critical in determining your options for working around the problem.
Obviously, we cannot change the way a third-party program opens / reads / interprets the CSV file.
