Why does my CSV break when I convert it?

CSV is a family of formats, not a format

The single most useful thing to know about CSV is that there is no authority defining it. RFC 4180, published in 2005, describes common practice and explicitly says so; it is not a standard that producers are obliged to follow, and many predate it.

The delimiter is the first divergence. Comma is the default in English-speaking locales, but in regions where the comma is the decimal separator — most of continental Europe — spreadsheet software writes and expects semicolons. A file exported from Excel in Germany and opened in Excel in the United States lands entirely in the first column. Tab-separated files are common in scientific data and pipe-separated in older systems, and none of these announce themselves in the file.

Quoting is the second. The convention is that a field containing the delimiter, a quote character or a line break is wrapped in double quotes, and a literal double quote inside a quoted field is written twice. That rule is widely but not universally followed, and some producers escape with a backslash instead, which a compliant parser reads as a literal backslash followed by the end of the field.

Line breaks inside quoted fields are where naive parsers fail hardest. A tool that splits the file on newlines before parsing quotes will tear a single record with a multi-line address into several broken rows, and because the damage is structural the row counts no longer match anything. Any parser worth using reads character by character with quote state, not line by line.

Encoding is the last of the four. UTF-8 is now usual, but files produced by older Windows software arrive in a regional code page, and Excel writes a UTF-8 byte order mark that some parsers surface as invisible characters at the start of the first header. A first column whose name will not match anything, despite looking correct, is almost always a BOM.

What is lost going from CSV to JSON

CSV stores text. Every field, without exception, arrives as a sequence of characters with no declaration of what it represents, so a converter has to guess types or refuse to. Both choices lose something.

Refusing to guess produces JSON where every value is a string, including all the numbers, which pushes the problem downstream to whatever consumes it. Guessing produces the more interesting failures.

Leading zeros are the classic one. A postal code of 01234, a product code of 007, or a national identifier beginning with zero is a number to a type-guesser and loses its leading digits permanently. So does a long numeric identifier: anything beyond about sixteen digits exceeds the precision of a double-precision float, which is what JSON numbers are in practice, so a nineteen-digit identifier silently changes its last few digits. Identifiers should stay strings even when they look numeric, and any converter that lets you say so is worth preferring.

Dates are the second. A field reading 03/04/2026 is the third of April or the fourth of March depending on the locale of whoever wrote it, and nothing in the file says which. A converter that parses dates will pick one, consistently and possibly wrongly, and the result is a dataset with a systematic error that no later validation catches.

Empty and null are the third. CSV has one representation of absence — nothing between two delimiters — and JSON distinguishes an empty string from null from an absent key. Files that write the literal text NULL or NA or a hyphen make this worse, because those are indistinguishable from genuine values without knowing the convention.

The best-known damage of this kind is not from a converter at all but from a spreadsheet. Excel autocorrects certain text into dates on import, which for years turned the gene name SEPT1 into a September date. The problem was widespread enough in published genomics data that in 2020 the naming committee renamed the affected genes rather than continue fighting the software. It is the clearest illustration available that a tool applying helpful type inference to somebody else's identifiers is a data-integrity risk.

What is lost going from JSON to CSV

The loss runs the other way here and is structural rather than about types.

JSON nests. CSV is a rectangle. An object containing an object has no natural representation in a grid, so a converter must flatten, and the flattening convention is a decision made for you. The common approach joins the path with dots, so an address object becomes columns named address.city and address.postcode. It works, it is readable, and it collides the moment a genuine key contains a dot.

Arrays are harder because their length varies per record. Three strategies are in use, and each is lossy in a different way. Joining the values into one cell with a separator loses the boundary if any value contains the separator. Expanding to numbered columns produces a table as wide as the longest array, mostly empty, and breaks if a later record is longer. Emitting one row per array element duplicates all the other fields and changes what a row means, which is fine for analysis and wrong if anything downstream counts rows.

Heterogeneous keys are the third problem. A JSON array where objects do not share the same keys has no single header row. A converter must take the union of all keys, which requires reading the entire input before writing anything and produces a sparse table, or take the keys of the first object, which silently discards every field that only appears later. The second is faster and is what a surprising number of tools do.

The practical advice is to flatten deliberately before converting. Decide which fields matter, project the JSON down to a flat shape you control, and convert that. A converter guessing at your structure will produce something that opens in a spreadsheet and quietly misrepresents the data.

YAML and query strings have their own traps

These formats sit adjacent to the same work and carry problems worth naming, because both are frequently converted to JSON.

YAML is best known for what is called the Norway problem. In YAML 1.1, the unquoted tokens y, yes, on, true and their negatives are booleans, so a list of country codes containing NO becomes a list containing false. Version 1.2 narrowed this to true and false only, but a great many parsers still implement 1.1 semantics, and the failure is silent. Quote anything that could be read as a keyword.

YAML has two further sharp edges. Tabs are illegal as indentation, which produces confusing errors from an editor configured to insert them. And a number with a leading zero was octal under 1.1, so a value of 0755 is 493, which matters exactly when it is a file permission and nowhere else.

Query strings have no specification for structure beyond key-value pairs. Repeated keys are the only agreed way to express a list, and whether a consumer reads the first, the last or all of them is entirely implementation-dependent. Bracket conventions for arrays and nested objects are a PHP invention that many frameworks copied and many did not. And a literal plus sign in a value means a space in form encoding, which is why a search for a term containing one arrives mangled unless it was encoded as its percent escape.

Questions