Leading Zeros in .CSV files
 

I have done this before but drawing a blank. I have a clolumn with "001" and
it is in a file sales.csv. I format the coumn the file in this manner:
Format = Style - Custom - and put in 3 zeros. It then displays the leading
zeros. But when I open the file it goes away. Then when I tried to import
the files and I get errors because the 001 doesnt matchl. However, If I do
the format and save then it will import. But this is a file that I import a
lot. I get the file from one program and have to open it to format it and
resave before I can import. I done this before and I thought there was an
options I had to set in Excel also. Any help?

Leading Zeros in .CSV files
 

Rename the .csv file so that it has an extension .txt, then with Excel
running you do File | Open (select "All files *.*" in the File Type
panel) and select your file. Excel will automatically enter the data
import wizard, where in the third stage you can specify that you want a
particular field to be imported as a text field - this will preserve
any leading zeros.

If you do this a lot you can record a simple macro once and then run
this whenever you need to import the file again.

Hope this helps.

Pete

klafert wrote:
I have done this before but drawing a blank. I have a clolumn with "001" and
it is in a file sales.csv. I format the coumn the file in this manner:
Format = Style - Custom - and put in 3 zeros. It then displays the leading
zeros. But when I open the file it goes away. Then when I tried to import
the files and I get errors because the 001 doesnt matchl. However, If I do
the format and save then it will import. But this is a file that I import a
lot. I get the file from one program and have to open it to format it and
resave before I can import. I done this before and I thought there was an
options I had to set in Excel also. Any help?

Leading Zeros in .CSV files
 

How about formating the coulmn to "Text". This should enable the display of
leading Zeros...

"klafert" wrote:

I have done this before but drawing a blank. I have a clolumn with "001" and
it is in a file sales.csv. I format the coumn the file in this manner:
Format = Style - Custom - and put in 3 zeros. It then displays the leading
zeros. But when I open the file it goes away. Then when I tried to import
the files and I get errors because the 001 doesnt matchl. However, If I do
the format and save then it will import. But this is a file that I import a
lot. I get the file from one program and have to open it to format it and
resave before I can import. I done this before and I thought there was an
options I had to set in Excel also. Any help?

Leading Zeros in .CSV files
 

Another solution is
instead of using File Open to open your .csv file
use Data Import external data Import data

This gives you the option to choose the format (delimited, comma), format as
text (not general).

--
Allllen


"klafert" wrote:

I have done this before but drawing a blank. I have a clolumn with "001" and
it is in a file sales.csv. I format the coumn the file in this manner:
Format = Style - Custom - and put in 3 zeros. It then displays the leading
zeros. But when I open the file it goes away. Then when I tried to import
the files and I get errors because the 001 doesnt matchl. However, If I do
the format and save then it will import. But this is a file that I import a
lot. I get the file from one program and have to open it to format it and
resave before I can import. I done this before and I thought there was an
options I had to set in Excel also. Any help?

Leading Zeros in .CSV files
 

Thanks for all the suggestion, but it has to have an extension of .csv in
order to be imported into an accounting software. This will be done once a
month. I really don't care what it looks like. I am not importing this into
excel. After the file is exported out of one software program, I have to
open the file up - format the column and then I can import the file into
another accounting program. I guess I will just have to do that , it doesn't
take that long.

"Allllen" wrote:

Another solution is
instead of using File Open to open your .csv file
use Data Import external data Import data

This gives you the option to choose the format (delimited, comma), format as
text (not general).

--
Allllen


"klafert" wrote:

I have done this before but drawing a blank. I have a clolumn with "001" and
it is in a file sales.csv. I format the coumn the file in this manner:
Format = Style - Custom - and put in 3 zeros. It then displays the leading
zeros. But when I open the file it goes away. Then when I tried to import
the files and I get errors because the 001 doesnt matchl. However, If I do
the format and save then it will import. But this is a file that I import a
lot. I get the file from one program and have to open it to format it and
resave before I can import. I done this before and I thought there was an
options I had to set in Excel also. Any help?

Leading Zeros in .CSV files
 

If you don't need to re-open the .CSV file in Excel, then don't.

Use Notepad to verify that the .CSV file looks ok.

It's not the saving that loses the leading 0's. It's the opening in excel that
causes your trouble.

klafert wrote:

Thanks for all the suggestion, but it has to have an extension of .csv in
order to be imported into an accounting software. This will be done once a
month. I really don't care what it looks like. I am not importing this into
excel. After the file is exported out of one software program, I have to
open the file up - format the column and then I can import the file into
another accounting program. I guess I will just have to do that , it doesn't
take that long.

"Allllen" wrote:

Another solution is
instead of using File Open to open your .csv file
use Data Import external data Import data

This gives you the option to choose the format (delimited, comma), format as
text (not general).

--
Allllen


"klafert" wrote:

I have done this before but drawing a blank. I have a clolumn with "001" and
it is in a file sales.csv. I format the coumn the file in this manner:
Format = Style - Custom - and put in 3 zeros. It then displays the leading
zeros. But when I open the file it goes away. Then when I tried to import
the files and I get errors because the 001 doesnt matchl. However, If I do
the format and save then it will import. But this is a file that I import a
lot. I get the file from one program and have to open it to format it and
resave before I can import. I done this before and I thought there was an
options I had to set in Excel also. Any help?

--

Dave Peterson

Leading Zeros in .CSV files
 

I have followed all the instructions regarding importing csv files into
Excel to keep the leading zeros, but it is still not keeping the zeros.

I have saved the file as a text file, then imported the data using the
wizard, changing the relevant columns to text. The data will import
correctly and looks good. However, if I then save the file as csv,
close it down and reopen it, I have lost all the formatting again.

I need to open the file in Excel to put headings on the columns, I then
need to save it as a csv as this is the format required by the client.

Can anyone help? As far as I can see, I am doing everything required,
but still losing the leading zeros!!!

Thanks

Leading Zeros in .CSV files
 

You haven't lost the leading 0's by saving it as a .CSV. Use Notepad to open
that CSV file and you'll see that they're still there.

You lose the leading 0's when you open the CSV file in excel.

So if you want to keep the leading 0's when you import the file into excel,
you'll have to rename it to .txt and follow the wizard.



Emma wrote:

I have followed all the instructions regarding importing csv files into
Excel to keep the leading zeros, but it is still not keeping the zeros.

I have saved the file as a text file, then imported the data using the
wizard, changing the relevant columns to text. The data will import
correctly and looks good. However, if I then save the file as csv,
close it down and reopen it, I have lost all the formatting again.

I need to open the file in Excel to put headings on the columns, I then
need to save it as a csv as this is the format required by the client.

Can anyone help? As far as I can see, I am doing everything required,
but still losing the leading zeros!!!

Thanks

--

Dave Peterson

Leading Zeros in .CSV files
 

Hi Dave,

Thanks for the quick reply. I now understand what is going on!!!
Basically I have got what I want I just didn't know it!!