How to: Save leading zeroes in CSV
It's so common, you'd be hard-pressed to find someone who hasn't experienced it - opening your CSV in Excel (or another spreadsheet program) and realizing something is...missing.
If your data contains numbers with leading zeroes (i.e. zip code "06001"), those zeroes may get "lost" when you try to open your file in a spreadsheet program. We'll use Excel as our example throughout this guide, since it is so ubiquitous, after all.
Why do they go missing? Well, as you may have already learned in our CSV to Excel conversion article, Excel takes data from a CSV and translates it before it opens up the file for you to work on. This is because Excel doesn't work in plain text. It is a spreadsheet program, designed to be formatted into dates, digits, formulas, and more. When Excel sees numbers coming in from your CSV, it thinks it's doing you a solid and translating that into number formatting. This means leading zeroes are dropped automatically.
What this means, is if you have things like zip codes, account numbers, or other data that starts with a "0" - you can end up losing data, not to mention having a bit of a debacle if you don't catch it and fix it before it ends up somewhere else (importing into a database perhaps!).
All of this is easily avoidable, however. If you know your data contains leading zeroes, stop, before you go jumping into Excel > Open > your CSV. Instead, follow the Text Import Wizard that is built into Excel, in order to convert your CSV to Excel format step-by-step so that nothing gets lost in translation.
We've covered this in our conversion article, but we'll provide the steps again here, paying special attention to step 3 in the wizard where you choose the format of your columns.
If your data contains numbers with leading zeroes (i.e. zip code "06001"), those zeroes may get "lost" when you try to open your file in a spreadsheet program. We'll use Excel as our example throughout this guide, since it is so ubiquitous, after all.
Why do they go missing? Well, as you may have already learned in our CSV to Excel conversion article, Excel takes data from a CSV and translates it before it opens up the file for you to work on. This is because Excel doesn't work in plain text. It is a spreadsheet program, designed to be formatted into dates, digits, formulas, and more. When Excel sees numbers coming in from your CSV, it thinks it's doing you a solid and translating that into number formatting. This means leading zeroes are dropped automatically.
What this means, is if you have things like zip codes, account numbers, or other data that starts with a "0" - you can end up losing data, not to mention having a bit of a debacle if you don't catch it and fix it before it ends up somewhere else (importing into a database perhaps!).
All of this is easily avoidable, however. If you know your data contains leading zeroes, stop, before you go jumping into Excel > Open > your CSV. Instead, follow the Text Import Wizard that is built into Excel, in order to convert your CSV to Excel format step-by-step so that nothing gets lost in translation.
We've covered this in our conversion article, but we'll provide the steps again here, paying special attention to step 3 in the wizard where you choose the format of your columns.
Convert CSV to Excel via Text Import Wizard
1. Open the spreadsheet where you want to view your CSV data (i.e. a new or existing sheet)
2. Click on the "Data" tab and then select "From Text"
1. Open the spreadsheet where you want to view your CSV data (i.e. a new or existing sheet)
2. Click on the "Data" tab and then select "From Text"
3. Browse for your CSV file and then click "Import"
4. Select the file type "Delimited"
5. Start the import at row 1 (this should be the default selection here), and check the preview to make sure everything is looking gravy...
5. Start the import at row 1 (this should be the default selection here), and check the preview to make sure everything is looking gravy...
6. Choose your delimiter (most commonly this is comma, so if you're not sure, go ahead and choose this option. Again, you can check the preview to make sure you're looking good.)
7. Select the text qualifier. The most common is a double quote ("), so again, if you're not sure, go ahead and select this.
7. Select the text qualifier. The most common is a double quote ("), so again, if you're not sure, go ahead and select this.
8. Choose your data format. This is the critical step to ensure leading zeroes are kept intact. Select the column with the numbers and choose the type "Text" in this step. This tells Excel to keep hands-off, and not try to guess the format of your data.
9. Now confirm the location where you wish to import the data (i.e. new spreadsheet, or existing) and click OK!
As you've hopefully gleaned from this guide, it's not actually CSV that is the issue here: it is opening a CSV in Excel (or other spreadsheet program) which can cause leading zeroes to drop off.
Now, if your data originated in Excel, and you need to save it to CSV, you can ensure that your leading zeroes are not dropped by formatting your column(s) as text before you begin. Simply highlight your column(s) and go to the number format section to select "text" from the dropdown list:
Now, if your data originated in Excel, and you need to save it to CSV, you can ensure that your leading zeroes are not dropped by formatting your column(s) as text before you begin. Simply highlight your column(s) and go to the number format section to select "text" from the dropdown list:
This tells Excel that your data in this column should be stored as plain text. Now, when you go to save as CSV, your data is maintained exactly as-is (remember, CSV is plain text already!).
With this simple but powerful tool in your arsenal, you should never run into dropped zeroes again when working with CSV and Excel!
With this simple but powerful tool in your arsenal, you should never run into dropped zeroes again when working with CSV and Excel!