JSON vs CSV

Shape: one table versus a tree

A CSV file is a sequence of records, each the same width: name, email, amount, repeated for every row. A header row — conventional, not guaranteed — names the columns once for the whole file. JSON records instead carry their keys with them, so every object is self-describing, and an object may hold an array of objects holding arrays. That asymmetry decides the format choice more than any feature list does.

Types and quoting: declared versus inferred

In JSON, a value's type is visible: quotes mean string, bare digits mean number, and true, false and null are exact tokens. In CSV, the file asserts nothing — 007 and 7 are both plain cell text, and every consumer re-infers types under its own rules. RFC 4180 does specify quoting precisely: fields containing commas, double quotes or line breaks are wrapped in double quotes, and embedded quotes are doubled. Files that skip this produce the classic broken-row bugs every data engineer has debugged.

The re-inference step is where data quietly corrupts. A ZIP code 02134 becomes the number 2134; 1.20 becomes 1.2; a long account number becomes 4.2E+12. JSON has the mirror-image rule — numbers cannot carry leading zeros at all, so a ZIP code must be a string there too — but at least the declaration is explicit rather than a guess made by someone else's spreadsheet.

Excel, bulk data and where each format wins

Excel behaviour deserves its own paragraph because it burns so many pipelines. Excel opens CSV natively but re-types every cell using the machine's locale: leading zeros vanish, ISO timestamps mangle into locale dates, and in much of Europe the semicolon dialect rules because the comma is a decimal separator. Saving then re-writes the file with those inferences baked in permanently. JSON never passes through Excel, which alone is a reason integrations prefer it.

At scale, CSV's flatness is a feature: rows parse one at a time with constant memory, which is why multi-gigabyte exports and column-oriented tools still speak CSV first. JSON wins wherever the consumer is a program and the structure is real: API payloads, configuration, nested documents. Choosing by consumer rather than by fashion resolves most arguments in one sentence.

When to pick which — and how conversion behaves

Pick CSV when the destination is tabular: spreadsheets, database bulk loads, BI and data-science tools. Pick JSON when the structure nests or the consumer is code. The guidance reverses constantly in real pipelines: an API returns JSON, an analyst wants CSV, a vendor sends CSV that a service must ingest — conversion is routine maintenance, not a migration project.

Conversion has honest limits. JSON-to-CSV must flatten: an array of objects becomes rows, the union of their keys becomes columns, and any nested array or object has to be stringified, joined or dropped. CSV-to-JSON must infer: empty cells become null or empty strings, and numeric-looking cells become numbers. This site's converters document those exact rules instead of guessing silently.