Turn JSON records into spreadsheet rows

What this works out

JSON and CSV do not disagree about punctuation; they disagree about shape. JSON can nest objects and arrays at any depth, omit a field from one record, and keep numbers, booleans and null as separate types. CSV is a rectangle of text cells. Converting between them therefore requires a declared answer to three questions: what counts as a row, which headings exist, and what happens to a value that is not already flat text.

This tool makes those decisions visible. It accepts only top-level structures that have an honest row model, discovers a stable header across the complete set, and either keeps nested values intact as JSON or flattens object paths. The downloaded bytes are then ordinary UTF-8 CSV or TSV, not a private format that needs this site to read it back.

The method

The row model comes entirely from the top level:

JSON shape	CSV interpretation
{ "name": "Ada" }	One object, one row
[{ "name": "Ada" }, { "name": "Grace" }]	Each object is a row; keys are headings
[["Ada", 36], ["Grace", 37]]	Each inner array is a row; headings are column_1, column_2, and so on
[{}, []]	Refused: the rows do not share one model
42, null or []	Refused: there is no usable table

For object rows, the header is not taken from the first record alone. Real API data is sparse: the tenth record may be the first one with a middleName or invoice.vatNumber. Every object is walked, and the first appearance of a key adds it to the end of the header. That keeps the order predictable without silently losing later fields.

The two nested modes answer a different question:

Keep as JSON in one cell preserves a nested value as compact JSON. A value such as { "city": "Berlin", "postcode": "10115" } occupies one quoted cell and can be parsed back without guessing which headings belonged to it.

Flatten objects to dot paths makes address.city and address.postcode separate columns. Dots and backslashes already present in a key are escaped, so the literal key address.city becomes address\.city and cannot collide with an object called address containing a key called city. Arrays still remain JSON because position zero is not a field name.

Once each row has values under stable headings, the CSV rule is small but strict:

plain                         → plain
contains,comma                → "contains,comma"
say "hello"                   → "say ""hello"""
two lines                     → "two
                                 lines"

A quote inside a field doubles. A field containing the separator, a quote or a line break is surrounded by quotes. Rows end with carriage-return plus newline, which is the convention described for CSV and the one spreadsheet software handles consistently.

Spreadsheet formula protection

CSV has no type information. When spreadsheet software sees a cell beginning with =, +, - or @, it may interpret the text as a formula rather than as data. That matters when a name, note or imported API field came from somebody else: exporting untrusted text can turn opening the resulting spreadsheet into an action.

Protection is therefore on by default. A JSON string such as "=2+2" becomes '=2+2, which a spreadsheet treats as text. A JSON number such as -2 remains -2, because it is genuinely numeric rather than a string whose meaning is being inferred. Headings receive the same check; field names can be untrusted data too.

Before you read on

A later JSON record introduces a field called email that the first record did not have. What should happen to the CSV?

• AIgnore email because the first object defines the schema.

  That silently loses real data, and sparse API records make it common.

• BAdd an email column and leave earlier cells empty.

  Yes. The header is the union of keys in first-seen order.

• CPut the email value in the last existing column.

  That shifts a value under the wrong heading and makes the table look valid while being false.

Add one email column at the point that key is first discovered, and write an empty email cell for records that do not carry it. A CSV header must describe the complete table, not merely its first row. Taking only the first object's keys is a tempting shortcut because it is fast and produces valid-looking output, but sparse JSON makes it a data-loss bug: a field that appears later vanishes with no error. The converter therefore scans every row for the union of keys before it writes the header.

A worked example

The unit test converts these two records with flattening, comma separation, the UTF-8 marker and formula protection enabled:

[
  {
    "name": "Ada, Ltd.",
    "active": true,
    "note": "Line 1\nLine 2",
    "profile": { "city": "London" }
  },
  {
    "name": "=2+2",
    "active": false,
    "profile": { "city": "Paris" }
  }
]

The result is:

name,active,note,profile.city
"Ada, Ltd.",true,"Line 1
Line 2",London
'=2+2,false,,Paris

Four details are doing real work:

1. profile.city is a flattened path, not an invented label.
2. The comma and the line break force their fields to be quoted.
3. The second record has no note, so its note cell is empty rather than allowing Paris to slide one column left.
4. =2+2 receives an apostrophe because it came from a JSON string; the boolean values remain the typed text true and false.

The physical file is 95 bytes: 92 bytes of visible CSV and line endings plus the three-byte UTF-8 marker. The test asserts the entire string byte for byte, all four output figures, the filename, and the MIME type. Separate tests cover changing delimiters, TSV naming, union columns, nested JSON cells, escaped path segments, missing values, adversarial width, progress and cancellation.

What it does not do

It does not infer a relational database from arbitrary JSON. Nested arrays are not exploded into child tables, objects are not joined by guessed identifiers, and a primitive top level is not wrapped in an invented value column. Those can all be useful operations, but each needs a schema decision the file itself does not supply.

It also cannot preserve the distinction between null and missing in CSV. Both become an empty cell because an empty cell carries no type. Writing the word null would preserve one value at the cost of making a real string "null" indistinguishable, so that convention would need to be chosen by the importer, not silently imposed here.

Finally, it does not evaluate formulas, dates or locale-formatted numbers. A JSON number is written with JSON’s decimal syntax, a string stays a string, and the spreadsheet decides how to display it after import. The converter’s job is to move data into stable rows without changing what the source said.