Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a CSV text file with the following content:

"Col1","Col2"
"01",A
"2",B
"10", C

When I open it up with Excel, it displays as shown here:

Note that Cell 2A attempts to display "01" as a number without a leading 0.

enter image description here

When I format rows 2 through 4 as "Text", it changes the display to

enter image description here

...but still the leading "0" is gone.

Is there a way to open up a CSV file in XLS and be able to see all of the leading zeros in the file by flipping some option? I do not want to have to retype '01 in every cell that should have a leading zero. Furthermore, using a leading apostrophe necessitates that the changes be saved to a XLS format when CSV is desired. My goal is simply to use Excel to view the actual content of the file as text without Excel trying to do me any formatting favors.

share|improve this question
    
The only way I've figured this out is arduous, but seems to work. Copy and paste to a new sheet. Set the entire pasted area to "text" format, then delete it, and paste in the values again. Bob's your aunty. –  user3463 Jun 1 '12 at 22:34
2  
(For our non-UK users, "Bob's your aunty" is an idiom which loosely translates to "and you're all set".) –  iglvzx Jun 1 '12 at 22:42
    
Robert was not my aunt. :-) I thought I had success doing that before, but it didn't work. Try it. What confused me is why formating the cell as text does not result in the value "02" being dispkayed with a leading 0 left justified. –  Chad Jun 2 '12 at 0:49
    
if the csv is for excel only, you can use explicit numbers as text - e.g. if you write '01 in excel, it will display 01 without the single quote.. –  Aprillion Jun 5 '12 at 8:19

2 Answers 2

up vote 5 down vote accepted

When you open the csv, you'll have the option to specify the delimiter and data type for each column. The text import wizard has 3 steps. Note that my screen shots are from Excel 2010, but this will work in exactly the same manner in Excel 2003.

Step 1: select delimited (instead of fixed width)

Step 2: select comma as your delimiter

Step 3: select each column and change the data format to "text". (You will see the word Text displayed above each column as in the screen shot below.)

enter image description here

The leading zeros will no longer be removed:

enter image description here

UPDATE: If you don't want to navigate through the process to get Excel to cooperate, you can use CSVEd, an editor designed specifically for editing delimited text files. A good text editor like Notepad++ might also work, but you won't be able to see your data arranged in columns.

share|improve this answer
    
1  
My issue was that my file had a csv extension and the Import dialog never comes up. when I renamed the file to have a text extension, I was all set and knew where to go. ty. –  Chad Jun 2 '12 at 0:50
    
Here's a complication: After importing it as you describe, I then want the ability to modify the file and to save it back as CSV. It presents me with a convoluted ugly dialog box (c'mon microsoft-design a custom dialog form for this instead of using the MessageBox Windows feature!). I choice the option to "keep it in the same format". When I do so, it defaults to a tab separated format. To ensure that the txt file is saved with a comma separator, you have to change the file name extension back to "csv." Is this ugly of what? After 10 versions of Excel we have this? –  Chad Jun 2 '12 at 1:08
    
Hi Velika, see the update at the bottom of my answer. You may be better off not using Excel at all. –  Head of Catering Jun 2 '12 at 18:33
    
Yeah, I was already looking for a CSV editor. I saw CSVEd. My initial impression is that it looks like a confusing ametuerish interface. I think "Tabular Text Editor" looks better. Thanks for your help. –  Chad Jun 3 '12 at 22:06

format the column so it displays how you want it to (e.g. 00 for 2 digits)
This has the advantage it will be saved with those digits (but you would have to alter the formatting every time you edit the CSV)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.