Fundamentally different shapes
CSV is inherently flat — rows and columns, nothing more. JSON is inherently hierarchical — objects can nest inside objects, arrays can nest inside objects, to arbitrary depth. This single structural difference decides almost everything about when each format fits:
{"name": "Ada", "address": {"city": "Delhi", "zip": "110001"}, "tags": ["engineer", "mathematician"]}
name,address.city,address.zip,tags
Ada,Delhi,110001,"engineer,mathematician"
Converting the nested object to CSV requires flattening address into separate address.city/address.zip columns (a convention, not a standard), and the tags array either gets joined into one delimited string (losing its list structure to the CSV reader) or split across multiple ambiguous columns.
Why CSV opens directly in a spreadsheet and JSON doesn't
CSV's row/column shape maps directly onto how spreadsheet software already thinks about data — no transformation needed, which is exactly why CSV remains the default export/import format for Excel, Google Sheets, and most reporting/BI tools. JSON's nested structure has no natural spreadsheet grid representation, so opening raw JSON in a spreadsheet either fails outright or requires an explicit flattening step first.
Where each actually wins
| Need | Use |
|---|---|
| Data will be opened/edited in a spreadsheet | CSV |
| Data has genuine nesting (objects within objects, variable-length lists) | JSON |
| Values need real types (numbers, booleans, nulls, not just strings) | JSON |
| Bulk import/export between business tools | CSV (near-universal support) |
| API request/response payloads | JSON (native web/programming language support) |
Why "everything is a string" in CSV is a real cost
CSV has no type system — a numeric-looking value, a boolean-looking value, and an actual string are all indistinguishable until something parses them with assumed types. This is exactly how leading zeros get lost (00123 read as the number 123 by a spreadsheet's auto-detection) and how "true"/"false" text needs explicit interpretation rather than being usable directly as a boolean the way JSON's native true/false are.
Common mistakes
- Flattening nested JSON to CSV without a documented convention. Different tools flatten nested keys differently (
address.cityvsaddress_cityvs separate sheets) — pick one and keep it consistent, or downstream consumers will disagree on structure. - Assuming a CSV column's content type without checking. A column that looks numeric might contain some non-numeric values further down, silently breaking naive type assumptions.
- Choosing CSV for data with genuinely variable-length nested lists. Forcing that into flat columns either loses information or produces an unpredictable number of columns per row.
FAQ
Can any JSON document convert cleanly to CSV?
Only if the JSON is already reasonably flat, or you're willing to define an explicit flattening convention for nested objects and arrays — deeply nested or variable-shaped JSON doesn't convert losslessly.
Why do numbers sometimes look wrong after opening a JSON-derived CSV in Excel?
Spreadsheet software auto-detects types from plain text, which can strip leading zeros or reformat numbers unexpectedly — CSV has no type system to prevent this, unlike JSON's native number type.
Is JSON always the better choice for APIs over CSV?
For most APIs, yes — JSON's native types and nesting match how most application code naturally models data; CSV remains better suited for bulk spreadsheet-oriented workflows.
Convert and inspect JSON structures with the JSON Toolkit — runs entirely in your browser.