Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop Excel from stripping out leading zeros when saving as CSV
Hi, I am getting strange behaviour from Excel when working with CSV files.
It's quite easy to reproduce the problem: 1) In a text editor, such as Notepad, create the file "test.csv", containing this data: 001,hello 2) Close the file 3) Open the same file up in Excel and change the value in B1 to "goodbye" 4) Save, clicking "Yes" when Excel asks if I want to keep CSV format, and close Excel 6) Open the file in the text editor. It now contains: 1,goodbye In other words, Excel has decided that "001" should be saved as "1". Is there any way to stop this? Regards Matthew |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop Excel from stripping out leading zeros when saving as CSV
It's not the saving, it's the re-opening. After you save the file as .csv,
open the csv with notepad, the leading zero will be there. If you rename the file to .txt and then open it with Excel, Excel will bring up the Import Wizard and allow you to specify text. -- Gary's Student "MattM" wrote: Hi, I am getting strange behaviour from Excel when working with CSV files. It's quite easy to reproduce the problem: 1) In a text editor, such as Notepad, create the file "test.csv", containing this data: 001,hello 2) Close the file 3) Open the same file up in Excel and change the value in B1 to "goodbye" 4) Save, clicking "Yes" when Excel asks if I want to keep CSV format, and close Excel 6) Open the file in the text editor. It now contains: 1,goodbye In other words, Excel has decided that "001" should be saved as "1". Is there any way to stop this? Regards Matthew |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop Excel from stripping out leading zeros when saving as CSV
Many thanks for the help, Gary's Student.
However, I'm not sure what you're saying is correct. Have another look at my original post. After saving the file in Excel, I then do what you've suggested (see my step 6 - which should be step 5! sorry): open the CSV file in a text editor. And the zeros are gone. In other words, it's not a presentation issue - Excel really has stripped out the leading zeros. Despite the fact that I didn't go anywhere near the relevant part of the file (namely, cell A1), during my editing. All the best MattM |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop Excel from stripping out leading zeros when saving as CSV
"MattM" wrote in message
... Many thanks for the help, Gary's Student. However, I'm not sure what you're saying is correct. Have another look at my original post. After saving the file in Excel, I then do what you've suggested (see my step 6 - which should be step 5! sorry): open the CSV file in a text editor. And the zeros are gone. In other words, it's not a presentation issue - Excel really has stripped out the leading zeros. Despite the fact that I didn't go anywhere near the relevant part of the file (namely, cell A1), during my editing. What he was saying was that the problem was when you read the original .CSV file into Excel. If you rename the original file to .TXT then you can specify the cell formats as text when you read the file in, but if you merely read a .CSV file into Excel, it will make its own mind up on the cell format and you'll get the problem you reported. -- David Biddulph |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop Excel from stripping out leading zeros when saving as CSV
Both - many thanks for the information. I'm now finding that using the import
wizard followed by "Save As CSV" causes Excel to convert the comma-separated text file into a tab-separated CSV file (!) which is equally frustrating. Still, you're right - it doesn't strip out the leading zeros and I should be able to find some workarounds with a bit more experimentation. I'm also toying with the idea of writing a macro which adds leading zeros preceeded by an apostrophe to all the cells in a given column, which might prove fruitful. Thanks again, MattM What he was saying was that the problem was when you read the original .CSV file into Excel. If you rename the original file to .TXT then you can specify the cell formats as text when you read the file in, but if you merely read a .CSV file into Excel, it will make its own mind up on the cell format and you'll get the problem you reported. -- David Biddulph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
editing an excel 97 file within excel 2003 and saving back as exce | Excel Discussion (Misc queries) | |||
How to prevent Excel from deleting leading zeros? | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Spliting a number with leading zeros | Excel Discussion (Misc queries) | |||
Displaying leading zeros in an Excel spreadsheet | Excel Discussion (Misc queries) |