JSON to CSV: flattening nested data without losing meaning

The column problem

Two rules make a JSON to CSV conversion predictable. First, the columns are the union of all keys across all records — a key that appears in only one object still gets a column, empty elsewhere. Second, every row must emit values in the same order. Getting either wrong produces a file that opens fine and is silently misaligned from the first row where a key was absent.

Flattening nested objects

The conventional technique is dot notation: `address` containing `city` and `postcode` becomes `address.city` and `address.postcode`. That keeps every value visible and queryable in the spreadsheet, at the cost of longer headers. Deeply nested structures can produce unwieldy names, so flatten one level where you control the data and let the tool flatten fully when you are exploring.

                {"id": 7, "customer": {"name": "Ada", "plan": "pro"}}
→ id,customer.name,customer.plan
   7,Ada,pro
              

Arrays: the part that flattens badly

An array of scalars can be joined into one cell with a separator, which is fine for tags and acceptable for short lists. An array of objects — order line items, for example — does not belong in a single cell: join it, and you cannot sum the quantities; index it into `items.0.sku`, `items.1.sku`, and the columns depend on how many items each order happened to have. When you hit that case, the data wants two CSV files, one per table, linked by a key.

If a cell contains a comma, quote or newline, it must be quoted and internal quotes doubled. Generated output handles this — hand-edited CSV usually does not, which is why imports fail intermittently on rows containing addresses.

Making the file behave in Excel

Excel assumes the system encoding unless told otherwise, so accented characters can corrupt. Emitting UTF-8 with a byte-order mark avoids that; without it, the same file that looks perfect in Numbers or Sheets shows mojibake in Excel. Long numeric identifiers — card numbers, large SKUs — are also worth quoting so Excel does not convert them to scientific notation.

When CSV is the wrong output

If the consumer is a program, CSV throws away type information and nesting for no benefit — JSON Lines or a database load is better. CSV is for humans with spreadsheets: reports, exports, reconciliation. Choosing it deliberately, rather than by habit, is half the work. The YamlBox JSON to CSV converter runs entirely in the browser, so an export containing customer rows never leaves your machine.