Convert JSON to TSV

A comma is the most common character in prose after the space, and a delimited file that uses it as a separator has to escape every value that contains one. The mechanism works, and it is also the source of nearly every mangled import anyone has seen: one unbalanced quotation mark and the rest of the row lands in the wrong columns.

Tabs occur inside real data almost never. Nobody types a tab into an address field, a product description or a comment box, because in most interfaces the key moves focus instead. That is the whole argument, and for a one-off job it is a strong one: the separator that does not appear in your data does not need escaping.

Excel and Google Sheets both read tab-separated text from the clipboard natively. Copy the converted output, click a cell, paste, and the data lands in columns with no dialog, no delimiter question and no import wizard. A CSV pasted the same way arrives as one column of text per row, which is why people end up on this page.

That makes this the quickest route from a JSON export to something you can sort and filter, and it is worth being honest about when it is the wrong route. If the file is going to be kept, sent to somebody or imported repeatedly, save a CSV or a workbook instead — the clipboard is a convenience, not a delivery mechanism, and pasted data carries no record of where it came from.

Each record in the array becomes one row, and the columns come from the keys. Records do not have to agree: the conversion collects the union of every key across the whole file and leaves a cell empty where a record has nothing for it, so an export where some entries carry an optional field converts without any of them being dropped.

That reconciliation explains a common surprise. Converting the first ten records for a test can produce fewer columns than converting the whole export, because the sample did not contain the rarer fields. If a column you expected is missing, the reason is almost always that no record in the file had it, which is itself worth knowing.

API responses rarely hand back a bare array. They hand back an object with one key — results, data, items — whose value is the array you want. The conversion recognises that shape: an object with exactly one key holding an array is unwrapped, and the records inside become the rows.

An object with two keys is not unwrapped, because there is no way to know which of them is the data and which is the pagination cursor. In that case the whole object becomes a single row with dotted column names, which looks wrong and is the signal to delete the envelope in a text editor and convert again.

JSON is a tree and a table is a grid, so nesting has to go somewhere. It goes into the column names: a record with a customer object containing a city produces a column headed customer.city. One level of nesting produces a table anybody can read, and two is usually still fine.

Beyond that it degrades quickly. Column names grow long, the table becomes very wide, and most cells are empty because different records nest differently. Sixty columns out of what looked like a simple export is the shape of the data showing through rather than a fault, and it is a reasonable moment to decide the JSON should stay JSON.

A record with three tags becomes three columns named tags.0, tags.1 and tags.2. That is honest, it preserves every value, and it is a poor table: the next record with five tags adds two more columns, and sorting by "the second tag" is meaningless.

Where the array carries the meaning — an order with line items, a post with comments — the useful answer is two tables joined by an identifier, the way a database would hold it. Where it is incidental, joining the values into one string in the JSON before converting produces a far more usable spreadsheet than a run of numbered columns does.

The claim that tabs never appear in data is true almost always and not quite always. Text copied out of a PDF, a code snippet in a comment field and anything pasted out of another spreadsheet can all carry one. A newline inside a value is more common still.

When it happens, the value is wrapped in double quotation marks, which is the standard escape and is read correctly by every tool that imports a file. The clipboard is where it is least reliable, because paste handling varies between applications and versions. If you know the data contains free text with line breaks in it, download the file and use the import path rather than pasting.

Numbers are written as numbers, booleans as true and false in lower case, and a null as an empty cell. Nothing is quoted that does not need to be, so the file reads exactly as it looks.

What happens next is out of the file’s hands. Excel and Sheets both infer a type per column on paste, which strips leading zeros from postcodes and product codes and turns anything date-shaped into a date. The defence is the same as always: format the destination columns as text before pasting, or use an import path where the column types can be set. It is not something the conversion can prevent, because the information is already correct when it arrives.

The clipboard is one audience for a TSV; the other is software that expects tabs by default. The Postgres COPY command uses tab as its default delimiter, R read.delim expects tabs, and a long tail of bioinformatics and log-processing tools take tab-separated input and nothing else.

For those the output is ready as it is, with one caveat worth checking: lines end with a plain newline rather than a carriage return and newline, which is what Unix tooling expects and what a few Windows-only importers dislike. If a loader complains about the line ending, converting it is one pass through a text editor.

Parsing and writing are both JavaScript in this tab. Nothing is uploaded, there is no sign-up and no daily allowance, and the free tier accepts up to 100 MB — with the practical ceiling being your own memory, because the whole array has to be read before anything can be written.

That is worth more than it sounds for this pair. A JSON export is nearly always raw API output — customer records, order histories, form submissions — and it usually contains fields nobody thought about because nobody expected them to be read. Getting it into a spreadsheet without it passing through anybody’s server is the simple version of not having to think about that at all.