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.