Losing date format when saving .csv files

[Harley]

I am exporting accounting data out of legacy database into .csv file format.
The text data is not delimited, so I use Text to Columns and set the date
fields to Text (from General). The date formats retain formatting (in my
case, mm/dd/yyyy). I save the file as a .csv. When I reopen the file, I
lose the date formatting. The date fields revert to m/d/yyyy.

I have to use .csv file in order to upload to a 3rd party.

What can I do to retain the mm/dd/yyyy format when saving as a .csv file?

 

[Bob I]

CSV files have no formatting. They are simply plain text files with a
..CSV extension, and provide no additional information about the
data/text. If you open the file with Notepad you will see that it isn't
the file loosing/changing the formatting, but the application.

 

[Harley]

Thanks for the quick response. After looking into this, I see that the date
fields are not changing formats, but they are dropping their leading 0's.
Can I prevent that from happening?

 

[Bob I]

Where are they dropping their leading 0's? If they exist in the .CSV
file, then they aren't dropped, if they don't exist in csv file, look at
the application that created it.

 

[Gord Dibben]

One method.

In a helper column enter =TEXT(Cellref,"mm/dd/yyyy")

Copy down.

Copy the helper column, select the original dates column and Paste
Special>Values>OK>Esc

Delete the helper column.

Save As *.csv


Gord Dibben MS Excel MVP

 

[Bob I]

Hi Gord,

He's not creating it in Excel.

Re: "I am exporting accounting data out of legacy database into .csv
file format."

 

[Harley]

The leading 0's exist in the date fields when I export the data out of the
accounting application and into a .csv file. They are there in the csv file.
I save the file as a csv, and close it. When I reopen the file, the leading
0's are gone. For example, "03/05/2008" becomes "3/5/2008". I exported the
data into a txt file, and the leading 0's are present. I even manually typed
data (a date) into a csv file, saved it, closed it, and when I reopened it,
the leading 0's were gone.

Help.

 

[Harley]

Thanks. Tried this and the date format worked. Unfortuanately, I closed the
the file and reopened it. The leading 0's were gone. mm/dd/yyy reverted to
m/d/yyyy

Any ideas?

 

[Bob I]

Configure/format to show the Date in the format desired.

 

[Gord Dibben]

In what application did you open the *.csv file?


Gord

 

[lfcgreene]

simply change the short date format in the Data and Time calendar settings in the bottom right corner of your desktop