Convert TSV to JSON

Where tab separated data comes from

TSV turns up wherever text may contain commas. Cells copied from Excel or Google Sheets reach the clipboard as tab separated text, so a selection from a spreadsheet can go straight into the input box. Database tools, command line programs and statistics software often export tab separated tables for the same reason: a tab almost never appears inside a name, an address or a sentence, so the file needs little or no quoting.

Because a tab is so rarely part of the data, this page always splits at tabs and does not try to guess a delimiter. If the text turns out to have commas or semicolons instead, a notice offers to switch the input format to CSV.

How cells become JSON values

A TSV has no types, so the converter decides one column at a time. A column becomes numbers only when every filled cell in it is a clean number: digits with an optional minus sign and decimal point, no leading zeros and at most 15 digits. A column of true and false becomes booleans, and empty cells become null. Anything else stays text, and one stray word keeps a whole column as text, so a column never mixes numbers and words.

• A column of 19.99, 5 and -0.5 becomes the numbers 19.99, 5 and -0.5, and 3.50 is written as 3.5.
• Product codes such as 007 and ZIP codes such as 02134 stay text, so they keep their zeros.
• An ID such as 12345678901234567890 stays text, since a JSON number is only exact up to about 15 digits.
• Values such as 1,5 and 1.234,50 stay text, because the same characters are a different number in different countries.
• \N, the empty value marker of PostgreSQL and MySQL text exports, stays the two characters \N. The words null and NULL do become null when all types are detected.

Choose Keep as text if you need every cell exactly as written.

Quotes, backslashes and uneven rows

Quotes are read the CSV way: a cell wrapped in double quotes may hold tabs and line breaks, and two quote marks in a row stand for one. A quote mark inside an unquoted cell, as in 5" pipe, is kept as it is. Backslash codes such as \t and \n are not decoded, because in many files a backslash is just a backslash.

Rows shorter than the header are filled with empty cells, so those keys are null. A row with extra values gets extra keys called column4, column5 and so on. A repeated header such as name, name becomes name and name_2, and rows with no content are skipped. Every repair is listed in the notes under the result.

Getting the JSON shape you want

You shape the JSON with the header cells of your spreadsheet. Headers called name, address.city, address.zip, tags.0 and tags.1 produce {"name": ..., "address": {"city": ..., "zip": ...}, "tags": [..., ...]} for every row. Empty items at the end of a list are dropped, and Leave out empty cells under More options stops an empty cell from creating a key.

Turn off Rebuild nested values from column names to keep the headers as flat keys. Choose A list under Write each row as to get one JSON list per row, with the column names as the first list. That is much smaller for big tables, because the keys are not repeated on every row.