Convert TSV to JSON

Tools reach for the tab when their data is full of commas and quoting is not wanted. Database clients print result sets with tabs. Genome browsers, sequence annotation files and expression tables use them by convention. Ad platforms and analytics consoles export them because campaign names and page titles are full of punctuation.

That choice buys reliability against commas and gives up almost everything else. The file has no types, no nesting, no way to mark a column as an identifier, and no defence at all against a tab appearing inside a value. It is a good transport format and a poor thing to compute against, which is why the first thing anyone does with one is convert it.

The header row supplies the keys, and every following row becomes one object. The result is a JSON array — the shape a script, a notebook, a JavaScript fetch and nearly every library assume when they say "the data".

Nothing is wrapped around it and nothing is keyed by an ID column, because doing either requires knowing which column is the identifier and that is not a decision a converter can make for you. An array assumes least, and turning it into a lookup keyed by any field is one line in whatever language you are already writing.

A tab-separated file has no accepted escape for a tab inside a field, and a line break inside one is worse: the row ends where the value was supposed to continue. Both happen in practice, usually in a notes, description or annotation column that somebody pasted into.

In JSON they are ordinary characters. A tab is written as \t inside the string and a newline as \n, and neither can affect the structure of the document, because the structure is expressed by braces and brackets rather than by the characters in the data. This is the strongest single reason to convert a messy tab-separated file rather than continue processing it as text, and it is worth doing before the file is passed to anything else rather than after a pipeline has already produced a plausible wrong answer.

A heading of `Gene symbol` becomes the key `Gene symbol`, spaces and all, and `Cost (USD)` keeps its brackets. Nothing is renamed, lower-cased or turned into snake_case, because a converter that silently rewrote your column names would make the output impossible to reconcile with the source.

That has consequences where you use it. `row.Gene symbol` is not valid in most languages and you will be writing `row["Gene symbol"]` instead. If the JSON is going into code that will be maintained, renaming the keys once — in the header row before converting, or in a single pass afterwards — is worth doing deliberately. Two columns sharing a heading is the case to watch for: object keys are unique, so the later one wins and the earlier is lost without a warning.

JSON distinguishes 7 from "7" and a tab-separated file does not, so the parser infers. Numbers become numbers, `true` and `false` become booleans, and everything else stays a string. For quantities, counts, scores and prices that is exactly right and saves a conversion pass at the other end.

For identifiers it is destruction. A value of 00123 becomes 123, an accession or part number written in scientific-looking notation becomes the number it resembles, and once the JSON holds a number there is nothing left to recover the original text from. The rule of thumb is whether adding two values in the column would mean anything; if it would not, the column is an identifier and it wanted to stay a string. Check those columns in the output before building anything on top of it.

The conversion is often the first step in reading a file nobody documented, and JSON makes that quick. `jq "length"` gives the row count without the header confusing it. `jq ".[0] | keys"` lists the columns as the parser saw them. `jq "[.[].status] | unique"` shows every distinct value in a column, which is how you find the sentinel that has been quietly ruining a numeric field.

That last one is worth doing on any column you expected to be numeric and that came back as strings. The reason is almost always a handful of rows carrying `NA`, `-`, `n/a` or a footnote, and knowing which marker the source used is more useful than any amount of guessing at the schema. It is also considerably faster than opening a large tab-separated file in a spreadsheet, which is the alternative and which will apply its own conversions on the way in.

A gap between two tabs is written as `null`. That is a decision worth stating plainly, because anything consuming the JSON will treat null and "" differently: a schema declaring a string type will reject null, and a truthiness check in JavaScript treats both as falsy but a type check does not.

The source cannot tell you which was meant. A delimited file has one way to write "nothing here" and uses it for both a blank value and a field that does not apply. If that distinction matters, it has to exist in the file before the conversion — as a sentinel, or as a second column — and no converter can invent it afterwards.

The keys come from the header row and every later row is matched against it, so a table where an extra column starts appearing after ten thousand rows does not grow a key for it. The surplus values land together under `__parsed_extra`, as an array, on exactly the records that carry them. Rows missing a field do not carry that key at all.

This is more common than it should be in exports assembled from several runs, and it is the failure a positional reader handles worst — a naive splitter shifts every value after the missing column and produces rows that look right and are not. Having the file as objects makes the inconsistency visible: one `keys` call on a few records shows immediately that the shape is not uniform.

Expect the file to grow. Every record repeats every key, every string is quoted, and the punctuation of an object is added around the values. On a wide table with long column headings and short values the JSON can be several times the size of the tab-separated source.

That is a fair price for a file you are going to compute against and a poor one for a file you only need to store. The output is also pretty-printed with two-space indentation, which makes it readable in an editor and adds further bytes. If the destination is storage or a stream rather than a script, NDJSON or Parquet is the better target and the same table converts to either.

The parsing and the serialisation are both plain JavaScript loaded by this page, so nothing is uploaded, there is no queue and no account. That matters for the kind of file this is: a reference table under embargo, a customer extract, an unpublished result set.

The array is built in full before it is written, so memory is the ceiling. Tens of megabytes is routine and hundreds is where a tab starts to strain — and at that size a single JSON array is an awkward thing to work with anyway, since most parsers will insist on holding all of it. NDJSON is the shape that scales past this point, and it converts from the same source.