Quick Answer

Why it happens: Excel auto-formats columns as numeric when it detects digits, stripping any leading zeros — 00123 becomes 123, 01234 becomes 1234. This happens on open, not on save.

The fix: Format the target column as Text before pasting your data, or prefix values with an apostrophe to force text treatment. For bulk fixes across thousands of rows, run your file through Data Cleaner — it preserves every leading zero in seconds with zero upload.

---

CSV Leading Zeros Disappearing? Fix ZIP Codes and IDs Fast

If any of these look familiar, you have a leading zeros problem. When leading zeros disappear, identifiers change meaning entirely — ZIP code 01234 becomes 1234, employee ID 00042 becomes 42, and system lookups fail silently. It's one of the most common Tier 3 CSV failures — the import says "Success" while quietly corrupting your data. See CSV import error diagnostics if you're not sure which error type you're dealing with.

The fix takes under 5 minutes once you know where in the pipeline the zeros are disappearing. Most guides only address one point of failure. This one covers both.

For the complete reference on CSV import error types, see our CSV import errors complete guide.

---

Table of Contents

• Why Leading Zeros Disappear
• Diagnose: Where Are Your Zeros Being Stripped?
• Fix 1: Source CSV Has the Zeros, Excel Strips Them on Open
• Fix 2: The CSV Itself Is Missing the Zeros
• Platform-Specific Fixes
• How to Prevent Leading Zero Loss at the Source
• FAQ

---

Why Leading Zeros Disappear

CSV files store everything as plain text. The value 01234 in a CSV file is the string "01234" — five characters, first character is zero. The zeros are there.

The problem is what happens when software reads that string. If an application — Excel, Salesforce, MySQL, pandas — decides the value looks like a number, it converts it to an integer. "01234" becomes the integer 1234. The leading zero is gone because integers don't have leading zeros.

This is not a bug in the CSV file. It is a type inference decision made by the software reading it. The same CSV file, opened in a plain text editor, shows 01234 perfectly. Opened in Excel without configuration, it shows 1234.

Leading zero corruption is one of the most common silent CSV failures precisely because most import tools report success even when values change type. There is no error message — just wrong data in production.

That distinction matters for the fix: if the CSV itself has the correct values, you only need to change how your software reads it. If the CSV is already missing the zeros, you need to fix the source export first.

One common real-world scenario: ZIP codes exported from Excel look correct in the spreadsheet because Excel's cell formatting displays them with leading zeros — but the underlying value is stored as an integer. When saved to CSV, the integer writes without the zero. The raw CSV file already has the problem before any import tool touches it.

ZIP codes, employee IDs, and SKUs are identifiers — not numbers. Storing them in numeric fields will always strip leading zeros, permanently. The correct fix is changing the field type to text, not reformatting after the fact.

[Screenshot: Same CSV file side-by-side — left: opened in Notepad showing 01234, right: opened in Excel showing 1234 with no leading zero]

---

Diagnose: Where Are Your Zeros Being Stripped?

Open your CSV in a plain text editor — Notepad on Windows, TextEdit in plain-text mode on Mac. Do not use Excel for this check.

If the zeros are present in the text editor: The CSV is correct. The problem is downstream — Excel is stripping them when you open or edit the file, or your import platform is stripping them on ingestion. Go to Fix 1.

If the zeros are missing in the text editor: The problem is upstream — the system that exported or generated the CSV already dropped them. Go to Fix 2.

This single check determines everything. Don't skip it.

Quick-fix reference:

If your CSV already has zeros missing, the Data Cleaner can restore them instantly — detect the affected columns, set the target field length, and download the corrected file without any upload to a server.

---

Fix 1: Source CSV Has the Zeros, Excel Strips Them on Open

This is the most common scenario. Your CSV is fine — Excel is auto-converting text fields to numbers when it opens the file.

Step 1 — Do not double-click the file to open it. Double-clicking triggers Excel's auto-format, which strips leading zeros immediately.

Step 2 — Open Excel to a blank workbook. Go to Data → Get External Data → From Text/CSV (Excel 2016–2019) or Data → Get Data → From File → From Text/CSV (Excel 365).

Step 3 — Select your CSV file. Excel opens the Power Query preview or the legacy Text Import Wizard.

Step 4 — Set the affected column data type to Text. In Power Query, click the column header and change the type from "Whole Number" or "Any" to "Text." In the legacy wizard, select the column and choose "Text" in the Column data format section.

Step 5 — Load the data. Your leading zeros will be preserved throughout the session.

Important: This formatting is session-only in Excel. If you save as CSV and reopen, Excel will strip the zeros again on the next open. The fix must be reapplied each time — or you handle the data entirely outside Excel (see platform sections below).

Note: Excel sometimes applies scientific notation and strips leading zeros simultaneously on long numeric identifiers (e.g., 001234567890 may become 1.23457E+09). If you're seeing scientific notation in addition to missing zeros, the fix is the same — set the column to Text during import. See also our guide on stopping Excel's scientific notation conversion.

[Screenshot: Excel Power Query column type selector — ZIP code column being changed from "Whole Number" to "Text"]

---

Fix 2: The CSV Itself Is Missing the Zeros

