JSON to TSV: The Clean Way to Paste Records Into a Spreadsheet

You have a JSON array of objects from an API, a log export, or a script, and you need it in a spreadsheet by the next standup. The instinct is to reach for CSV, because everybody knows CSV. Then one customer name has a comma in it, the layout shears sideways, and you spend ten minutes hunting for the row that broke. TSV avoids that whole class of pain. It uses a tab between cells instead of a comma, and tabs are exactly what Excel and Google Sheets split on when you paste. This post walks through converting a JSON array into TSV, why the tab matters, how object keys turn into a header row, and what happens when records do not all carry the same fields.

A JSON array of objects becomes rows and columns

The conversion has a simple shape. Each object in the array is one row. Each key across those objects is one column. The first line of output is a header: the object keys, separated by tabs. After that, every object writes its values in the same column order, one row per line.

Concretely: object keys become a tab-separated header row, and each object becomes one data row underneath it. Where a record is missing a key that other records have, that cell is left empty so the columns never shift out of alignment. The header is the union of every key seen across the array, kept in the order each key first appears, so the column layout is deterministic no matter how ragged the input is.

That deterministic order is the part people overlook. If your records come from a paginated API where later pages add an optional field, a naive converter might reorder columns per row and turn your sheet into confetti. Building the header from the first-seen union once, up front, keeps every row pointing at the right column.

Why tabs paste cleaner than commas

Here is the core argument for TSV over CSV, and it is worth being precise about.

CSV's delimiter is the comma, which is also one of the most common characters in real data. Prices, addresses, sentence fragments, and "Last, First" name formats all contain commas. The CSV spec handles this by quoting any field that contains a comma and doubling inner quotes. That works on paper, but it means the moment a value contains a comma you depend on a quoting-and-escaping dance to keep cells from splitting. Paste a quoted CSV blob into a cell and many spreadsheets do not honor the quotes the way a file importer would, so the row tears apart at the first comma inside a value.

Tabs sidestep the entire problem. Real-world text almost never contains a literal tab character. Names, prices, descriptions, and IDs are tab-free in practice, so TSV cells stay clean with no quoting at all. Better still, Excel and Google Sheets both treat a pasted tab as a column break and a pasted newline as a row break by default. That is the magic: copy TSV, click cell A1, paste, and the data lands across columns and rows with no import wizard and no "comma or semicolon?" delimiter prompt that CSV so often triggers in non-US locales.

The short version: tabs avoid the comma-in-value escaping that breaks CSV pastes. Use TSV when the destination is a spreadsheet paste. Reach for CSV only when a downstream program specifically expects a .csv file.

A worked example

Take this JSON array of three records, where the third one is missing the email field:

[
  {"name": "Ada Lovelace", "role": "Engineer", "email": "ada@example.com"},
  {"name": "Linus Torvalds", "role": "Maintainer", "email": "linus@example.com"},
  {"name": "Grace Hopper", "role": "Admiral"}
]

Run it through and you get TSV where each arrow below is a literal tab:

name →  role →  email
Ada Lovelace →  Engineer →  ada@example.com
Linus Torvalds →  Maintainer →  linus@example.com
Grace Hopper →  Admiral →

Three things to notice. First, the header is the union of all keys — name, role, email — in first-seen order. Second, every value sits under its own column because tabs separate them, and none of these values need quoting. Third, Grace Hopper's row has no email, so the email cell is simply empty and the row still has the right number of columns. Paste that block into A1 of any sheet and you get a clean three-column table with one blank cell, not a jumbled mess.

Now imagine one role were written as Engineer, ML. In CSV that comma would force quoting and risk a broken paste. In TSV the comma is just an ordinary character inside the cell — it changes nothing, because the delimiter is the tab.

Handling missing fields and awkward values

Heterogeneous data is the normal case, not the exception. Mixed API responses, merged exports, and records that gained fields over time all produce arrays where not every object has every key. The union header handles this cleanly: any row missing a column gets an empty cell there, so the table stays rectangular and sortable instead of shifting columns row to row.

Then there are the values that could genuinely break a paste — a field that itself contains a tab, a newline, or a quote. This is the one real edge case, and a good converter lets you choose the behavior. Escape mode wraps such a cell in double quotes and doubles any inner quotes, the convention spreadsheets understand for file imports. Replace mode swaps every embedded tab and newline for a single space so the row can never spill into extra cells or rows. When the destination is a live Sheets paste and you want zero surprises, replace mode is the safer pick; when you must preserve the original characters for a file import, use escape mode.

Nested objects and arrays get JSON-stringified into a single cell so nothing silently disappears. An object like {"city":"London"} lands as the literal text {"city":"London"}, and you can JSON.parse it back later. If you would rather flatten nested keys into dotted columns such as address.city, run the data through the JSON to CSV converter, which has a flatten toggle for exactly that.

How I actually use it

I reach for this most often during a five-minute fire drill. Someone in ops pings me asking for "that data, but in a sheet I can filter," and they do not run scripts. I hit the endpoint, copy the JSON array, drop it into the JSON to TSV converter, click copy, then click A1 in a fresh Google Sheet and paste. It is across columns before they finish typing their follow-up question. The first time a value had a comma in it and the table did not shear, I stopped exporting CSV for spreadsheet pastes entirely. The tab does the boring, reliable thing, and boring and reliable is exactly what you want when someone is waiting on you.

When to use TSV, CSV, or something else

A quick decision guide:

• Pasting into Excel or Google Sheets right now? Use TSV. It pastes across columns with no delimiter prompt and no comma escaping.
• Handing a .csv file to a program or import screen that asks for CSV? Use CSV, and accept the quoting rules that come with it.
• Need nested keys flattened into their own columns? Use the JSON to CSV tool's flatten option.
• Sharing with a non-technical teammate who lives in a workbook? Convert to TSV, paste into a sheet, save, and send the file.

TSV is not exotic or clever. It is the format spreadsheets quietly prefer when you paste, and JSON-to-TSV conversion is just the small bridge that gets your records there without a fight. Build the header from the union of keys, separate everything with tabs, leave missing fields blank, and decide once how to treat the rare value that contains a tab. Do that and the paste works the first time, every time.

---

Made by Toolora · Updated 2026-06-13