leading zero on a zipcode in a csv file

jbyrne · May 14th, 2002, 01:05 PM

I'm having a problem leaving a leading zero on a zipcode when saving it as a csv.

Also, in a another column I have account numbers that are sixteen digits long. Again, when I open the file as a csv, it contains a E+15 extentions. Any help on resolving these 2 issues? I need to save this file as a csv file

Yogi Anand · May 14th, 2002, 01:13 PM

For the first one, you can format the cella as SPECIAL --> ZipCode

For the second part, Excel is limited to 15 places -- so you can't display 16 significant digits.

Regards!

jbyrne · May 14th, 2002, 01:19 PM

YOGI,

Thanks for your reply. I'm formatting the column under special then zip code. It puts the leading zero there but after saving and closing the csv file. I re-open the file and the leading zeros are gone.

Jim

Jay Petrulis · May 14th, 2002, 01:21 PM

Hi,

To save the account numbers, you must treat them as text. I think they will survive the transfer to .CSV and back only if they are recgonized as text.

Bye,
Jay

klb · May 14th, 2002, 01:32 PM

I have had the same problem saving a zip code in CSV format. I have tried formatting with the special zip code and as text. I have also tried adding a ' at the start of the field. In each case, when I tried to reopen it, Excel thought it was a number and stripped the zero. If I add a " as a label prefix, it will keep the zero.

Also the E+15 is scientific notation. It does not mean that there are more than 15 places after the decimal. Same problems and same solution. Save it with a " prefix.

Tommy Bak · May 14th, 2002, 01:53 PM

Hi
I agree with klb.
The problem is not when saving, but when opening a csv-file.
If you take a look at the file in an editon it is ok.
A solution is renaming the file to *.txt
In the importguide you can change the whole column to text before opening the file.
It will now be read properly. (as saved)
regards Tommy

PDuPre' · May 14th, 2002, 02:25 PM

Yes. The CSV file is opened automatically by Excel, unlike the .txt file which automatically brings up the wizard. As a result Excel automatically treats all columns as "general" instead of text. Thus, all leading zeros etc. will be wiped since Excel thinks that column is for numbers.

Opening a .txt triggers the wizard when opening it in Excel. However, if you need to save it as a .csv to be imported in another application everything is actually saved in the correct format. One trick is to rename the file from filename.csv to filename.txt before opening it in Excel if you need to edit the .csv file.

Philip

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 14th, 2002, 01:05 PM	#1
jbyrne Board Regular Join Date: Feb 2002 Posts: 165	I'm having a problem leaving a leading zero on a zipcode when saving it as a csv. Also, in a another column I have account numbers that are sixteen digits long. Again, when I open the file as a csv, it contains a E+15 extentions. Any help on resolving these 2 issues? I need to save this file as a csv file

May 14th, 2002, 01:13 PM	#2
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	For the first one, you can format the cella as SPECIAL --> ZipCode For the second part, Excel is limited to 15 places -- so you can't display 16 significant digits. Regards!

May 14th, 2002, 01:19 PM	#3
jbyrne Board Regular Join Date: Feb 2002 Posts: 165	YOGI, Thanks for your reply. I'm formatting the column under special then zip code. It puts the leading zero there but after saving and closing the csv file. I re-open the file and the leading zeros are gone. Jim

May 14th, 2002, 01:21 PM	#4
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi, To save the account numbers, you must treat them as text. I think they will survive the transfer to .CSV and back only if they are recgonized as text. Bye, Jay

May 14th, 2002, 01:32 PM	#5
klb Board Regular Join Date: Apr 2002 Location: Minnesota Posts: 821	I have had the same problem saving a zip code in CSV format. I have tried formatting with the special zip code and as text. I have also tried adding a ' at the start of the field. In each case, when I tried to reopen it, Excel thought it was a number and stripped the zero. If I add a " as a label prefix, it will keep the zero. Also the E+15 is scientific notation. It does not mean that there are more than 15 places after the decimal. Same problems and same solution. Save it with a " prefix.

May 14th, 2002, 01:53 PM	#6
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	Hi I agree with klb. The problem is not when saving, but when opening a csv-file. If you take a look at the file in an editon it is ok. A solution is renaming the file to *.txt In the importguide you can change the whole column to text before opening the file. It will now be read properly. (as saved) regards Tommy

May 14th, 2002, 02:25 PM	#7
PDuPre' Board Regular Join Date: Apr 2002 Location: Trussville, AL Posts: 134	Yes. The CSV file is opened automatically by Excel, unlike the .txt file which automatically brings up the wizard. As a result Excel automatically treats all columns as "general" instead of text. Thus, all leading zeros etc. will be wiped since Excel thinks that column is for numbers. Opening a .txt triggers the wizard when opening it in Excel. However, if you need to save it as a .csv to be imported in another application everything is actually saved in the correct format. One trick is to rename the file from filename.csv to filename.txt before opening it in Excel if you need to edit the .csv file. Philip