CSV Type Inference: When "42" Should Stay a String

A CSV file contains no types. Every value is bytes between delimiters. The moment a parser reads one, it guesses: 42 becomes an integer, 01234 becomes 1234, TRUE becomes a boolean, and a ZIP code quietly loses its first digit. This post covers CSV type inference — what each layer guesses, which guesses destroy data, and how to decide when "42" should stay a string.

#TL;DR

• RFC 4180 defines no types. Every type you see was invented by the parser.
• Use the arithmetic test: if summing the column is meaningless, keep it a string.
• Leading zeros, big IDs and 1e5 are the three classic destruction cases.
• In pandas, dtype= stops conversion; low_memory=False does not.
• Papa Parse's dynamicTyping accepts a per-column object — use it, not true.

#What CSV type inference actually is

Type inference is the step where a reader looks at the characters 4 and 2 and decides they represent the number forty-two rather than a two-character string. Nothing in the file authorises that decision.

#RFC 4180 defines no types at all

Read the grammar in RFC 4180 and you will find file, record, field, escaped, non-escaped, COMMA, DQUOTE, CRLF and TEXTDATA. That is the complete vocabulary. There is no number, no boolean, no date, no null. Per the spec, a field is a run of text data and nothing more.

This matters because it means there is no correct answer supplied by the format. When two tools disagree about whether a column is text or numeric, neither is violating the standard — the standard has no opinion.

#Three places the guessing happens

Inference is not a single step. It usually runs three times on the same data, in different tools, with different rules:

• The spreadsheet. Excel, Numbers and LibreOffice infer on open, and Excel additionally applies locale rules and date autocorrection.
• The parser. Papa Parse, Python's csv module, pandas, Ruby's CSV, Go's encoding/csv — each has its own defaults, and some have none.
• The destination. A database column type, a JSON writer, or a spreadsheet cell format applies a final coercion.

Data survives the round trip only if all three agree. They frequently do not.

#The three failure classes

Narrowing is the common one and the one people notice late — usually in production, when a ZIP code lookup starts returning nothing.

#Why does my CSV lose leading zeros in Excel?

Because 01234 is a valid decimal literal, and integers do not store leading zeros. The conversion is lossy and irreversible: once the cell holds the number 1234, the original text is gone from memory. Re-formatting the cell as text afterwards gives you 1234, not 01234.

#ZIP codes, SKUs, and phone numbers

These three account for most real-world reports of the problem, and they share a property: they are identifiers that happen to use digits. A US ZIP code like 02134 is Boston. Parsed as a number it becomes 2134, which is not a ZIP code at all. Product SKUs like 0049-2201 and phone numbers with a country prefix behave the same way.

#Why "01" and 1 are not the same value

An identifier's digits are positional characters, not magnitudes. Consider what equality means in each interpretation:

"01234" == "1234"   // false, as strings
 01234  ==  1234    // true, as numbers

If your join key goes through a numeric round trip on one side and not the other, the join silently drops rows. There is no error, no warning — just a smaller result set than you expected.

#How do I keep leading zeros when opening a CSV?

Do not double-click the file. In Excel use Data → Get & Transform Data → From Text/CSV, then in the preview pane set the column's data type to Text before loading. That applies the type at parse time, which is the only moment it can still help.

If you control the producing system, the more durable fix is to move the conversion out of the spreadsheet entirely — convert once at the boundary with a tool that lets you keep columns as text, such as the CSV ↔ JSON converter, and hand the spreadsheet a file it cannot misread.

#When should a numeric-looking CSV field stay a string?

Here is the rule that resolves nearly every case.

#The arithmetic test

If adding two values together, or averaging the column, would be meaningless, the field is an identifier — keep it a string.

Sum two ZIP codes and you get nonsense. Sum two order quantities and you get a total. That is the whole test, and it takes about a second per column.

#A decision table

The version-string row catches people out regularly. Parsed as a float, 1.10 becomes 1.1, and a sort that should place it after 1.9 places it before.

#Identifiers that only look numeric

A useful heuristic when you cannot inspect every column by hand: identifiers tend to have a fixed width across the file. If every value in a column is exactly five digits, or exactly thirteen, you are almost certainly looking at codes rather than measurements. Real quantities vary in width. You can spot fixed-width columns quickly with a pattern like ^\d{5}$ in a regex tester before deciding on a type.

#The precision traps: big integers, floats, and 1e5

Even when a field genuinely is a number, the inferred type can still be wrong.

#Why numbers above 2^53 lose digits

JavaScript's Number is an IEEE 754 double, so integers stay exact only up to Number.MAX_SAFE_INTEGER, which is 2^53 − 1, or 9007199254740991. Above that, distinct integers collapse onto the same double:

const id = "9007199254740993";
Number(id);        // 9007199254740992
Number(id) === 9007199254740992;  // true

Snowflake IDs, Twitter/X status IDs, and many 19-digit database keys live well above that line. This is why Papa Parse's dynamicTyping option explicitly refuses to convert values greater than 2^53 or less than −2^53 — the library documents that it leaves them as strings to preserve precision. That is the right default, and it is worth knowing your parser does it, because many do not.

#Why does 1e5 become 100000 in a CSV?

Because 1e5 matches the definition of a decimal literal in most languages, so a numeric parser accepts it and stores the value 100000. On the way back out, the writer serialises the value in plain notation. The number is correct; the notation is not what you wrote.

