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.

Yes, I've researched the question here, here, and at stackoverflow and I know this seems like a duplicate question. Therefore, I'm making my question more specific: "How can I keep leading zeroes in a comma delimited, double-quoted fields csv file when viewing in Excel 2010?"

My customer's requirements:

  1. Must be CSV file
  2. Must be able double click file to view in Excel
  3. Must keep leading zeroes, especially in the zip code field.
  4. Must double quote fields to keep comma in name field such as Donald Duck, Jr.

My attempts.

  1. If I don't double quote the fields then "=06953" works great, however Donald Duck, Jr. get split into two separate columns which is not acceptable.
  2. If, when double quoting my fields, I try "=""06953"" as this suggests, then I get ="06953" in my cell.
share|improve this question
    
Why doesn't the first answer you linked solve the problem? –  uSlackr Jun 8 '12 at 18:12
    
It does if I don't double quote my fields. –  zundarz Jun 8 '12 at 18:59
    
Re first link... this requires user to have file named something other than csv and then import into excel. User doesn't want to do that. –  zundarz Jun 8 '12 at 19:06
    
=06953 has no leading zero, it starts with an "="? So your questions is a little hard to follow. –  techie007 Jun 8 '12 at 23:32
1  
Your last linkhas the solution, you just missed one quote: "=""06593""" (note the third closing quote) resolves to a field value of ="06593" while importing a quoted CSV file, which should get Excel to preserve leading zeros. –  kopischke Jun 9 '12 at 19:05
show 1 more comment

2 Answers

You miss the point about double quoting, to include double quote in field you should double it:

"=""634576345" => ="634576345

To keep leading zeroes you should present your numbers to Excel like strings so they should not be converted to numbers at all. I have no Excel installed anywhere right now but here are possible solutions that come in mind:

  • using comma instead of a point;
  • adding leading space symbol;
  • adding leading quote (') symbol (AFAIK Excel does this to distinguish ordinary strings from some-other-type-look-alike).
share|improve this answer
    
I don't want to keep any double quotes, rather I want to keep my leading zeroes which Excel removes. –  zundarz Jun 8 '12 at 20:28
add comment
up vote 0 down vote accepted

Here's a solution that works on Excel 2010

  1. Double quote delimit fields that don't have leading zeros. This will allow you to use double quotes on fields that contain commas.
  2. For fields that have leading zeros, use =" for the beginning field delimiter and " for the end field delimiter. This will keep the leading zeros for that field.

It is not necessary to double quote fields with leading zeroes, when you are using double quotes as a field delimiter.

share|improve this answer
add comment

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.