Import Builder/Guides/Account Numbers & Leading Zeros
Data Formatting

Leading Zeros Disappearing from Account IDs?

Customer IDs, account numbers, and SKUs lose their leading zeros when imported. Learn why Excel strips them and how to preserve them in your CSV imports.

Why leading zeros matter

Leading zeros are critical for business data. They keep IDs and codes consistent, sortable, and recognizable. But they disappear when data is treated as numbers instead of text.

Customer IDs become unrecognizable

Customer ID 00847 becomes 847. Your team can't find them in the system.

Account numbers don't match

Account 0095 becomes 95. It won't match your accounting software.

SKU lookups fail

Product SKU 00412 becomes 412. Inventory system can't find it.

Postal codes and area codes break

ZIP code 00501 becomes 501. Shipping software can't route the order.

Fixed-length codes change length

00847 and 01045 become 847 and 1045, so codes that should all be five digits fail length checks and no longer line up.

Data becomes unmappable

00847 in CSV ≠ 847 in your database. No way to match them.

Real example:

What your data should be:

Customer ID | Name           | Account
------------|----------------|-------
00847       | Smith Corp     | 0095
00923       | Jones Inc      | 0102
01045       | Brown Ltd      | 0156

What Excel does (WRONG):

Customer ID | Name           | Account
------------|----------------|-------
847         | Smith Corp     | 95
923         | Jones Inc      | 102
1045        | Brown Ltd      | 156

❌ Leading zeros stripped—IDs no longer match your system

Root cause: Excel treats numbers as numbers, not text. It automatically strips leading zeros because "00847" and "847" are mathematically identical. But for business data, they're different.

Common data types that lose leading zeros

Customer/Account IDs

Examples:

00847, 00923, 01045

Impact when lost:

Can't find customers, duplicate entries created

Product SKUs

Examples:

00412, 00413, 01020

Impact when lost:

Inventory lookups fail, wrong products shipped

ZIP/Postal Codes

Examples:

00501 (NY), 00602 (PR), 01001 (MA)

Impact when lost:

Addresses don't geocode, shipping routes fail

Phone numbers

Examples:

07700 900123 (UK mobile), 0412 345 678 (Australia)

Impact when lost:

The leading 0 goes, so numbers don't dial or validate

Invoice/Reference Numbers

Examples:

00001, 00002, 00100

Impact when lost:

Can't match invoices to accounting software

Employee IDs

Examples:

00001, 00042, 00156

Impact when lost:

Other systems can't find employees

Lab/Report Numbers

Examples:

00001-LAB, 00002-TEST

Impact when lost:

Medical/scientific results become unsearchable

Why Excel strips leading zeros

Excel automatically interprets data as numbers when possible. Mathematically, 00847 = 847, so once Excel reads it as a number, the zeros have nowhere to go.

The Problem: Number Format vs Text Format

When you type: | Excel sees as: | Result: 00847 | number 847 | Displays as 847 ❌ '00847 (apostrophe first) | text "00847" | Displays as 00847 ✓

Excel is designed for math, not business IDs. So it assumes any numeric-looking value should be treated as a number. The apostrophe tells it to keep the value as text; it isn't shown in the cell or saved in the CSV. Typing quotes doesn't work: they become part of the value.

The Cascade: Spreadsheet → CSV → Import

  1. You paste customer ID 00847 into Excel
  2. Excel strips the leading zeros → 847
  3. You save as CSV
  4. CSV contains: 847 (no leading zeros)
  5. You import into your system
  6. System receives: 847 (can't match 00847)

Once the CSV is saved, the zeros are gone from the file. For codes that are always the same length you can add them back (step 6); otherwise you need the original data.

How to fix and prevent leading zero loss

Step 1: Don't open the file in Excel unless you need to

Best approach: If you're only moving a CSV from one system to another, upload the file as it is. Double-clicking it in Excel is what strips the zeros.

To check it: open the CSV in a text editor instead (see step 5).

Step 2: In Excel, set the column to Text first

Typing or pasting:

  1. Select the column with leading zeros
  2. Right-click → Format Cells
  3. Choose "Text" from the list
  4. THEN type or paste your data

Opening a CSV: use Data → From Text/CSV, click Transform Data, set the ID columns to Text, then Close & Load.

⚠️ Important: Format the column BEFORE pasting. If you paste first, then format, it's too late—the zeros are already gone.

Step 3: Switch off zero-stripping (Excel for Microsoft 365)

Recent versions of Excel let you turn the conversion off: go to File → Options → Data, and under Automatic data conversion untick "Remove leading zeros and convert to number format". The setting is per computer, so anyone else opening the file still needs steps 1 or 2.

Step 4: Work in .xlsx, save the CSV last

A workbook remembers which columns are Text. A CSV doesn't, so Excel guesses again every time it's opened. Keep your working copy as .xlsx and use File → Save As → CSV UTF-8 only when you're ready to import.

Step 5: Verify in a text editor

Before importing, open the CSV in a text editor (VS Code, Notepad++) to verify leading zeros are there:

Customer ID, Name 00847, Smith Corp 00923, Jones Inc 01045, Brown Ltd

✓ If you see 00847, 00923, 01045 → Good, leading zeros are preserved
❌ If you see 847, 923, 1045 → Bad, they're already lost

Step 6: If leading zeros are already lost

If every code should be the same length, you can restore them:

  1. Open the CSV in Excel
  2. Add a helper column with formula: =TEXT(A1,"00000")
  3. This adds back the leading zeros (adjust 00000 for your ID length)
  4. Copy the helper column, Paste Special → Values
  5. Replace the original column
  6. Delete the helper column
  7. Save as CSV

This only works for fixed-length codes. If they vary (847 could have been 0847 or 00847), go back to the original data.

Best practices for preserving leading zeros

Always format columns as Text BEFORE pasting data
Never open raw ID data in Excel—export CSV directly from source
If you must use Excel, verify leading zeros before saving
Keep a working copy as .xlsx with ID columns as Text, and save the CSV last
Open the CSV in a text editor to verify leading zeros are present
Communicate with data providers: "Send IDs as text, with leading zeros preserved"
Use alphanumeric IDs if possible (e.g., "C00847" instead of "00847")
In your import system, treat ID fields as text, not numbers
For ZIP codes specifically: import as text, never as numbers
Test with sample data that includes leading zeros before bulk import
If importing legacy data, run through a validation step to check for missing zeros
Document your ID format (e.g., "5-digit customer ID with leading zeros")
When exporting from your system, always include leading zeros
For postal codes: verify the import system expects text format, not numbers

Related CSV topics:

Prevent ID corruption at the source

Collect data through forms instead of spreadsheets and IDs are never corrupted. No Excel stripping zeros, no manual formatting—data stays clean from collection to export.

Learn more about Import Builder