2

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.
6
  • Why doesn't the first answer you linked solve the problem?
    – uSlackr
    Jun 8, 2012 at 18:12
  • It does if I don't double quote my fields.
    – zundarz
    Jun 8, 2012 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, 2012 at 19:06
  • =06953 has no leading zero, it starts with an "="? So your questions is a little hard to follow. Jun 8, 2012 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, 2012 at 19:05

2 Answers 2

2

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.

1

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 (or point instead of the comma if your locale uses comma as a decimal separator);
  • adding leading space symbol;
  • adding leading quote (') symbol (AFAIK Excel does this to distinguish ordinary strings from some-other-type-look-alike).
2
  • I don't want to keep any double quotes, rather I want to keep my leading zeroes which Excel removes.
    – zundarz
    Jun 8, 2012 at 20:28
  • using comma instead of a point isn't a general solution. In French e.g, the decimal point separator is , instead of ..
    – Jack
    Jul 23, 2014 at 5:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .