Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving a CSV file from Excel with 0 in front of Zip code
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving a CSV file from Excel with 0 in front of Zip code
"ChuckW" wrote in message
... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving a CSV file from Excel with 0 in front of Zip code
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving a CSV file from Excel with 0 in front of Zip code
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving a CSV file from Excel with 0 in front of Zip code
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving a CSV file from Excel with 0 in front of Zip code
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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving a CSV file from Excel with 0 in front of Zip code
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving a CSV file from Excel with 0 in front of Zip code
"ChuckW" wrote in message
... "David Biddulph" wrote: "ChuckW" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving a CSV file from Excel with 0 in front of Zip code
"ChuckW" wrote in message
... 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving a CSV file from Excel with 0 in front of Zip code
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" wrote in message ... "David Biddulph" wrote: "ChuckW" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2003 saving a file | Excel Discussion (Misc queries) | |||
How do I stop Excel from saving a file with a different name? | Excel Discussion (Misc queries) | |||
Using other workbooks.. | Excel Worksheet Functions | |||
saving excel file as tab delimited | Excel Discussion (Misc queries) | |||
Opening and saving Excel 2003 file from Excel 97. | Excel Discussion (Misc queries) |