🔢 Data Integrity · 7 min read

How to Keep Leading Zeros in CSV Files: Excel, Sheets and Python (2026)

The zero is not lost in the CSV. It is lost when your spreadsheet opens the file and decides the column is a number. A CSV is plain text with no types attached, so 01234 is sitting there, intact, until Excel reads it and helpfully turns it into 1234. To keep it, open the file through Data > From Text/CSV and set that column to Text in the preview. In Google Sheets, format the column as Plain text before importing. In pandas, read with dtype=str. Every fix is the same idea: tell the tool the value is text before it decides otherwise.

Why the Zero Disappears in the First Place

It helps to know that the CSV is not the culprit. RFC 4180, the document that defines the format, describes it as a way of moving tabular data as plain text. Nothing in it records that a column is a number, a date, or a string. The reader decides, and most readers decide by looking at the values.

A column of digits starts with 0, looks like a number to Excel, and a number does not keep a leading zero. That is the whole mechanism. Excel is not deleting your data so much as interpreting it, and it is confident enough not to ask.

This is why the same file behaves differently in different tools. Some importers keep everything as text and the zeros survive by default. Excel optimises for arithmetic, which is the right guess most of the time and the wrong one exactly when the digits are an identifier rather than a quantity.

Which Values Actually Need the Zero

Only the ones that are labels wearing a numeric costume:

• ZIP codes. The 0xxxx range covers New England and part of the Northeast (Connecticut, Massachusetts, Maine, New Hampshire, New Jersey, New York, Puerto Rico, Rhode Island, Vermont), so a large share of US postcodes start with a zero.
• Employee and customer IDs. Any zero-padded sequence, from 0001 onward, is a label. Its length usually matters.
• Product codes. EAN-13 and UPC barcodes routinely begin with a zero, and the checksum depends on the full string.
• Phone numbers and account numbers. With the country code or a fixed prefix, the leading zero is often part of the dialled format.
• Sort keys. A column padded to a fixed width only sorts correctly if the padding survives, which is exactly why it was added.

If the value is a quantity, let it be a number. If it identifies a thing, it is text, and the tool needs to be told.

Method 1: Excel Import Wizard (the Right Way)

This is the only route that never guesses, so it is the one to use for anything that matters.

1. Open Excel on a blank workbook. Do not double-click the CSV.
2. Go to Data > From Text/CSV and pick the file.
3. In the preview, find the column with the ZIP codes or IDs.
4. Set its data type to Text. Leave the other columns alone if they really are numbers.
5. Click Load.

Once loaded this way, the column is text for good, and a re-save to CSV writes the zeros back out as characters. Double-clicking the same file skips every one of those steps and applies the type guess with no chance to intervene, which is the root of the whole problem.

Method 2: Getting It Back After Excel Already Stripped It

If the file is already open and the zeros are gone, the digits are no longer in the sheet, so you cannot recover them from there. Two options remain.

Re-import through the wizard above, which is the correct fix. Or, if you still have the original column somewhere, rebuild it with a formula that pads to a known width: =TEXT(A2,"00000") turns 1234 into 01234, and =TEXT(A2,REPT("0",5)) does the same when you want the width in one place. This works only when you know how long the value should be, and only when Excel has not also dropped digits off the end.

Method 3: Google Sheets

Sheets has the same instinct but lets you turn it off in two places.

1. Before importing: in File > Import, expand the advanced options and untick Convert text to numbers, dates, and formulas.
2. For anything you paste later: select the column, then Format > Number > Plain text.

The second one is worth doing even if you use the first. Formatting the column as Plain text applies to future pastes into that column, so the zeros stop disappearing one paste at a time.

Method 4: pandas, and Reading With the Right Type

import pandas as pd

# Simple version: read every column as text
df = pd.read_csv("customers.csv", dtype=str)

# Better: only the columns that need it, so your maths still works
df = pd.read_csv(
    "customers.csv",
    dtype={"postcode": str, "customer_id": str},
)

print(df["postcode"].head())

The timing is what trips people up. pandas strips the zero as it parses, so converting the column to text after read_csv is too late, the digits are already gone. It has to be in the read call, per column, which is what the dtype argument is for.

Watch the blanks while you are there. A column of text and a column with a genuine missing value do not mix well: if a postcode cell is empty, pandas reads it as NaN even with dtype=str, unless you pass keep_default_na=False. That keeps blanks as empty strings rather than the float NaN.

Method 5: Fix the Source, Not the Import

The most durable fix is to stop the problem upstream. If you generate the CSV yourself, format the identifier column as text at the point it is written, and quote it. A CSV field of "01234" is unambiguous text, and most importers respect the quotes even when they are guessing types.

This matters for handoffs. An ID that survives your tool but loses its zero in the next person's Excel is still a broken ID, and the failure is silent. Padding the column, quoting it, and documenting the expected width costs ten seconds and saves a support ticket.

Which Method Keeps What

The 15-Digit Problem Behind All of This

Leading zeros are only half the story. Excel stores numbers with a limit of 15 significant digits, so a 16-digit account or card number has its tail replaced, often with zeros, and no amount of cell formatting brings it back. The value is wrong from the moment the file opens, and it looks plausible enough that nobody notices until a payment fails.

The same reflex solves both: if the value is an identifier, it is text, and it should be set to Text before it is imported. That one decision fixes the stripped leading zero and the truncated tail together.

For a wider look at why a plain text grid is often safer than a spreadsheet for identifiers, the CSV vs Excel comparison covers the trade-offs. If the corruption is already in the file rather than the import, the dirty data cleaning guide walks through the usual suspects, and the duplicate removal guide handles the pass after that. To check what a file really contains before a spreadsheet touches it, the free CSV analyzer shows the columns and types without altering a single value.

Frequently Asked Questions

Why does Excel remove leading zeros from a CSV?

Excel reads the column and guesses its type. A column of digits looks like a number, and a number has no leading zeros, so 01234 becomes 1234. The CSV still contains 01234 as text; the zero is lost when Excel converts it on import, not when the file was written.

How do I open a CSV in Excel without losing leading zeros?

Do not double-click the file. Use Data > From Text/CSV, then in the import preview set the column containing IDs or ZIP codes to Text and click Load. Excel keeps the column as text and the zeros survive.

Does Google Sheets drop leading zeros too?

It will if you let it convert. In File > Import, untick Convert text to numbers and dates, or format the target column as Plain text before pasting. The column format is the more reliable of the two, because it applies to future pastes as well.

Is a leading apostrophe a real fix?

It is a workaround with a cost. An apostrophe before 01234 makes Excel treat the cell as text and the apostrophe is not stored in the value, but it will be there if you export the column back out, and it can break a downstream formula. Prefer the import wizard; use the apostrophe only for a one-off cell.

How do I keep leading zeros in pandas?

Read the file with dtype=str, or name the specific columns with a dtype dictionary. Do it at read time. Converting the column back to string afterwards is too late, because pandas has already stripped the zero during parsing.

My ID has more than 15 digits and Excel changes the last ones. Why?

Excel keeps only 15 significant digits, so a 16-digit card or account number has its trailing digits replaced, often with zeros. This is separate from leading zeros and no number format fixes it. Set the column to Text on import, or the value has already changed by the time you see it.

Does saving as CSV keep the leading zeros?

Yes, if the cell is stored as text. A CSV is plain text with no type information at all, so whatever characters are in the cell are what gets written. The loss happens on the way in, when a reader decides the column is numeric.