Clean a CSV Without Losing Leading Zeros

Open a CSV in a spreadsheet and ZIP code 02134 becomes 2134, and a 19-digit account number turns into 1.23E+18. This example cleans a fictional customer file while keeping every digit.

ℹ️ A worked example you can check. The before and after tables below were produced by the free tool on this site from a fictional sample file. Open the same file yourself and you will get the same result.

What Usually Goes Wrong

• ZIP codes that start with zero
• Customer IDs padded with zeros
• Account numbers longer than 15 digits
• Phone numbers that lose their + sign
• The usual stray spaces and repeated rows on top

The Sample File, Before

7 rows of fictional data with the problems above. Every person and company is invented.

Open This File in the Workspace Download the CSV

Clean a CSV Without Losing Leading Zeros sample before cleaning
Customer ID	Name	ZIP	Phone	Account Number
000481	Halima Yusuf	02134	(617) 555-0181	00012345678901234567
000482	Peter Vandermeer	00501	631.555.0182	1234567890123456789
000483	Ingrid Solberg	07030	+1 201 555 0183	00098765432109876543
000482	Peter Vandermeer	00501	631.555.0182	1234567890123456789
000484	Mateo Ferrer	96701	808-555-0184	4000000000000000002

000485	Chiara Benedetti	06510	(203) 555 0185	00000000000000000017

How to Clean It

1. Do not open and re-save the CSV in a spreadsheet first. Load the original file straight into the workspace.
2. Run it with duplicates and phones switched on.
3. On the Review step, check that the ZIP, Customer ID and Account Number columns show no highlighted cells.
4. Export as CSV for a CRM import. If people will open the result in Excel, use the Excel export: every cell is stored as text, so the zeros, long numbers and + signs are still there when the file opens.
5. If the file was already damaged by a spreadsheet, go back to the system it came from and export again. Lost digits cannot be recovered from the damaged copy.

Prefer the command line or an AI agent? The same result comes from smartdata-clean clean leading-zeros.csv --dedupe-rows --phones. See tools for agents.

The Result

7 rows in, 2 removed, 5 out. The repeat and the blank row are removed, the padded name is trimmed and phones are standardized. Every ZIP, ID and account number is identical to the original, character for character — the verifier compares all of them.

Clean a CSV Without Losing Leading Zeros sample after cleaning
Customer ID	Name	ZIP	Phone	Account Number
000481	Halima Yusuf	02134	+16175550181	00012345678901234567
000482	Peter Vandermeer	00501	+16315550182	1234567890123456789
000483	Ingrid Solberg	07030	+12015550183	00098765432109876543
000484	Mateo Ferrer	96701	+18085550184	4000000000000000002
000485	Chiara Benedetti	06510	+12035550185	00000000000000000017

What the tool left for you

Nothing was listed for review on this file.

Checked, not just claimed

The original file, the cleaned file and the change log were run through the separate verifier. Result: PASS.

✓ Removed rows with a valid, verified reason: 2 of 2
✓ Rows: 7 in, 2 removed with a logged reason, 5 out
✓ Cells compared: 25; changed: 6; every change matched to the log: yes
✓ Log entries: 9; not matching the files: 0

The verifier confirms that the change log explains every difference between the two files. It does not judge whether each cleaning choice suits your situation; that part is yours.

The cleaning report

Report text for the sample file

SMARTDATAAI — CLEANING REPORT
Source: leading-zeros.csv (fictional practice data)

SUMMARY
Rows in (excluding header): 7
Rows out: 5
Rows removed: 2
  Empty rows: 1
  Exact duplicates: 1
Cells changed: 7
  Leading/trailing spaces trimmed: 1
  Phones standardized to +E.164: 6

COLUMNS DETECTED
Email: none
Phone: Phone
Date: none
Name: Name

SAMPLE OF CHANGES (first 7 of 7)
Row 2 · Phone: "(617) 555-0181" → "+16175550181"  [phone]
Row 3 · Phone: "631.555.0182" → "+16315550182"  [phone]
Row 4 · Name: "  Ingrid Solberg " → "Ingrid Solberg"  [whitespace]
Row 4 · Phone: "+1 201 555 0183" → "+12015550183"  [phone]
Row 5 · Phone: "631.555.0182" → "+16315550182"  [phone]
Row 6 · Phone: "808-555-0184" → "+18085550184"  [phone]
Row 8 · Phone: "(203) 555 0185" → "+12035550185"  [phone]

Values the tool cannot settle are left as they were and listed above. Where the day/month order of a date was worked out from other dates in the same column, that is listed above too.
Row numbers refer to the original file (header = row 1). Email checks cover format only; they do not confirm that a mailbox exists.

Where a Tool Stops and a Person Starts

If the only copy you have has already lost its zeros, a person can sometimes rebuild them from a known length or a second source, and will tell you which values could not be recovered. Ask for a quote under Complex Cleanup.

See Service Levels From $19 How We Check Our Work

When the tool reads an Excel file, it warns you if it finds numbers longer than 15 digits, because Excel may already have rounded them before the file was saved.