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

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,


--
Chuck W

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?



ChuckW wrote:
>
> 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,
>
> --
> Chuck W

--

Dave Peterson

"ChuckW" <haji@hotmail.com> wrote in message
news:A94EF7D3-423C-4E1F-BFF2-A8E87B4E1763@microsoft.com...
> 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?
--
David Biddulph

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,
--
Chuck W


"Dave Peterson" wrote:

> 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?
>
>
>
> ChuckW wrote:
> >
> > 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,
> >
> > --
> > Chuck W
>
> --
>
> Dave Peterson
>

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,
--
Chuck W


"David Biddulph" wrote:

> "ChuckW" <haji@hotmail.com> wrote in message
> news:A94EF7D3-423C-4E1F-BFF2-A8E87B4E1763@microsoft.com...
> > 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?
> --
> David Biddulph
>
>
>

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.




ChuckW wrote:
>
> 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,
> --
> Chuck W
>
> "Dave Peterson" wrote:
>
> > 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?
> >
> >
> >
> > ChuckW wrote:
> > >
> > > 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,
> > >
> > > --
> > > Chuck W
> >
> > --
> >
> > Dave Peterson
> >

--

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 wrote:
>
> 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,
> --
> Chuck W
>
> "David Biddulph" wrote:
>
> > "ChuckW" <haji@hotmail.com> wrote in message
> > news:A94EF7D3-423C-4E1F-BFF2-A8E87B4E1763@microsoft.com...
> > > 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?
> > --
> > David Biddulph
> >
> >
> >

--

Dave Peterson

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,

--
Chuck W


"Dave Peterson" wrote:

> 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.
>
>
>
>
> ChuckW wrote:
> >
> > 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,
> > --
> > Chuck W
> >
> > "Dave Peterson" wrote:
> >
> > > 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?
> > >
> > >
> > >
> > > ChuckW wrote:
> > > >
> > > > 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,
> > > >
> > > > --
> > > > Chuck W
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

"ChuckW" <haji@hotmail.com> wrote in message
news:A6809249-B7EE-4E89-8E44-BCD796151804@microsoft.com...
> "David Biddulph" wrote:
>
>> "ChuckW" <haji@hotmail.com> wrote in message
>> news:A94EF7D3-423C-4E1F-BFF2-A8E87B4E1763@microsoft.com...
>> > 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?

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

> 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

"ChuckW" <haji@hotmail.com> wrote in message
news:49190A63-0144-47FE-8A17-B2D7B53E4FF2@microsoft.com...
> 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.]
--
David Biddulph

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


"David Biddulph" wrote:

> "ChuckW" <haji@hotmail.com> wrote in message
> news:A6809249-B7EE-4E89-8E44-BCD796151804@microsoft.com...
> > "David Biddulph" wrote:
> >
> >> "ChuckW" <haji@hotmail.com> wrote in message
> >> news:A94EF7D3-423C-4E1F-BFF2-A8E87B4E1763@microsoft.com...
> >> > 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?
>
> >> Is the leading zero really there in the data in Excel, or is it part of
> >> the
> >> cell formatting?
>
> > 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
>
>
>

I'm very surprised microsoft hasn't taken steps to correct this problem - it has caused me no end of grief - I do a lot of data conversions and if I forget to change the csv file to a txt file I always have problems