Export to csv/excel way to include leading zero [duplicate]

Question

Exporting select query data from mysql to csv & opening with excel, the leading zeros are gone.

Although the column is varchar, when the data gets exported, it writes like

4567 instead of 04567, is there a way to preserve the leading zero while exporting to csv?

Answer 1

If you are using SSRS, go the the textbox you need to have leading zeros, and over in the text box pane, scroll down to format and enter zeroes for as many numbers will be in the number. For example, in a zip code, you would enter 00000 into the format box. I know this works for Excel 2013.

Answer 2

Place the data in between single quotes when you export the csv.

i.e. 04567 will be '04567'

Answer 3

Your CSV file will have the leading zeroes. Just open it in a text editor and see.

The problem is that importing into Excel and Numbers (for Mac) will remove leading zeroes, and convert strings with numbers into number format. This is by intent, since a lot of input into excel comes from various sources, where numbers often are treated as text. And when you put numbers into excel you typically want to do something with them as numbers (ie. calculations) and not as text. So they have made it automatically handle the most common use case.

The only way to force excel to display numbers as text from the input, without the user having to do anything, is by outputting an excel file and specifying a "custom number format" in it. I recommend using a XLS export library for this, as it doesn't seem as simple as adding a line of config text into a CSV file.