Why Your CSV Import Silently Mangles IDs and Dates

A CSV file carries no type information and no encoding declaration, so every program that opens one has to guess what the characters mean. Those guesses happen independently at every hop in the pipeline, and they are destructive: a ZIP code of 02138 becomes 2138, a 16-digit order number loses its last digit to a zero, and a part number of 3/4 becomes March 4. The fix is not to format the column as text afterward, because by then the original characters are gone. The fix is to stop every program in the chain from having to guess.

Why every program reading a CSV has to guess

There is no CSV standard in the sense people assume. RFC 4180 is the closest thing to one, and it opens by admitting the problem: "there is no formal specification in existence, which allows for a wide variety of interpretations of CSV files." It closes by warning implementors that "due to lack of a single specification, there are considerable differences among implementations."

Two specific things are missing from the file, and both of them matter.

The first is types. Everything in a CSV is characters between delimiters. The sequence 02138 could be a five-character postal code or the number two thousand one hundred thirty-eight, and the file does not say which. Every reader applies its own inference rules to decide.

The second is encoding. RFC 4180 notes that common usage is US-ASCII and that other character sets may be used "in conjunction with the charset parameter." That parameter belongs to the MIME type, which travels in an HTTP header. A file sitting in a folder or attached to an email has no MIME type parameters, so the information is simply absent. Python's standard library documentation states the consequence plainly: without external information it is impossible to reliably determine which encoding was used.

Two undeclared properties, two independent guesses, repeated at every hop. That is the whole problem.

What actually gets rewritten

Here is what a type guesser does to values that appear in ordinary business data.

Value in the source system	What a type guesser makes of it	Does anything error?
02138 (ZIP, account number, SKU)	2138	No
1234567890123456 (16-digit order ID)	1234567890123450	No
3/4 (size, fraction, part number)	March 4, stored as a date serial	No
1E5 (lot code, part number)	100000	No
MARCH1 (any letter-plus-digit code)	1-Mar	No
00:30 (a duration in a timesheet)	A clock time	No
José in UTF-8 with no byte order mark, opened on Windows	José	No
id as the first header, file has a byte order mark	A column name with three invisible bytes in front	Yes, as a confusing mapping failure

The 16-digit case is not a display issue, which is the usual assumption. Microsoft documents a hard maximum precision of 15 significant digits, and states that for any number containing 16 or more digits, any digits past the 15th are rounded down to zero. Your order number does not look wrong. It ends in a zero it never had.

Notice the last column. Everything except the byte order mark row fails silently. 2138 is a plausible number, 100000 is a plausible quantity, March 4 is a plausible date. No downstream validation has any reason to object, which is why this surfaces weeks later, when a mailing goes to the wrong town or a payment reconciles against the wrong invoice.

If you think this is a training problem, genomics already ran the experiment at scale. A 2021 study in PLOS Computational Biology screened 11,117 genomics papers published between 2014 and 2020 across 741 journals that shipped supplementary gene lists, and found spreadsheet-mangled gene names in 30.9 percent of them. The figure it compares against, reported in 2016, was 19.6 percent. Five years of published warnings and reviewer attention, and the error rate went up.

The eventual response was not better training. The HUGO Gene Nomenclature Committee renamed the genes: SEPT1 became SEPTIN1, MARCH1 became MARCHF1. A field of careful people with a strong professional interest in data integrity decided that renaming human genes was more reliable than getting everyone to stop a spreadsheet from converting them. Your operations team, moving faster and caring less about a SKU column than a geneticist cares about a gene, will not win that fight with a note in a runbook.

The five hops, and only one of them gets blamed

This is the part that matters for automation, and it is the part every "keep your leading zeros" tutorial skips. The conversion does not happen once, in Excel. A typical file-based integration has five places where something makes an independent guess.

Hop	What decides	How to pin it
1. Export from the source system	Quoting, delimiter, and whether a byte order mark is written	Choose the export options instead of accepting the default
2. A person opens the file to check it, then saves	Every column, re-inferred on open	Do not open it. To inspect, use Data then From Text/CSV, or a plain text editor
3. Import into a spreadsheet or upload to a drive	The importer's own inference	Import into a sheet whose identifier columns are already formatted as text
4. The workflow's CSV parse step	Header row, delimiter, encoding, and per-column type	Set the encoding explicitly and read identifier columns as strings
5. The write into the destination system	The destination's parse of what you handed it	Use the destination's raw or unparsed input mode

Hop 5 is the one that catches people who did everything else right, so it deserves the detail.

