Convert NDJSON to TSV

Look at what an event record actually contains. A request path with a query string. A user agent, which is nothing but commas and parentheses. An error message quoting a value. A postal address. Send that to CSV and a large share of every row is wrapped in quotation marks, because the delimiter appears inside the data on nearly every line.

Tabs almost never appear inside those values, so most fields are written bare and the file stays positional — column three really is between the second and third tab characters. That is the property the rest of this page is about, and it is also the property that a handful of fields can quietly take away from you.

It is worth being exact about what the writer does, because the whole appeal of a tab-separated file is that the tools reading it can be crude. A value containing a tab is wrapped in double quotation marks. A value containing a double quotation mark is wrapped, with the internal quotes doubled. A value containing a newline is wrapped. Everything else, commas included, is written as it is.

That is the RFC 4180 convention with a tab in place of the comma, and it is not the IANA definition of tab-separated values, which forbids tabs inside fields rather than escaping them. The practical consequence is that `cut -f4` and `awk -F$'\t'` are correct until a log message contains a tab, at which point that row gains a field and every column after the fourth shifts by one, on that row only. Nothing errors. If your fields can carry pasted text — and log messages can — validate before you trust a positional read, or use a reader that understands quoting.

The columns are the union of every key seen anywhere in the file, in the order each key first appeared. That is the correct answer for a source where nothing obliges the second line to carry the keys of the first, and it is the single most important thing to understand before automating this conversion.

It means the column order is a property of the data rather than of the format. A nightly export in which the first error record happens to arrive at line 12 today and line 40,000 tomorrow produces two files whose columns are in different orders. The header row is right in both. A pipeline that reads by name is fine; a pipeline that reads by position is wrong on the second day and gives no sign of it.

The fix is to stop letting the file decide. Project the records to a fixed key set before converting — `jq -c '{ts, level, service, msg}'` gives every line the same four keys in the same order, and the conversion then has only one possible column layout no matter what the source contained.

That also solves the wide-and-sparse problem at the same time. A mixed-event export converted whole produces a column for every field any event type ever carried, most of them empty on most rows; projecting first gives you a narrow table with no empty cells. If you cannot project, at least assert: read the header line in the pipeline and fail loudly if it is not the one you expected, rather than letting `cut -f3` return the wrong field for a month.

Structured logs nest — a `request` object with a method and a path, a `user` object with an identifier, a `context` block with a trace id. Each leaf gets a column named for its path: `request.method`, `user.id`, `context.trace_id`. One column per leaf value, no exceptions.

A dot in a column name is harmless to `cut` and `awk`, which never look at the header, and awkward everywhere else: it needs quoting in SQL, and it is not a legal identifier in most loaders. If the TSV is going into a table, rename during the projection step rather than after the fact — `jq -c '{method: .request.method}'` produces the column name you want and saves an ALTER later.

PostgreSQL is the one that needs care, because its default `COPY` text format uses backslash escapes rather than quotation marks and will read a quoted field literally, quotation marks and all. The form that matches what was written is `\copy events FROM 'out.tsv' WITH (FORMAT csv, DELIMITER E'\t', HEADER true)` — CSV rules, tab delimiter.

DuckDB reads it with `read_csv('out.tsv', delim='\t', header=true)` and infers types from a sample, which is convenient and is also how a column of identifiers loses its leading zeros; pass an explicit `types` map for anything that must stay text. SQLite’s `.import --csv` needs the separator set with `.separator "\t"` first. In all three the empty field arrives as an empty string rather than NULL unless you say otherwise, which is the next section’s problem.

A JSON null and a JSON empty string both become nothing between two tabs, and there is no way to tell them apart afterwards. Neither is the writer being careless — a delimited text file has no third state to put them in.

Whether that matters depends on the question you are asking. Counting how many events lacked a `user_id` is a different query from counting how many carried an empty one, and after this conversion both give the same answer. If the distinction is load-bearing, encode it before converting — `jq -c '.user_id //= "«null»"'` is ugly and unambiguous — or send the export to Parquet instead, where null is a real state and the column keeps its type.

Usually less than it was. Every line of the source repeats its key names; the TSV writes them once in the header and then only the values, so a narrow record set often shrinks noticeably. A wide, sparse one goes the other way, because every row has to carry a tab for every column including the ones it has nothing in.

The free ceiling is 100 MB per file and the whole export is read into records before the columns can be worked out, so memory rather than the limit is what you meet first. NDJSON splits safely at any line boundary, so `split -l 500000` gives files that each convert cleanly — but note that split files converted separately can disagree about column order for exactly the reason above, which is one more argument for projecting first.

If a person is going to open it, send it to CSV or XLSX instead — a spreadsheet handles a CSV without being told what the separator is, and a workbook keeps the identifier columns as text. If it is going into a warehouse or being kept, Parquet is smaller, typed and does not have a column-order problem at all.

TSV earns its place in exactly one situation: the destination is a program that splits on a character, and you want the file to be readable with `head` while you build the command. That is a real and common situation. It is not the same one as "I need this in Excel", and choosing TSV for that reason is how people end up with a file their spreadsheet imports into one column.

Plain JavaScript in this tab. No upload, no engine download, no account, no daily allowance — and the network tab during a conversion is how to confirm that rather than taking this sentence for it.

Production event exports are the material this pair exists for, and they carry IP addresses, session identifiers, request paths and user agents. Feeding one to a hosted converter is a data transfer to a third party whatever the converter promises. Here there is no transfer to reason about.