Data & spreadsheets · CSV Cleaner
Why Excel Silently Corrupts CSV Fields: Leading Zeros, Dates and Long IDs
· Why it matters
csv data-cleaning spreadsheets
Opening a CSV in a spreadsheet is not neutral: postcodes lose zeros, codes become dates and long identifiers turn into scientific notation. This post explains why it happens, what is unrecoverable, and how to keep the raw file intact.
A file that was fine until someone opened it — the postcode 01234 becomes 1234, and the damage is saved back
A file leaves a source system with postcode 01234, is opened in a spreadsheet, and returns to the analyst with 1234. The original CSV was not malformed: it contained text with a leading zero. The difference arose when a program guessed that column was numeric and then saved its interpreted value. Once someone overwrites the source file, a later cleaning tool cannot know whether 1234 used to mean 01234 or 001234. Keep an untouched export before inspecting it in any spreadsheet.
Type guessing on open — how spreadsheets infer numbers, dates and formulas from text, and why CSV gives them no way to say otherwise
CSV expresses rows, separators and quoted text; it does not declare per-column number, date or postcode types. A spreadsheet opening it directly must make guesses. A value beginning with digits can become a number, 03-04 can be interpreted as a date according to locale, and a value beginning with = can be interpreted as a formula in some applications. The quoting of "01234" protects the CSV delimiter, not its future classification as text by an automatic import wizard.
The classic casualties — leading zeros, long numeric identifiers, anything that resembles a date, and values beginning with an equals sign
Leading zeros in postcodes and SKUs, identifiers with more significant digits than a spreadsheet number format can preserve, and ambiguous date-looking strings are classic casualties. Scientific notation is not itself corruption—it is a display choice—but saving a long ID after numeric conversion can lose exact digits. Formula-looking values from untrusted data are a separate security risk. ToolAcre’s CSV export has a formula-injection-safe option that prefixes risky starts, with the trade-off that intentionally calculated cells will no longer calculate.
Why the damage is often permanent — precision lost on save and dates re-serialised in a different format cannot be reversed from the output
Suppose a 20-digit customer reference is rounded after numeric interpretation and saved. The missing original digits cannot be guessed back from a new CSV. A date converted to an internal date and exported in another locale may have lost whether the source meant 3 April or March 4. This is why “I will clean it later” is not a safe first step. Preserve the raw bytes before any software with automatic typing has a chance to replace them.
Working on the raw text instead — keeping a copy no spreadsheet has touched, and cleaning it as text before any import
Work on a copy of the text export with a parser that regards cells as strings. ToolAcre’s CSV Cleaner parses delimiters and quoted fields in the browser and shows row warnings without silently dropping extra cells. Check whether the header still includes its original leading bytes and whether identifiers contain the exact source characters. Cleaning spaces or duplicates is separate from interpreting a field’s meaning; a postcode should remain a string even if every character happens to be a digit.
Worked example — a product export with SKUs and postcodes, showing what a spreadsheet round-trip changes and what a text-level clean preserves
Use an illustrative export with header sku;postcode;description and rows 00042;01234;"Red, small" and 00043;00105;"Blue, large". A naive comma import collapses the semicolon fields, while automatic numeric typing can turn 00042 and 01234 into 42 and 1234. CSV Cleaner’s semicolon parse yields three textual columns, including the quoted comma inside each description; exporting the cleaned copy still requires you to import columns as text into the destination spreadsheet. Compare the raw and round-tripped files before sending them to purchasing.
What this does not cover — restoring values already destroyed by a previous save, or configuring a spreadsheet's import wizard
This post cannot recover identifier digits or leading zeros already destroyed in a saved spreadsheet file. Nor does a text-level cleaner configure Excel’s import wizard, a colleague’s locale settings or a database schema. Once the raw file is safe, follow the spreadsheet vendor’s text-import instructions for code and ID columns. Test a small sample through the exact recipient workflow; “opens into columns” does not prove “preserved every string.”
Fix the file before the spreadsheet sees it — how the ToolAcre CSV Cleaner lets you repair delimiters, encodings and quoting before the export is ever opened in a spreadsheet
Fix the file before the spreadsheet sees it, then choose an import mode that keeps identifiers textual. CSV Cleaner can repair the delimiter, encoding and quoting of an unmodified export locally. It does not make a spreadsheet stop guessing field types on your behalf, so the untouched original and a post-import comparison remain the most important safeguards.