Data exchange guide

Moving data between JSON and CSV

A practical way to decide what can be preserved when a nested document crosses into a flat table.

First decide whether the data is actually tabular

CSV represents rows and columns. JSON can represent arrays, nested objects, nulls, mixed types, and repeated structures. A JSON array of records is a good candidate for conversion when each record has a stable set of scalar fields. A deeply nested API response usually needs a deliberate projection before it becomes a useful spreadsheet.

The JSON CSV Converter is useful for fixtures and review copies, but it cannot infer the business meaning of every nested field.

Define the column contract

Choose column names, required fields, empty-value rules, and a type policy before exporting. Decide whether an empty string means “unknown”, “not applicable”, or an intentionally empty value. Keep identifiers such as 00127 as strings when leading zeroes matter; spreadsheet software may otherwise turn them into numbers.

Quoting and delimiters are data

A comma, quote, or line break inside a field must be represented according to the CSV dialect. A quoted field containing a quote usually doubles that quote. Do not split rows by commas with a simple string operation. Confirm the delimiter, quote character, escape rule, and newline convention used by the receiving program.

name,notes\n"Ada Lovelace","Uses commas, quotes, and\nline breaks"

The second row is still one record because the quoted field owns the comma and newline.

Validate after conversion

Round-trip a representative sample: convert JSON to CSV, parse the CSV again, and compare required values. Test an empty array, missing fields, Unicode text, long values, embedded delimiters, and a value that looks numeric but is an identifier. Then import the result into the actual spreadsheet, database, or API that will consume it.

Checklist

1. Confirm the source is an array of records or define a flattening rule.
2. Write down column names and type expectations.
3. Test quoting, delimiters, newlines, and Unicode.
4. Preserve identifiers and timestamps intentionally.
5. Validate the converted file in the destination system.

Reference

For a standards-oriented description of the JSON format, see RFC 8259. CSV dialect details must be agreed with the receiving application.