If your text editor confirms the zeros are already gone from the CSV, the export process dropped them. Common sources:

• Excel saved the file after auto-converting the column to a number
• A database exported without quoting the field
• A CRM or ERP export formatted the field as an integer

Option A — Re-export from the source system with text formatting. In Excel: format the column as Text before saving as CSV. In databases: cast the column as CHAR or VARCHAR in your SELECT statement, not INT.

Option B — Restore zeros programmatically using a fixed-width pad. If you know the required length of the field (e.g., US ZIP codes are always 5 digits, employee IDs are always 6 digits), you can restore zeros by left-padding:

In Excel, use TEXT() formula:

=TEXT(A2,"00000")   ← for 5-digit ZIP codes
=TEXT(A2,"000000")  ← for 6-digit employee IDs

This converts 1234 back to 01234. Copy the formula column, paste as values, then save as CSV.

Option C — Restore using SplitForge Data Cleaner. Upload your CSV to the Data Cleaner, select the affected column, choose "Pad with leading zeros," and specify the target length. The corrected file downloads instantly — no formulas, no Excel, no upload to any server.

---

Platform-Specific Fixes

Salesforce

Salesforce fields typed as Text preserve leading zeros correctly. The issue occurs when the field is typed as Number — Salesforce stores it as an integer and the leading zero is gone permanently on write.

Fix: Change the Salesforce field type from Number to Text before import. In Setup → Object Manager → [Object] → Fields & Relationships → edit the field → change Data Type to Text. Re-import after the type change. Per Salesforce's field type documentation, Number fields store values as integers with no zero-padding support.

If you cannot change the field type (e.g., it's a standard field), use a formula field to display the zero-padded version, or use an external ID text field as your import key.

HubSpot

HubSpot contact and company properties store numeric-looking values as strings when the property type is set to "Single-line text." If your property type is set to "Number," HubSpot will strip leading zeros on import.

Fix: In Settings → Properties, edit the property and change the field type from Number to Single-line text. Re-import after the type change. For ZIP/postal code specifically, HubSpot's built-in zip property is already typed as text — use it instead of a custom number field.

MySQL

MySQL strips leading zeros when inserting into INT, BIGINT, or other numeric column types. The CSV value 01234 becomes 1234 in the database.

Fix: Use VARCHAR or CHAR for fields that require leading zero preservation:

ALTER TABLE contacts MODIFY zip_code VARCHAR(10);

For new tables, define the column as CHAR(5) (ZIP codes) or VARCHAR(n) from the start. When importing with LOAD DATA INFILE, the column type in the table definition controls how values are stored — per the MySQL LOAD DATA documentation, the CSV format itself doesn't determine storage type.

pandas

pandas infers column types on read_csv(). Any column that looks numeric gets cast to int64 by default, stripping leading zeros.

Fix: Specify dtype=str for affected columns:

# Single column
df = pd.read_csv('data.csv', dtype={'zip_code': str})

# Multiple columns
df = pd.read_csv('data.csv', dtype={'zip_code': str, 'employee_id': str, 'sku': str})

Per the pandas documentation, specifying dtype per-column is the correct approach. Using a global dtype=str works but converts all columns to strings, which may interfere with downstream numeric operations on other columns.

To restore zeros to a column that already lost them:

df['zip_code'] = df['zip_code'].astype(str).str.zfill(5)
# zfill(5) pads to 5 characters with leading zeros

---

How to Prevent Leading Zero Loss at the Source

The most reliable fix is prevention — ensuring zeros are preserved at the point of export, before downstream tools get a chance to strip them.

Rule 1 — Quote all fields that contain leading zeros in your CSV. Per RFC 4180, quoting a field signals to parsers that the value is a string. "01234" is treated as text by more parsers than 01234. Most export tools have an option to quote all fields or quote text fields.

Rule 2 — Never open a CSV in Excel if it contains leading-zero fields. Use a text editor for inspection. Use your import tool directly. Excel is the single most common cause of leading zero loss in CSV workflows.

Rule 3 — Define receiving column types before import, not after. Whether it's a database, CRM, or dataframe, setting the column type to string/text before loading data is always more reliable than reformatting after.

Rule 4 — Validate before import. The Data Cleaner detects columns where numeric-looking values have inconsistent lengths — a reliable signal that leading zeros were stripped from some rows but not others. Run it before every import on any file that contains IDs, codes, or reference numbers.

For systematic pre-import validation across all error types, see our CSV file validation guide. If your leading zeros issue is accompanied by text appearing in numeric fields, see the CSV data type mismatch fix guide. If invisible whitespace is also causing lookup failures on your ID fields, see the CSV whitespace fix guide. And if your column counts are shifting during the same export, see the CSV column count mismatch fix.

Note on Google Sheets: Sheets preserves leading zeros if the column is formatted as Plain Text before data entry (Format → Number → Plain text). Like Excel, changing the format after data is entered does not recover already-stripped zeros. Export to CSV from Sheets using File → Download → CSV — the Text-formatted values will export correctly.

Last reviewed: March 2026. Platforms tested: Excel 365, Salesforce (Spring '26), HubSpot, MySQL 8.0, pandas 2.x, Google Sheets.

---

FAQ