The Google Sheets API offers two input modes. RAW is documented as "the values the user has entered will not be parsed and will be stored as-is." USER_ENTERED is documented as "the values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc." Most connectors default to USER_ENTERED, because that is what makes formulas and dates behave for a human looking at the sheet.

So a workflow can read 02138 correctly from a clean file, hold it correctly in memory, hand it to the write step as a string, and still land 2138 in the destination. No spreadsheet was opened, no person was involved, and the write step re-parsed a value that was already correct. If a spreadsheet is the backing store for your workflow, that compounds with everything else that makes Sheets fragile as an automation backend.

Everyone blames hop 2, because it is the one with a person in it. Hops 3, 4, and 5 do the same damage with nobody watching.

The fixes you have been given, and the three gaps in them

Excel ships controls for this now, under File, Options, Data, Automatic Data Conversion on Windows and Excel, Preferences, Edit on Mac. Four switches: remove leading zeros, keep only the first 15 digits, convert E notation, and convert date-like combinations. Turn all four off. It costs nothing, and it leaves three gaps.

The date-like switch is narrower than it sounds. Microsoft describes it as covering continuous letter and number combinations such as JAN1, and warns that values including a space or other character, such as JAN 1 or JAN-1, may still be treated as dates. Nothing in the list covers a purely numeric date pattern. Turn every switch off and 3/4 is still March 4. In a parts catalog or a size column, that is the most common damaging case there is, and the switches do not reach it.

The switches also do not apply everywhere. Microsoft states they do not directly affect data imported using Power Query, which is the Data then From Text/CSV path. The safest import route in Excel and the safety switches are two separate systems, so knowing someone turned the switches on tells you nothing about how the file actually came in.

The third gap decides the whole approach. Fixing the format afterward does not bring the characters back. Microsoft's guidance on long numbers says formatting a column as text will not restore previously entered numbers and only affects new entries. Once 02138 is the number 2138, the zero is not hiding behind a format. It is gone. Any process that relies on somebody noticing and correcting a column after the file is open has lost before it started, which is why this is not solvable with training. Every taught fix is a per-person, per-file, per-open discipline standing against a default that is silent and irreversible.

Your human file and your machine file cannot be the same file

The encoding half has a cleaner answer than the type half, and it is one most teams have never actually decided.

A UTF-8 byte order mark is three bytes, 0xEF 0xBB 0xBF, at the very start of the file. Python's documentation records where it came from: Microsoft invented the variant for Notepad, as a signature that helps a reader guess the encoding correctly. It works. Excel on Windows opens a marked UTF-8 CSV with accented names intact, and opens the byte-identical file without the mark using the system code page, turning José into José.

Now run it the other way. A parser that is not expecting the mark reads those three bytes as the first three characters of the file, which is usually the first header. Your column is not named id. It is named three invisible characters followed by id. The workflow reports a required column missing while you are staring at it in the file, and the error never mentions encoding. This is common enough that Python ships a dedicated codec for it, utf-8-sig, whose only job is to skip the mark on decode.

The requirement inverts depending on who reads the file. Human on Windows: write the mark. Machine parser: do not write it, or strip it at the parse step. One file cannot satisfy both, and the usual compromise, write it and hope the parser copes, makes your data correctness depend on a library default nobody chose.

So stop trying to make one file serve both. If people need to eyeball the data, produce a separate export for reading, with the mark and with identifiers already formatted for display. The feed the automation consumes is a different file that no person opens.

Better still, take CSV out of the machine path entirely. JSON has a string type, so "zip": "02138" is unambiguous at every hop and no reader infers anything. When two systems both expose an API, putting a CSV between them is a type-erasing step you chose to add, and removing it is usually the cheapest reliability win available in a file-based integration.

What to do next

Run a two-minute check on your highest-value identifier column. Pull the values from the source system, pull the same column from the destination, and compare character lengths. If the source is uniformly five characters and the destination holds a mix of four and five, you have already been losing leading zeros, and nothing ever errored. Repeat on any column that can hold a slash, a letter followed by digits, or more than 15 digits.

Then put the control where it actually catches things: a shape assertion at the parse step, not a validation at the end. For each identifier column, assert the real rule, exactly five digits, or a two-letter prefix and ten characters, or whatever your data genuinely is, and fail the run when a value does not match. This is the only check that works, because a mangled identifier is a structurally valid value. Nothing else in the pipeline has a reason to complain about it, which is exactly how you end up matching customer records that no longer share an ID.

If you want a second pair of eyes on where files still sit inside your integrations, and which of those hops can be removed rather than hardened, tell us what you are moving and between which systems.