Saving a CSV file from Excel with 0 in front of Zip code

[ChuckW]

Hi,

I am trying to import some addresses into UPS worldship software. I need it
in the form of a CSV file. It does not accept excel as an import engine. I
have several addresses with zip codes which start with zero. I am able to
keep the zero in front of the zip code in Excel but when I go to save it as a
CSV file it drops the zero. Worldship then rejects the record since it is
not a valid address. I need to find a way to have a zip code which starts
with a zero saved as a CSV file. Can anyone help?

Thanks,

 

[Dave Peterson]

How did you verify the zipcode format (for leading 0)?

Did you reopen the .csv file in excel or did you open the file in Notepad?

 

[David Biddulph]

Hi,

I am trying to import some addresses into UPS worldship software. I need
it
in the form of a CSV file. It does not accept excel as an import engine.
I
have several addresses with zip codes which start with zero. I am able to
keep the zero in front of the zip code in Excel but when I go to save it
as a
CSV file it drops the zero. Worldship then rejects the record since it is
not a valid address. I need to find a way to have a zip code which starts
with a zero saved as a CSV file. Can anyone help?

Thanks,

Is the leading zero really there in the data in Excel, or is it part of the
cell formatting?

 

[ChuckW]

Dave,

Here is the whole process of what I did. I exported data out of worldship
in Excel. When I did this it stripped the leading zeros out of records of
zip codes that begin with a zero. I then added a bunch of missing
information such as phone numbers and e-mails. I save the zip code field as
a text field and then sorted by zip code and use the concatenate to add
zeros back in and then saved my excel file. I then saved it as a csv file
from Excel. I then opened this csv file and noticed that it had stripped
out the zeros from my zip codes. I can't seem to add them either.

Thanks,

 

[ChuckW]

David,

I think it is in there. When the zero's had been stripped out originally I
inserted a column and added a zero to them. I then concatenated the two
columns. There is this green triangle in the upper left corner of all of the
cells in the zip code field that start with a zero. When I click on it, it
give me choices such as ignore error or convert to number. If I convert it
to a number it strips off the zero so I chose ignore error. Does that help?

Thanks,

 

[Dave Peterson]

How did you open that CSV file--did you use excel or did you use NotePad?

As an aside, If the zip code field is really numbers, you can give that column a
custom format of: 00000 instead of concatenating the leading 0's.

Dave,

Here is the whole process of what I did. I exported data out of worldship
in Excel. When I did this it stripped the leading zeros out of records of
zip codes that begin with a zero. I then added a bunch of missing
information such as phone numbers and e-mails. I save the zip code field as
a text field and then sorted by zip code and use the concatenate to add
zeros back in and then saved my excel file. I then saved it as a csv file
from Excel. I then opened this csv file and noticed that it had stripped
out the zeros from my zip codes. I can't seem to add them either.

Thanks,

 

[Dave Peterson]

Don't use Excel to check the data. Open the .csv file in Notepad.

Excel will strip any leading 0 from a number when it opens it. It doesn't mean
that the leading 0 isn't there--it just means that when excel re-opens the file,
it'll remove the leading 0.

 

[ChuckW]

Dave,

When I open it in notepad the zeros are there. When I save it as a text file
though they dissapear. I am saving it as a text file but it looks like an
Excel/CSV file rather than the notepad icon. Does this help?

Thanks,

 

[David Biddulph]

I think it is in there. When the zero's had been stripped out originally
I
inserted a column and added a zero to them. I then concatenated the two
columns. There is this green triangle in the upper left corner of all of
the
cells in the zip code field that start with a zero. When I click on it,
it
give me choices such as ignore error or convert to number. If I convert
it
to a number it strips off the zero so I chose ignore error. Does that
help?

In that situation (with green triangles showing in a text field), when I
produce a CSV, the leading zero is still there providing that I actually
read the CSV in something like Notepad. If I reimport the CSV into Excel it
will treat it as a number and lose the leading zero. Have you actually
looked at your CSV file with Notepad?

What format were the fields in Excel? Are you sure that that they were
text? [Do a Format/ Cells to check.]

 

[David Biddulph]

Dave,

When I open it in notepad the zeros are there. When I save it as a text
file
though they dissapear. I am saving it as a text file but it looks like an
Excel/CSV file rather than the notepad icon. Does this help?

So if when you open it in Notepad the zeroes are there, the problem isn't
with Excel writing the CSV file, it is with the program that you are later
using to read the CSV file.

What do you mean about "saving it as a text file"? CSV already *is* a text
file (as you've seen in Notepad). You can change its extension to anything
else (such as TXT) if you wish to do so. If you are reading the CSV into
Excel and then using that to save as TXT, then of course you'll lose the
leading zeroes. [If you did want to read such a CSV into Excel without
losing the data, change the extension to TXT and read it in with the wizard,
ensuring that you select text as the format for the relevant columns at the
final stage.]

 

[ChuckW]

Hi,

Opening the file in notepad did the trick. I can now import this file into
my program.

Thanks for everyone's help.


--
Chuck W

I think it is in there. When the zero's had been stripped out originally
I
inserted a column and added a zero to them. I then concatenated the two
columns. There is this green triangle in the upper left corner of all of
the
cells in the zip code field that start with a zero. When I click on it,
it
give me choices such as ignore error or convert to number. If I convert
it
to a number it strips off the zero so I chose ignore error. Does that
help?

In that situation (with green triangles showing in a text field), when I
produce a CSV, the leading zero is still there providing that I actually
read the CSV in something like Notepad. If I reimport the CSV into Excel it
will treat it as a number and lose the leading zero. Have you actually
looked at your CSV file with Notepad?

What format were the fields in Excel? Are you sure that that they were
text? [Do a Format/ Cells to check.]