Two formats, two worldviews
CSV sees the world as a table: rows of values separated by commas, one record per line, every record shaped the same. JSON sees the world as structures: objects containing named fields, which can contain lists, which can contain more objects, nested as deep as reality requires. Neither view is wrong. Almost every data headache I have ever debugged came from forcing data born in one worldview through the other without thinking about what would not survive the trip.
My admission up front, because it still stings. I once opened a client's product CSV in Excel to make one quick edit, saved it, and sent it back. Every SKU with a leading zero, like 00451, had silently become 451, and a column of product codes like 3-10 had become March 10 dates. The warehouse system rejected hundreds of rows. Nothing in Excel warned me; it was being helpful. That day taught me that the format war is really a war over who gets to interpret your data.
Where CSV wins
CSV wins on flat, rectangular, homogeneous data, and it wins hard. A million rows of transactions with the same twelve columns is CSV's home turf: the file is compact because column names appear once in the header instead of repeating in every record, it streams line by line without loading everything into memory, and every spreadsheet, database, and analytics tool on earth imports it.
It is also human-legible in the plainest way: you can open a CSV in any text editor and read it. For handing tabular data to a non-technical colleague, exporting from one system into another, or archiving something you want readable in twenty years, boring wins. CSV is gloriously boring.
Its fatal limitation is that it can only be a table. No nesting, no lists inside a cell, no mixed record shapes, and, crucially, no types: every value in a CSV is text, and what that text means is left entirely to whatever program opens it. Hold that thought, because it is the root of the Excel problem.
Where JSON wins
JSON wins the moment data stops being rectangular. A customer with a list of orders, each with a list of items, each with options, is one natural JSON object and a miserable set of denormalized CSV rows. JSON also carries real types: strings, numbers, booleans, and null are distinct in the format itself, so the string 00451 and the number 451 cannot be confused by a correct parser.
This is why JSON became the language of APIs and configuration: structure and types survive the trip between systems without a negotiation about what each column means. The costs are real, though. JSON is verbose, repeating every field name in every record, so the same tabular data as JSON is typically several times the byte size of its CSV. It also does not stream as naturally, and no spreadsheet opens it directly, which matters when the audience is analysts rather than programs. When an API hands you a wall of it, a JSON formatter is the difference between reading structure and reading soup.
What breaks in translation
Converting between the two formats is routine, and each direction loses something specific. Going from JSON to CSV, nesting has to flatten: either child objects become dotted columns like address.city, or lists get joined into a single cell, or the structure explodes into multiple rows. All three choices are lossy in different ways, and a JSON to CSV conversion forces you to pick one deliberately instead of by accident.
Going from CSV to JSON, the missing types come home to roost. Should 00451 become a number or stay a string? Is TRUE a boolean or a word? Is an empty cell null, an empty string, or absent? A converter has to guess, and every guess is wrong for someone's data. The professional habit is to know your identifier columns, zip codes, phone numbers, SKUs, account numbers, and force them to stay strings.
Then there are the quoting rules within CSV itself, which is where RFC 4180, linked below, earns its place. Fields containing commas, quotes, or line breaks must be wrapped in double quotes, and quotes inside quoted fields are doubled. Homemade exporters that just join values with commas produce files that shatter on the first product name containing a comma. If you have ever seen a CSV where the columns shift halfway down the file, you have met this bug.
- JSON to CSV loses: nesting, lists, mixed record shapes, explicit types.
- CSV to JSON must guess: numbers vs strings, booleans, nulls, and every guess needs checking.
- Both directions can mangle: encodings, line endings, and delimiter conventions across regions.
- Always round-trip a sample and compare before converting the real file.
The Excel problem, named and tamed
Excel does not open CSVs so much as interpret them. Anything that looks numeric loses leading zeros. Anything that resembles a date becomes one, in your local date convention, which is how gene researchers famously ended up renaming genes because names like SEPT2 kept turning into dates. Long numbers get displayed in scientific notation, and in some regional settings Excel expects semicolons instead of commas as the separator entirely.
None of this is malice; it is a spreadsheet aggressively guessing types that CSV never recorded. The defenses are straightforward. Import instead of opening, using Excel's dialog to mark identifier columns as text. Keep the true CSV as the master and treat spreadsheet copies as disposable views, extracting clean data back out with an Excel to CSV conversion when needed. And when files arrive from unknown pipelines with stray whitespace, smart quotes, or invisible characters, a pass through a text cleaner before parsing saves an unreasonable amount of debugging.
Encoding is the last silent killer: a UTF-8 CSV opened as the wrong encoding turns every accented name into garbage like é. If names look mangled, the data is usually fine and the interpretation is wrong. Re-import with UTF-8 selected before assuming corruption.
A decision rule and a safe pipeline
The choice compresses well. Flat rows for humans and spreadsheets: CSV. Structured records for programs and APIs: JSON. Data that must cross both worlds: store the richest form you have, usually JSON, and generate flattened CSV views for the spreadsheet audience, never the reverse, because you can always flatten structure but you cannot reliably reconstruct it.
And whatever the direction, the safe pipeline is the same four steps: convert a small sample first, inspect the exact columns you know are fragile, spot-check values against the source, then run the full file. Five minutes of paranoia per file. My warehouse incident cost considerably more than five minutes.
Questions people ask
Neither is better; they model different things. CSV is smaller and universal for flat tables; JSON preserves nesting and types for structured data. The mistake is not choosing wrong once but forcing data through the wrong shape without checking what got lost.
CSV stores everything as text, and Excel guesses types on open. A value like 00451 looks like the number 451, so Excel converts it and drops the zeros. Use the import dialog and set those columns to text, or keep identifiers quoted and untouched in the original file.
Wrap the field in double quotes, per RFC 4180: a value like "Smith, Jane" stays one field. Quotes inside a quoted field are doubled. Most broken CSVs come from exporters that skip these rules and join values with raw commas.
JSON repeats every field name in every record, while CSV states column names once in the header. The same tabular data is typically several times larger as JSON. Compression narrows the gap for storage, but for flat tables CSV stays the compact choice.
UTF-8, consistently. Most modern tools default to it, and it handles every language. If a recipient's Excel shows garbled characters, the fix is usually importing with UTF-8 explicitly selected, or exporting UTF-8 with a byte order mark for older Excel versions.