Why do CSV files lose leading zeros?
Understand why programs strip the zeros from codes, and learn to read ZIP codes and IDs as text in pandas, DuckDB and Excel.
Free course. Your Maestro, an AI, .
Accounting sends you clientes.csv so you can split the orders by delivery zone. You open it in Excel with a double click and the first customer's postal code (codigo_postal) says 6700. The branch code (clave_sucursal) says 1. Did they send you a bad file?
Open it as text, like in module 1, and the doubt goes away:
cliente,codigo_postal,clave_sucursal,ventas
Abarrotes Doña Peña,06700,001,15230.50
Papelería La Única,44100,014,8920.00
Farmacia San José,45640,003,22105.75The 06700 is right there, complete. A CSV is text, and text keeps every character, zeros included. What gets lost isn't lost in the file: it's lost when it's read, when the program decides that column is numbers. And a number has no leading zeros: 06700 and 6700 are the same quantity.
Microsoft says it plainly in its Excel help: "Excel automatically removes leading zeros, and converts large numbers to scientific notation, like 1.23E+15". Excel assumes that if something looks like a number, you'll want to do math with it.
But a ZIP code isn't a quantity. You will never add two ZIP codes or average a set of branch codes. They're labels that happen to use only digits. The same goes for a customer number, a phone number or a SKU like 00731.
The rule you'll use for the rest of the course: if you would never add it up, read it as text.
pandas falls into the same trap as Excel. Predict what happens with the accounting file:
pandas with no instructions
Example 1 of 4
import pandas as pd
with open("clientes.csv", "w", encoding="utf-8") as archivo:
archivo.write("cliente,codigo_postal,clave_sucursal,ventas\nAbarrotes Doña Peña,06700,001,15230.50\nPapelería La Única,44100,014,8920.00\nFarmacia San José,45640,003,22105.75\n")
clientes = pd.read_csv("clientes.csv")
print(clientes.dtypes)
print(clientes.loc[0, "codigo_postal"], clientes.loc[0, "clave_sucursal"])
A guess is fine: it's kept as you wrote it, so you can compare. Only you and your Maestro see your predictions.
Example 2 of 4, opens once you explain this one
The key point of the four runs: the file was the same. What changed was what each reader assumed. pandas assumed numbers; DuckDB, in this case, assumed text. Neither one warned you.
That's why you shouldn't depend on anyone's guess. When a column is a code, tell the reader:
- In pandas:
dtype={"codigo_postal": str, "clave_sucursal": str}. - In DuckDB:
read_csv('clientes.csv', types={'codigo_postal': 'VARCHAR', 'clave_sucursal': 'VARCHAR'}).
Both do the same thing: they read those columns as text and let the rest be guessed. Ask your Maestro how to decide which columns in your files are codes.
In Excel
In recent versions of Excel you can turn that conversion off. Microsoft documents an option called Automatic Data Conversion, which applies to Excel for Microsoft 365 and Excel 2024, on Windows and Mac. Where it lives depends on your system:
- On the File tab, choose Options.
- Select the Data tab and go to Automatic Data Conversion.
- Disable Remove leading zeros and convert to number.
From then on, 00123 stays 00123, stored as text.
- Select Excel and choose Preferences.
- Select the Edit tab and go to Automatic Data Conversion.
- Disable Remove leading zeros and convert to number.
From then on, 00123 stays 00123, stored as text.
Two notes from the same Microsoft help page. First, that option doesn't directly affect data you bring in with Power Query, which has its own way of setting each column's type; you'll see it in module 4. Second, a number stored as text may not work in math operations. For a ZIP code that doesn't matter: you'll never add it up.
If your Excel doesn't have that option or you'd rather not change it, in module 4 you learn to import the file and say, column by column, which ones are text.
Common mistake: Formatting afterward doesn't bring the zeros back
A common way out is to apply a custom format like 00000 to the column. Microsoft warns that "This will not restore leading zeros that were removed prior to formatting." And if you save the CSV from Excel after it removed the zeros, the new file no longer has them. If you need the original, ask for it again or keep an untouched copy.
Try it
This program gives each customer a delivery zone and a branch by looking up the postal code and the branch code in two dictionaries. Run it: every zone comes out NaN, which in pandas means "I found nothing."
Notice one detail before you fix it: it can't find the zone for 44100 either, and that one has no zeros at all. The problem isn't only the lost zero. The number 44100 and the text "44100" aren't the same thing to Python, and the dictionary keys are text. If you don't see why 44100 fails too, ask your Maestro.
Run the program: zone and branch come out NaN. Change only the pd.readcsv line so codigopostal and clavesucursal are read as text. You're done when each customer has a zone and a branch, and total sales are still 46256.25.
Run the program: zone and branch come out NaN. Change only the pd.read_csv line so codigo_postal and clave_sucursal are read as text. You're done when each customer has a zone and a branch, and total sales are still 46256.25.
Run your code to see what it prints and the value of its last expression. Every run starts fresh. Ctrl+Enter runs it. Tab indents; to leave the editor, press Escape, then Tab.
Ready to run your code.
Run your code to see its output here.