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

 

[David Biddulph]

Please understand. A CSV is a TEXT file. No formatting. You can open and
close the CSV as a text file (using Notepad or any other text editor) as
often as you like, and it won't lose the leading zeroes. Again I'll say it;
a CSV file doesn't include formatting.

The problem arises if you use Excel to open the CSV, and if you let Excel
use its default settings. If you want to open the file with Excel, and if
you want Excel to keep the data unaltered, import the data as text. Either
rename the file as TXT instead of CSV, or use Excel's Data/ Import External
Data, and specify each column as text. [Another option might be to change
the Windows Regional Options to set the default date format to suit your
requirements.]