Convert TSV to NDJSON

Search indexes and document stores do not take delimited files. Elasticsearch and OpenSearch ingest through a bulk endpoint that reads newline-delimited JSON. MongoDB’s import tool reads one JSON document per line by default. BigQuery has a load format called NEWLINE_DELIMITED_JSON and will not accept anything shaped like an array. None of them will look at a tab-separated file.

That is not arbitrary. A document store holds documents, and a document has named fields with types, which a delimited row does not. The conversion is therefore a change of model as much as a change of syntax: each row stops being a position in a table and becomes a self-describing object that can be indexed, queried by field, and stored alongside documents that do not have the same fields at all.

The output is one complete JSON object per line and nothing else — no opening bracket, no commas between records, no closing bracket. That absence is the format. It lets a loader read a line, index it, discard it and move on, without ever holding the file.

It also means the file is not valid JSON as a whole, and a parser expecting an array will fail on the second line. That is not a defect; it is the property that lets a hundred-million-line file be loaded by a process with a small memory limit. If something downstream rejects it, the thing to check is whether that consumer wanted an array, in which case the source table should be converted to JSON instead.

The header row supplies the field names exactly as written. `Product title` stays `Product title`, spaces included, and `price (USD)` keeps its brackets. Nothing is renamed or normalised, because a converter that quietly rewrote your field names would make the loaded documents impossible to reconcile with the source.

Several destinations are fussier than JSON is. A dot in a field name is treated as a path separator by Elasticsearch and by MongoDB’s dot notation, so a column called `price.usd` produces a nested field rather than a flat one and a mapping conflict shortly after. BigQuery requires field names to start with a letter or underscore and to contain only letters, digits and underscores, and will reject the load outright otherwise. Fixing the header row in the source before converting is one edit; fixing a million documents after loading is not.

JSON has types and a tab-separated file does not, so the parser infers. Values that read as numbers become JSON numbers, `true` and `false` become booleans, an empty field becomes null, and everything else stays a string. For a catalogue that means prices and stock counts arrive numeric and are aggregatable without a mapping override, which is usually what you want.

The inference is wrong in the same place it is always wrong: identifiers. A SKU of `00123` becomes the number 123, an ISBN without hyphens becomes a large integer, and a code written like `1e5` becomes 100000. In a search index that is worse than usual, because a numeric field is not analysed and will not match a term query for the original string. Prefix or quote those columns in the export, or fix them in a `jq` pass over the converted file before loading.

The output is documents only. Elasticsearch and OpenSearch `_bulk` expect a metadata line before each document — a small object naming the operation and usually the index — so posting this file directly produces a parse error on the second line and a stack of confusing messages after it.

It is a single pass to add: `jq -c '{index:{}}, .'` over the converted file interleaves an action line before every document, and the endpoint takes it from there. Everything else on this page’s list needs no such step. `mongoimport --type json` reads the file as it is, a BigQuery load job with NEWLINE_DELIMITED_JSON reads it as it is, and a Logstash or ingest pipeline reading lines reads it as it is.

The columns being indexed are usually the awkward ones: product descriptions, article bodies, review text, annotation fields. In a tab-separated file those are precisely the values that break things — a pasted tab adds a column, a line break ends the row early, and the file’s row count stops agreeing with itself between tools.

In JSON they are ordinary string content. A tab is escaped as \t, a newline as \n, a quotation mark is escaped, and none of them can affect the structure of the document, because the structure lives in the punctuation of the object rather than in the characters of the data. For a search load that is the whole reason the conversion is safe: the text you index is the text that was in the field, not the text up to the first character that happened to be significant.

An empty field is written as `null` rather than as an empty string, and the keys come from the header row rather than from the rows below it: a line carrying more fields than the header does not invent names for the surplus, it puts them into one `__parsed_extra` array on that document alone.

Both of those interact with a mapping. Elasticsearch ignores a null for the purposes of inferring a field type, so a column that is null for the first several thousand documents and numeric afterwards can end up mapped from the first non-null value it happens to see — and if that value is a footnote rather than a number, every later document in that field fails to index. Defining the mapping explicitly before the load is the fix, and reading a sample of the converted file with `head -n 100` is how you find out which fields need it.

Repeating every field name on every line makes the file bigger, often substantially so on a wide table of short values. On a text-heavy catalogue the effect is muted, because the descriptions dominate the bytes and the field names are noise beside them.

It also matters less than it would elsewhere, because this file is a transport artefact rather than a store. It exists to be read once by a loader and then deleted; the durable copy is the index or the table it produced. Compression removes most of the difference in transit in any case, and every destination named here accepts a gzipped file.

Both the tab-separated reader and the JSON writer are plain JavaScript loaded by this page, so no request carries the file. A catalogue before launch, an unpublished dataset or an extract containing customer text is not copied to a third party in order to change its shape.

The ceiling is 100 MB a file, and a hundred files to a drop. The whole table is built in memory before anything is written, which puts tens of megabytes comfortably in range and a file near the cap at the point where a browser tab strains. For an export past 100 MB, a streaming reader in a script is the right instrument — and since the destination is a streaming load, that script is frequently the thing you were going to end up writing anyway.

BigQuery, Redshift and Snowflake all read delimited text directly and will load a tab-separated file faster than JSON documents, because they do not have to parse a field name on every row. If the destination is a warehouse table with a schema you already defined, the conversion is buying you nothing.

Parquet is the better answer again when the same data will be queried repeatedly rather than ingested once, since it stores the field names once in a footer and the values in typed columns. NDJSON earns its place where the destination is genuinely document-shaped — a search index, a document store, an ingest pipeline reading lines — which is exactly the case this page is for.