CSV vs JSON: Choosing the Right Data Format

The same records, both ways

id,name,city,orders
1,Ada,London,12
2,Grace,New York,7

[
  { "id": 1, "name": "Ada",   "city": "London",   "orders": 12 },
  { "id": 2, "name": "Grace", "city": "New York", "orders": 7 }
]

For flat, uniform rows CSV is dramatically more compact — the field names appear once instead of on every record. Add one nested field and that advantage disappears.

Comparison

CSV's rough edges

• There is no single CSV standard — separators, quoting and line endings all vary
• Locales that use a comma as the decimal separator often export semicolon-delimited files
• Fields containing commas, quotes or newlines must be quoted, and quotes doubled inside
• Leading zeros and long identifiers are mangled by spreadsheet auto-typing
• Character encoding is not declared in the file, so UTF-8 is frequently misread

Choosing between them

• Use CSV for tabular exports, spreadsheet handoff, bulk database loads and analytics files
• Use JSON for API payloads, configuration, nested records and anything with optional fields
• Use NDJSON — one JSON object per line — when you want JSON's structure with CSV's streamability
• Use Parquet instead of both when datasets grow into the gigabytes

Converting between them

JSON to CSV requires flattening: nested objects become dotted column names such as address.city, and arrays either become indexed columns or a joined string. Both are lossy in the sense that the original shape has to be reconstructed by convention. CSV to JSON is simpler, but every value arrives as a string, so types must be inferred or declared.

{ "id": 1, "address": { "city": "London" }, "tags": ["a","b"] }

id,address.city,tags
1,London,"a|b"

Common mistakes