3

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.

Improve this question
10
  • 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
    Commented Jul 12, 2019 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
    Commented Jul 12, 2019 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
    Commented Jul 12, 2019 at 16:53
  • 1
    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
    Commented Jul 12, 2019 at 18:40
  • TAB is most promising! I now have to double check that it doesn't cause harm at any other point.
    – OneWorld
    Commented Jul 12, 2019 at 19:30

4 Answers 4

Reset to default
3

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

enter image description here

Improve this answer
3

This works, and the number is still treated as a number when referenced by formulas:

Example number to display in CSV is 00141.
CSV file line:

field1,"=""00141""",field3
Improve this answer
1
  • Interesting, but looks even less suitable for further processing in programs other then Excel. My feature is in production now with the TAB-Version. Won't change it now
    – OneWorld
    Commented Jul 5, 2021 at 15:11
1

Try:

="0123"

So:

{equal}{quote}{numbers}{quote}

BTW: no colon, no blanks

Improve this answer
1
  • 1
    Formatting your post correctly makes it easier to read and understand
    – Greenonline
    Commented Jul 12, 2022 at 18:28
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.

Improve this answer
3
  • 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
    Commented Jul 12, 2019 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
    Commented Jul 12, 2019 at 17:19
  • Some more ideas added.
    – harrymc
    Commented Jul 12, 2019 at 18:22

You must log in to answer this question.

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