Why ZIP Codes Lose Their Leading Zeros in Excel, CSV and JSON (and How to Fix It)
Published November 15, 2026 by the USA Address Generator editorial team · Data Quality · 8 min read
02134 turns into 2134 because something decided a ZIP code was a number. Where that happens in spreadsheets, CSV imports, JSON, databases and code, and how to prevent and repair it.
A ZIP code is a five-character label that happens to be made of digits. The moment any tool in a pipeline treats it as a number, every code that starts with zero is damaged: 02134 becomes 2134 and 00926 becomes 926. The data still looks plausible, nothing raises an error, and the problem is usually found when mail is returned. This article covers where the conversion happens and how to stop it.
Which ZIP codes are affected
Every ZIP code beginning with 0. That is all of Connecticut, Maine, Massachusetts, New Hampshire, New Jersey, Rhode Island and Vermont; Puerto Rico and the US Virgin Islands, whose codes begin with 00; military addresses in the AE region, which begin with 09; and a small number of codes in New York, such as 06390 for Fishers Island and 00501, used by the IRS in Holtsville.
That is a large population. A dataset that "works fine" in testing with addresses from California and Texas can fail for an entire region the first time it meets real customers.
Where the zero gets lost
Opening a CSV in Excel
This is the most common cause by a wide margin. When Excel opens a CSV by double-click, it guesses the type of each cell, decides 02134 is the number 2134 and stores it that way. The zero is not hidden; it is gone. Saving the file writes 2134 back to disk.
Two things that do not help:
- Quoting the value in the CSV.
"02134"is still converted. Quotes in CSV mark the boundaries of a field; they do not declare a type. - Applying the ZIP Code number format afterwards. Excel's special format displays the number with five digits, but the cell still contains 2134. Export it and the zero is missing again.
What does work:
- Import instead of opening. Use Data, then From Text/CSV, and set the ZIP column's type to Text before loading. In the older Text Import Wizard, choose Text for that column in the last step.
- Format the column as Text before typing or pasting. Values entered afterwards are kept as written.
- Turn off automatic conversion. Recent versions of Excel have an option under File, Options, Data to stop removing leading zeros when loading text files.
The same applies to other spreadsheet programs. Google Sheets has an import option to not convert text to numbers; LibreOffice shows an import dialog where the column type can be set to Text.
Type inference in code
Libraries that read CSV files infer column types too.
import pandas as pd
# Wrong: postal_code is inferred as int64 and the zeros are gone
df = pd.read_csv("customers.csv")
# Right: declare it as a string
df = pd.read_csv("customers.csv", dtype={"postal_code": "string"})
The same trap exists in spreadsheet-reading libraries, in database import tools that sample the first rows to guess a schema, and in "schema on read" analytics engines. If the first thousand rows are from the west coast, the tool sees only five-digit numbers with no leading zeros and has no reason to doubt that the column is numeric.
JSON without quotes
JSON has a number type, and numbers cannot have leading zeros. {"zip": 02134} is not valid JSON; a serialiser handed the integer 2134 writes {"zip": 2134}. The value must be a string:
{ "zip": "02134" }
If you define an API, declare the field as a string in the schema, and say so in the documentation. If you consume one that sends numbers, you have to repair it on the way in, as described below.
Numeric database columns
A column declared INTEGER or NUMERIC cannot hold a leading zero. The fix is to make the column text, which is covered in the guide to storing addresses in a database. Changing the type alone does not restore zeros that have already been dropped.
Loosely typed code
In JavaScript, Number("02134") is 2134, and so is parseInt("02134", 10). Any "clean up the input" helper that coerces to a number and back destroys the value. HTML has the same trap: is the wrong control for a ZIP code. Use a text input with a numeric keyboard hint:
<input type="text" inputmode="numeric" autocomplete="postal-code"
pattern="[0-9]{5}(-[0-9]{4})?" maxlength="10">
Repairing damaged data
If a five-digit US ZIP has been stored as a number, the repair is to pad it back to five characters with zeros on the left.
-- PostgreSQL: after changing the column to text
UPDATE customers
SET postal_code = lpad(postal_code, 5, '0')
WHERE country = 'US' AND postal_code ~ '^[0-9]{3,4}$';
df["postal_code"] = df["postal_code"].astype("string").str.zfill(5)
const zip = String(value).padStart(5, '0');
In Excel, a helper column with =TEXT(A2,"00000") produces a text value with the zeros restored.
Three cautions before running a repair across a whole table:
- Only repair US rows. Padding a foreign postal code makes it wrong.
- Handle ZIP+4 separately. A nine-digit ZIP stored as a number, such as 21341234, should become
02134-1234. Pad to nine, then insert the hyphen. If your data mixes five-digit and nine-digit values, decide by length: three to five digits is a ZIP, seven to nine is a ZIP+4. - Check for other damage first. If the file passed through a spreadsheet, long numeric identifiers may have been converted to scientific notation and dates may have been reformatted. Padding ZIP codes will not fix those, and they suggest the file should be re-exported from the source instead.
Preventing it
- Treat ZIP as a string at every layer: form control, API schema, application type, database column, export.
- Validate with a pattern,
^[0-9]{5}(-[0-9]{4})?$, not with a numeric range. - When you publish a CSV for people to open in a spreadsheet, say in the documentation how to import it, or offer an
.xlsxexport in which the column is already typed as text. - Put leading-zero ZIP codes in your test data on purpose.
That last point is the cheapest protection there is. A fixture set drawn from one region hides the bug; a set that includes Massachusetts, New Jersey and Puerto Rico finds it in the first round trip. The ZIP prefix lookup can generate codes inside any prefix, including 006 to 009 and 010 to 089, and the address generator can be filtered to a single state when you want a whole batch of them.