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?

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 wrote:
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?

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" wrote:

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 wrote:
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?

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.

Harley wrote:
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" wrote:


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 wrote:

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?

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
SpecialValuesOKEsc

Delete the helper column.

Save As *.csv


Gord Dibben MS Excel MVP

On Wed, 31 Dec 2008 08:47:01 -0800, Harley
wrote:

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?

Hi Gord,

He's not creating it in Excel.

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

Gord Dibben wrote:

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
SpecialValuesOKEsc

Delete the helper column.

Save As *.csv


Gord Dibben MS Excel MVP

On Wed, 31 Dec 2008 08:47:01 -0800, Harley
wrote:


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?

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.

"Bob I" wrote:

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.

Harley wrote:
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" wrote:


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 wrote:

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?

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?

"Gord Dibben" wrote:

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
SpecialValuesOKEsc

Delete the helper column.

Save As *.csv


Gord Dibben MS Excel MVP

On Wed, 31 Dec 2008 08:47:01 -0800, Harley
wrote:

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?

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

Harley wrote:

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.

"Bob I" wrote:


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.

Harley wrote:

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" wrote:



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 wrote:


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?

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


Gord

On Wed, 31 Dec 2008 12:09:00 -0800, Harley
wrote:

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.

"Bob I" wrote:

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.

Harley wrote:
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" wrote:


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 wrote:

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?

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.]
--
David Biddulph

Harley wrote:
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.

"Bob I" wrote:

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.

Harley wrote:
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" wrote:


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 wrote:

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?