Converting JSON to CSV without losing data

Two formats with different shapes

JSON (RFC 8259) describes nested values: objects inside arrays inside objects, with types such as numbers, booleans and null. CSV has rows and columns and nothing else. Every value in a CSV file is text, and there is no standard way to say "this column is a number" or "this cell is empty because the value was null".

CSV does not even have a single strict standard. RFC 4180 is an informational document that describes the format "followed by most implementations" and registers the text/csv media type. Its rules are short: records separated by CRLF line breaks, an optional header line, fields separated by commas, and fields that contain commas, double quotes or line breaks enclosed in double quotes, with any double quote inside such a field written twice. Fieldkit follows those rules, and they are the safest target when you do not know which program will read the file.

Step 1: pick the rows

The converter expects an array of objects and turns each object into one row. A single object becomes a one-row table. Before converting, look at where the records actually live. Many APIs wrap them, as in {"data":[...],"page":2}. Converting that whole response gives you one row with a data column full of JSON text. Copy only the array that holds the records.

Newline-delimited JSON (one object per line, often called NDJSON or JSON Lines) is not a single JSON value, so it fails to parse. Put the lines inside square brackets and add commas between them first.

Step 2: build the header from every object

Real data is uneven. Some records have optional fields, and others gained new fields after a schema change. The header has to be the union of all keys, not the keys of the first object. Fieldkit collects keys in the order they first appear and leaves a cell empty when a row lacks a key.

That rule also covers null. A null value and a missing key both become an empty cell, so the CSV can no longer tell them apart. If that difference matters to whoever reads the file, replace nulls with an explicit marker in the JSON before converting.

Step 3: decide what to do with nesting

Nested objects are flattened into dotted column names. {"address":{"city":"Recife"}} becomes a column called address.city. This works well for one-to-one data such as an address or a settings block.

Arrays are different. An order with three line items cannot become one row without losing something. Fieldkit keeps arrays as compact JSON text in a single cell, so nothing is silently dropped, but that cell is hard to use in a spreadsheet. Here is what happens with two orders:

[{"order":"A-1001","customer":"Ana","items":[{"sku":"MUG-01","qty":2},{"sku":"TEE-M","qty":1}]},
 {"order":"A-1002","customer":"Bruno","items":[{"sku":"MUG-01","qty":1}]}]

The output keeps one row per order, with the items as quoted JSON:

order,customer,items
A-1001,Ana,"[{""sku"":""MUG-01"",""qty"":2},{""sku"":""TEE-M"",""qty"":1}]"
A-1002,Bruno,"[{""sku"":""MUG-01"",""qty"":1}]"

If you want one row per item, unwrap the array first. In a browser console or Node.js:

const rows = orders.flatMap(o =>
  o.items.map(i => ({ order: o.order, customer: o.customer, sku: i.sku, qty: i.qty }))
);
JSON.stringify(rows);

Paste the result into the converter and you get a tidy table with Rows 3:

order,customer,sku,qty
A-1001,Ana,MUG-01,2
A-1001,Ana,TEE-M,1
A-1002,Bruno,MUG-01,1

The order and customer values repeat on each item row. That repetition is normal in a flat table, and it is what makes filtering and pivot tables work.

One edge case to know about: dotted names can collide. If one object has a literal key "a.b" and another has {"a":{"b":3}}, both values land in the same a.b column. It is rare, but it happens with keys that already contain dots, such as domain names or version numbers.

Step 4: quoting and escaping

You rarely need to think about quoting if the converter follows RFC 4180, but it helps to recognize the result. Here is a small contact export with an embedded quote, a null, a boolean and a note with a line break:

[{"id":101,"name":"Ana \"Nina\" Costa","active":true,"plan":null},
 {"id":102,"name":"Bruno Lima","active":false,"notes":"Prefers email\nafter 6pm"}]

The name becomes "Ana ""Nina"" Costa": wrapped in quotes, with each inner quote doubled. The plan cell is empty. The note is quoted, and the line break stays inside the quotes, so a CSV parser reads it as one field even though it spans two physical lines. Tools that split the file on line breaks without parsing will break that row in two, which is a good reason to read CSV with a real parser rather than split("\n").

RFC 4180 also says spaces are part of a field. " padded " keeps its spaces in the output, and a careful importer keeps them too. Trim in the JSON if you do not want them.

Numbers that change on the way

Two different things can alter numbers. The first happens during the JSON parse. JavaScript numbers are IEEE 754 doubles, and RFC 8259 warns that integers outside the range -(2⁵³)+1 to 2⁵³-1 may not survive between systems. Convert [{"id":9007199254740993}] and the CSV says 9007199254740992. The fix belongs at the source: send large IDs as strings.

The second happens when a spreadsheet opens the file. Excel stores at most 15 significant digits, so long numeric IDs and card-like numbers lose their last digits. Values such as 00042 are read as the number 42. Fieldkit writes 00042 exactly as it was in the JSON, but the spreadsheet decides how to display it. To keep such columns as text, import the file instead of double-clicking it, and set those columns to Text in the import preview.

Opening the file in Excel

Fieldkit writes UTF-8 without a byte order mark. Microsoft's own support article says Excel opens a UTF-8 CSV normally when it has a BOM, and otherwise recommends importing it through Data, Get Data, From Text/CSV. In that dialog, set File origin to 65001: Unicode (UTF-8) and confirm the delimiter is a comma. That avoids names like José and keeps the columns aligned.

Formula injection

When a CSV includes text that came from users, a cell that starts with =, +, - or @ can be treated as a formula by spreadsheet programs. OWASP calls this CSV injection. A converter that follows RFC 4180 does not change those cells, and neither does Fieldkit: =1+2 comes out as =1+2. If you produce CSV files from user data for other people to open, read the OWASP guidance and sanitize in your export code. Note that OWASP itself says no single fix is safe for every spreadsheet program and every downstream reader.

Checklist

1. Convert the array of records, not the whole API envelope.
2. Unwrap arrays that should become rows before converting.
3. Send large IDs and codes with leading zeros as strings.
4. Import into spreadsheets with UTF-8 and text columns where needed.
5. Read CSV in code with a parser that understands quotes.

Open the live tool

Open JSON to CSV Converter Related reading: all guides · FAQ.