JSON to CSV: Flattening Data for Spreadsheets

Why this conversion never goes away

The two formats serve different masters: JSON carries structured data between systems, CSV carries flat rows between tools and humans. Business workflows constantly cross that border — API exports landing in spreadsheets, analytics data reaching stakeholders who live in Excel. The conversion is a permanent fixture of data work, worth doing well.

The shape requirement: arrays of objects

CSV maps one object to one row, keys to columns. JSON that fits this shape — an array of flat objects — converts directly. JSON that does not (a single object, nested structures, arrays of arrays) needs reshaping first. Recognizing the shape of your input before converting saves the confused output that comes from forcing the wrong structure through.

Flattening nesting: the dotted-path convention

Objects inside objects cannot become CSV cells directly, so flattening invents columns: user.address.city becomes one column named by its path. The convention is predictable and spreadsheet-friendly — until the nesting goes deep enough that column names become sentences. The practical limit: flatten two levels confidently; beyond that, consider whether the CSV consumer actually needs the depth or a summary.

The union of keys: handling ragged records

Real JSON arrays rarely have identical keys — some records carry optional fields. Correct conversion collects the union of all keys as the column set and leaves missing fields as empty cells. The failure mode to recognize: seeing blanks and assuming corruption. Blanks in a union-of-keys CSV are honest reporting that the record lacked the field.

Quoting: the standard that keeps data intact

CSV's weakness is its simplicity — commas separate fields, so fields containing commas must be quoted, and quoted fields escape their quotes by doubling them. RFC 4180 codifies exactly this. Conversion tools that skip proper quoting produce files that look fine until the first field with a comma splits itself across columns. Correct quoting is invisible when right and catastrophic when wrong.

The Excel import problem

Double-clicking a CSV lets Excel guess the structure — and guess wrong, especially with semicolon-delimited files, unusual encodings, or leading zeros in numeric-looking fields (which Excel eagerly strips). The professional path is the import wizard: declare delimiter, encoding, and which columns stay text. Ten seconds of explicit import prevents the silent data damage that spreads through spreadsheets.

Arrays inside objects: the cell that holds JSON

When a record contains an array — tags, line items — CSV has nowhere structured to put it. The standard compromise: serialize the array as JSON text inside its cell. Functional for transport and human reading; awkward for further analysis. If the array's contents need columns of their own, the honest answer is reshaping the source data before conversion — one row per array item with parent fields repeated.

Encoding: the UTF-8 decision

CSV has no encoding declaration — consumers assume. UTF-8 is the modern default, but legacy spreadsheet workflows occasionally expect Windows-1252, producing mangled accents when mismatched. If exported names or cities show wrong characters on open, encoding — not the conversion — is the suspect. Keeping a UTF-8 with BOM option handy covers the Excel-recognizes-UTF-8 edge case.

Privacy: real records in the pipeline

The rows being converted are frequently personal — user lists, order histories, survey responses. Local conversion keeps those records in the browser through the format change, which matters exactly as much as it does at any other stage of handling them.

Flattening nested objects: the path-naming decision

Real JSON rarely arrives flat — records carry addresses, tag arrays, and nested metadata that CSV cannot represent directly. Flattening means choosing column names for each nested path: address.city, address.zip, and so on. The decision that shapes the output: dot-notation paths preserve the full structure readably, while picking only the fields you need keeps the spreadsheet clean. The failure to avoid is silent dropping — a converter that skips nested fields without naming them loses data invisibly. Review the generated header row against the source schema: every value you care about should have a named column before the file reaches the spreadsheet.

Arrays inside records: split, join, or drop

Array-valued fields have three CSV fates, and each suits different analysis. Joining into one cell (semicolon or pipe separated) keeps the record one-row but demands parsing later. Splitting into repeated columns (tag1, tag2, tag3) works when array length has a practical cap, giving filterable columns. Dropping makes sense only when the array is genuinely irrelevant to the analysis. The deciding question: what will the spreadsheet do with this field? Filtering needs split columns; a quick read tolerates joined values; downstream re-parsing argues for keeping the data in JSON longer instead of forcing the conversion early.

Encoding and delimiter choices for the destination tool

CSV is a family of dialects, and the destination picks the dialect. Excel on Windows traditionally expects semicolon separators in locales where comma is the decimal mark; most other tools expect comma. Encoding matters as much: UTF-8 without a BOM renders accented characters as mojibake in desktop Excel, while adding the BOM fixes Excel and can annoy strict parsers. The professional habit: ask where the file goes before converting — delimiter, quote behavior, and BOM — because a CSV that opens wrong in its destination is indistinguishable from a failed conversion, and the fix costs one setting chosen in advance.

Flattening JSON without losing meaning

JSON and CSV model different worlds, so conversion is a modeling decision, not a mechanical one. A flat array of objects — the common case — converts naturally: keys become columns, values become rows, and the only question is column order. Nested objects break the model: {"address": {"city": ...}} has to become address.city as a dotted column, and arrays inside records — an order's list of items — cannot become one cell at all without a policy: join them with a separator, take the first element, or emit one row per item.

Escaping is where naive conversion corrupts data. CSV has exactly three special characters: comma, quote, and newline, and any field containing one must be wrapped in quotes with internal quotes doubled. Values containing commas (descriptions, addresses), quotes (quoted speech), or newlines (multi-line text) are common in real data, and a converter that does not handle all three produces files that spreadsheet software parses into shifted, misaligned columns. Verify the output by opening it in a real spreadsheet — the eye catches column drift instantly.

Choose your encoding deliberately before export. UTF-8 preserves everything but older Excel versions detect it unreliably without a byte-order mark; if the destination is Excel, a BOM or UTF-16 export avoids the classic garbled-accents failure. And keep the source JSON: CSV cannot represent the structure you flattened away, so the round trip home requires rebuilding the nesting by convention.

Common mistakes with this tool

• Double-clicking CSVs instead of importing with explicit settings.
• Trusting unquoted output once a field contains a comma.
• Reading union-of-keys blanks as corruption.
• Forcing deeply nested JSON into CSV instead of reshaping first.

Frequently asked questions