JSON to CSV: Differences, Edge Cases & Pitfalls
Flatten nested JSON into CSV rows with configurable key-path separators and array handling for pivots and bulk imports.
Flattening nested JSON into a table turns key paths into column names. The design decision is what happens to arrays: join into one cell, expand across rows, or emit separate columns. That choice decides whether downstream can pivot and sum, so settle it before converting.
Format Differences
| Aspect | JSON | CSV |
|---|---|---|
| Depth | Any | Two dimensions only; flattening required |
| Key set | May differ per object | All rows share one column set |
| Arrays | Native | Expand into rows, columns, or a joined string |
| Types | Explicit number/boolean/null | Strings; types live in a convention |
| Large integers | Exact as strings | Exact as text (downstream parsing may still damage them) |
| Readability | Clear structure, machine friendly | Spreadsheet friendly, loads into Excel/BI |
| Size | Compact | Even more compact, but repeated headers add redundancy |
| Best for | APIs, config | Reports, bulk import, analysis |
Edge Cases
- Values containing commas, newlines or quotes must be quoted with inner quotes doubled, or columns shift.
- Dotted keys collide with the flattening separator and create ambiguous paths; change the separator or escape.
- Implementations that take keys from the first object only drop later columns silently — compare column count against the key union.
- An empty array and a missing key both render blank, so "empty collection" and "no such field" look identical.
Common Pitfalls
- Emitting a JSON array as a row joins its elements into an unreadable string that cannot be split later.
- Dotted key names collide with flattened paths so one column name appears twice and lookups hit either at random.
- Unescaped CJK keys with embedded commas shift the entire header row.
- Generating columns from the first object alone loses later fields with no warning.
Related Tools
- JSON to CSVConvert a JSON array of objects to CSV with custom delimiters and headers, flatten nested keys and pick which fields to export for spreadsheet and BI tools.
- Data Format ConverterConvert between JSON, YAML, TOML, XML and CSV in one place with a single paste and instant preview for seamless multi-format config and data conversion.
- JSON to YAMLConvert JSON data to YAML format with custom indentation and nested object support. Turn verbose JSON configs into clean readable YAML in a couple of seconds.
- Excel to CSVConvert Excel files to CSV with custom delimiter options and batch sheet conversion. Export data for databases or scripts via SheetJS, in the browser.