1

We all know Excel's annoying behavior of removing leading zeros from imported CSV files

I am a developer and I'd like to program an export feature that creates a CSV file that forces Excel to keep the leading zeros.

I hoped "012345" would result in 012345, but even then Excel 2010 strips the leading zero ;(

Naming the file *.txt instead of *.csv forces Excel to use the wizard as https://www.youtube.com/watch?v=9KDK1FRcmSo suggested. But I really doubt that the user knows how to operate the wizard. He must change the data type from "General" to "Text" at one of 30 columns. I believe the user will just click Next, Next, Finish.

Any better idea that avoids using the wizard?

Is there an official character to "mask" a zero while importing a CSV file into Excel? I've tried ' (Typewriter apostrophe). But it's not the same to enter '0123 in Excel compared to open a CSV with '0123.

  • record yourself importing the csv with the wizard, making the column with the numbers text on import. Then you can modify that code to do it repeatedly. – Scott Craner Jul 12 at 16:50
  • How the user operates Excel is out of my control. Most users will just double click the CSV file and then it's too late... – OneWorld Jul 12 at 16:52
  • Then the answer from your linked question is the answer to this question. It is not possible. One needs to use the wizard where one can set the format of the column BEFORE opening the text file. – Scott Craner Jul 12 at 16:53
  • Prepend the numeric string with some character that won't show up to the end user. You could use the NBSP. Experimentation suggests that the tab (CHAR(9)) as well as multiple other characters in the <32 code range (but not all) will also work. Or you could save it as Unicode text and use the Unicode ZWSP character. – Ron Rosenfeld Jul 12 at 18:40
  • TAB is most promising! I now have to double check that it doesn't cause harm at any other point. – OneWorld Jul 12 at 19:30
1

Thanks for all the comments and answers! Credit goes to user Ron Rosenfeld whose recommendation led to this implementation:

enter image description here

0

The only further ideas I can come up with are:

  • Distribute Excel files instead of CSV

  • For your users, install your own viewer for CSV files. There are many such free viewers, or you may write your own as a script or a program that will correctly import the CSV to a temporary Excel spreadsheet.


(This does no longer work:)

The only way to avoid Excel removing the zeroes when importing numerical data, is not to have numerical fields.

You may achieve this by enclosing these fields with double-quotes so Excel will interpret them as text, like this : "000123".

You can do this, as you control the export.

  • OP says "I hoped "012345" would result in 012345, but even then Excel 2010 strips the leading zero". Maybe using a single quote ('012345) might work? – cybernetic.nomad Jul 12 at 17:10
  • @cybernetic.nomad that results in '012345 which isn't also good for further processing like printing labels for letters where the ZIP should be 012345 and not '012345 – OneWorld Jul 12 at 17:19
  • Some more ideas added. – harrymc Jul 12 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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