This bites hardest with scientific identifiers and part numbers formatted like 2E5 or 1E9, which are text to a human and exponents to a parser. Excel does this on open, which is why lab part numbers frequently come back as large round numbers.

#Money should not be a float

0.1 + 0.2 is 0.30000000000000004 in every IEEE 754 language, JavaScript included. If a CSV column holds currency, the safe representations are an integer number of minor units (cents) or a decimal string handed to a decimal type at the destination. Inferring 19.99 into a float and then summing ten thousand rows will drift.

#Dates, booleans, and the values that lie

#The gene-name problem: SEPT1 becomes 1-Sep

The most documented case of destructive inference in the wild comes from genomics. Excel's default settings convert gene symbols such as SEPT2 and MARCH1 into dates. A 2016 Genome Biology study by Ziemann, Eren and El-Osta (listed in the references below) screened 35,175 supplementary Excel files and found gene-name conversion errors in roughly one in five papers carrying supplementary gene lists.

The eventual fix was not a software change. In 2020 the HUGO Gene Nomenclature Committee renamed the affected symbols — SEPT1 became SEPTIN1 — because it was easier to rename twenty-seven human genes than to stop spreadsheets from guessing. That is worth remembering the next time someone calls type inference a convenience feature.

#TRUE, yes, Y, 1 — booleans have no canon

There is no agreed CSV spelling for true and false. A single export pipeline can produce all of these:

• TRUE / FALSE
• true / false
• yes / no, Y / N
• 1 / 0
• t / f (Postgres COPY output)

Papa Parse converts true and false under dynamicTyping; pandas recognises True/TRUE/False/FALSE. Neither handles Y/N. If your data uses those, normalise them explicitly rather than hoping.

#Null, NULL, N/A, and the empty string

An empty field in CSV is genuinely ambiguous: it can mean the empty string, an unknown value, or a missing column. Most parsers map it to null or NaN, which means a legitimately empty text field comes back as null and a legitimately null number comes back as 0 after a downstream ?? 0. Decide explicitly at the boundary, and if you are writing the CSV, quote empty strings as "" so the distinction survives.

#How do I stop pandas from converting a column to float?

#dtype= is the fix, not low_memory

The advice you will find most often online is to set low_memory=False. That is the wrong lever. low_memory controls whether the file is type-inferred in chunks; turning it off makes inference consistent, not absent. The column still gets converted.

The actual fix is to state the type, as documented in pandas.read_csv:

import pandas as pd

df = pd.read_csv(
    "orders.csv",
    dtype={"zip": "string", "order_id": "string"},
    keep_default_na=False,
)

keep_default_na=False is the companion flag — without it, a literal NA or N/A in a text column still becomes NaN.

If you want nothing inferred at all, dtype=str reads every column as text and lets you cast the handful you actually need. For pipelines that mostly move data rather than analyse it, that is usually the correct default.

#Papa Parse: dynamicTyping per column

In the browser, the equivalent control is an object rather than a boolean:

Papa.parse(csvText, {
  header: true,
  dynamicTyping: {
    quantity: true,
    price: true,
    zip: false,
    order_id: false,
  },
});

Passing dynamicTyping: true globally is the switch that causes most browser-side CSV data loss. Per-column is barely more work and is explicit about intent.

#A round-trip checklist

Before you ship a CSV pipeline, verify these five things:

• Every ID column is typed as text at the first parse, not corrected later.
• No numeric column exceeds 2^53 without being treated as a string.
• Currency is integer minor units or a decimal string, never a float.
• Booleans and null markers are normalised at the boundary.
• The output is re-read with the same types and compared to the input.

That last step is the one people skip. If you convert to JSON as an intermediate, the types become visible and checkable — a JSON payload states "zip": "02134" or "zip": 2134, with no ambiguity left. Running the file through a CSV ↔ JSON converter and reading the result in a JSON decoder is a thirty-second way to see exactly what your types became. The same check applies when you are loading into a database — inspect the inferred column types before the SQL converter or your migration tool commits them.

#References

• RFC 4180 — Common Format and MIME Type for Comma-Separated Values (CSV) Files — the ABNF grammar confirming CSV defines fields as TEXTDATA with no type system.
• Papa Parse — Documentation — dynamicTyping semantics, per-column configuration, and the documented 2^53 precision cutoff.
• pandas.read_csv — pandas documentation — dtype, low_memory and keep_default_na behaviour used in the Python examples.
• Number.MAX_SAFE_INTEGER — MDN — the 2^53 − 1 safe-integer limit behind the big-ID examples.
• Gene name errors are widespread in the scientific literature — Ziemann, Eren and El-Osta (Genome Biology, 2016); source of the supplementary-file scan figures.

#Related on iKit

• Start with the fundamentals of moving CSV into JSON — the end-to-end conversion guide this post's typing rules sit on top of.
• Going the other way, nested objects need flattening before they fit a CSV grid — JSON has types, CSV does not; this is where they get dropped.
• The parsing edge cases that break tools before typing even starts — quoting, embedded newlines and trailing commas, straight from RFC 4180.
• Garbled characters in Excel are an encoding problem, not a type problem — the other half of "Excel opened my CSV wrong".
• Semicolon delimiters change what a parser even sees as one field — delimiter locale rules that can merge two typed columns into one string.
• Spreadsheets carry cell formats that CSV cannot — reading XLSX directly avoids one full round of inference.