Nearly every system you use needs to hand data to another system: a shop exports orders to accounting software, an app sends your profile to a server, a researcher shares a dataset with a colleague. For plain-text data exchange, two formats dominate the world: CSV, the humble comma-separated values, and JSON, the web's structured workhorse.
They solve the same problem — moving data between programs — in opposite ways. CSV is flat and minimal; JSON is nested and expressive. Choosing well saves hours of debugging. This article explains what each format is, when each wins, and the traps that catch everyone at least once.
CSV: the humble spreadsheet
CSV is exactly what the name says: values separated by commas, one record per line. A customer list looks like this: name,email,city on the first line, then one line per customer. No types, no nesting, no metadata — just a grid. Spreadsheet programs have read and written it for decades, which is why it became the universal lowest common denominator.
Its strength is that anything can produce and consume it. Databases export it, Excel opens it, and a human can read it in a text editor. For rectangular data — rows of the same shape — nothing is simpler. Millions of business processes still run on nightly CSV exports, and they will for decades.
The price of that simplicity is ambiguity. CSV has no official standard (the closest is RFC 4180, which many tools ignore), so dialects differ on delimiters, quoting and line endings. “CSV” from one tool is not always CSV another tool expects.
JSON: data with structure
JSON — JavaScript Object Notation — describes data as objects (key-value pairs) and arrays (ordered lists), nestable to any depth. A customer in JSON can carry an address object inside it, an array of past orders inside that, and each order its own line items. CSV cannot do this without ugly workarounds; JSON does it naturally.
It became the web's default because it maps directly onto the data structures programmers actually use, and because it is still plain text a human can read. Nearly every public API speaks JSON. Configuration files, from package managers to cloud infrastructure, are JSON (or its supersets).
JSON also carries types: strings, numbers, booleans, null, arrays and objects are distinct. CSV has only text — the number 42 and the string “42” are identical until something interprets them, which is a rich source of bugs.
When CSV wins
Choose CSV when your data is genuinely tabular: every record has the same fields, no nesting, no variable-length lists. Exports for accountants, bulk imports into databases, dataset downloads for analysis — CSV is smaller, faster to parse, and opens directly in Excel or Google Sheets, which is often the actual requirement.
Size matters at scale. CSV's minimal syntax means less overhead per record than JSON's repeated key names. A million-row export can be dramatically smaller as CSV, and streaming parsers can process it line by line without loading everything into memory.
Rule of thumb: if a spreadsheet is the natural home for the data, CSV is the natural format for moving it.
- Database exports and bulk imports
- Data for Excel / Sheets users
- Large flat datasets where size matters
- Simple logs with fixed columns
When JSON wins
Choose JSON the moment your data has shape: nested objects, arrays of varying length, optional fields, or mixed types. An API returning a user with three addresses and a variable number of orders cannot be faithfully flattened into rows without either duplicating data or losing structure.
JSON also wins when machines are the only audience. APIs, configuration, and communication between services all benefit from explicit structure and types. A JSON parser in any language turns the text into ready-to-use objects; a CSV parser gives you strings you must interpret yourself.
Rule of thumb: if a programmer would draw the data as a tree rather than a table, use JSON.
- Web APIs and service-to-service messages
- Configuration files
- Nested or variable-shape records
- Data where types (numbers, booleans, null) matter
The pitfalls nobody warns you about
CSV's quoting rules bite everyone eventually. A field containing a comma — “Springfield, Ohio” — must be wrapped in quotes. A field containing a quote doubles it: “She said “hii”” becomes "She said ""hi""". Fields can even contain newlines inside quotes, which breaks naive line-by-line parsers. Then there is encoding: a CSV saved as UTF-8 opened in Excel on Windows may show mojibake unless it carries a byte-order mark, and Excel famously converts long numbers and gene names (SEPT2 became a date) on import.
JSON has its own traps. Trailing commas are illegal (unlike in JavaScript), duplicate keys are silently overwritten, and there is no date type — dates are strings by convention, and “2026-10-17” means different moments in different timezones unless you use ISO 8601 with an offset. Large numbers can lose precision in JavaScript, which is why IDs are often sent as strings.
The meta-lesson: both formats look trivially simple, and both punish the assumption that simple means safe. Validate on input, test with adversarial data (commas, quotes, emoji, empty fields), and never hand-roll a parser when a battle-tested library exists.
Frequently asked questions
Can I convert between CSV and JSON?
Yes, and tools for it are everywhere — including free online converters. CSV to JSON is straightforward when every row has the same columns. JSON to CSV requires flattening: nested objects become dotted keys like address.city, and arrays must be joined or split into multiple rows. Round-trips can lose structure, so convert deliberately, not casually.
Which format produces smaller files?
Usually CSV, because it does not repeat key names on every record. For a million identical rows, JSON's {"name":...,"email":...} overhead adds up. But compressed (gzip), the difference shrinks, and for nested data JSON is smaller than any CSV workaround.
Why does Excel mangle my CSV?
Three usual suspects: delimiter (some locales expect semicolons), encoding (missing BOM makes UTF-8 look garbled on Windows), and type guessing (long IDs lose digits, dates get reformatted). Import via Data > From Text/CSV instead of double-clicking, and set column types explicitly.
Is JSON always better for APIs?
For machine-to-machine APIs, essentially yes — structure and types win. The exceptions are bulk data endpoints, where formats like CSV, Parquet or protocol buffers move tabular data far more efficiently than JSON's verbose key repetition.
Conclusion
CSV and JSON are not rivals; they are tools for different shapes of data. Flat, uniform, human-opened-in-a-spreadsheet data belongs in CSV. Nested, typed, machine-consumed data belongs in JSON. Know both, respect each one's pitfalls, and you will spend your debugging time on real problems instead of quoting rules.