Convert CSV to TSV

A large share of the files that arrive with a .csv extension are separated by semicolons. Excel writes them that way in every locale where the comma is the decimal separator, which is most of continental Europe and much of South America, because a file using the comma for both jobs would be unreadable. Pipe-delimited exports turn up too, mostly out of older banking and telecoms systems.

The parser here works out which character it is by looking at the file rather than at its name, so a semicolon export converts without being told anything. Where that fails is a single-column file: with no separator present anywhere, every candidate is equally absent and the detection has nothing to go on. That is what the separator control on this page is for, and it is the only situation in which you need it.

A comma is a bad delimiter for the specific reason that it appears constantly in real data. Addresses have commas. Product descriptions have commas. Company names ending in ", Inc." have commas. Every one of those fields has to be wrapped in quotes, and every consumer of the file has to implement the quoting convention correctly — including doubled quotes inside a quoted field, and newlines inside one — before it can find the column boundaries again.

Tabs are almost absent from the kind of data people put in tables, so the delimiter and the content stop competing. After this conversion the field "Berlin, Germany" appears in the file exactly like that, with no quotation marks on the line at all, and anything that splits on a tab recovers it. That is the whole argument, and it is a reliability argument rather than a size or a speed one.

The control offers automatic detection, comma, semicolon, tab and pipe. Automatic is right nearly always, because a file with several columns gives the detector plenty of evidence and it is good at reading it.

Name the separator explicitly in two cases. The first is a single-column file, where there is nothing to detect. The second is a file where one field is stuffed with the wrong character — a notes column full of semicolons in a comma-delimited export — which can leave the detector choosing the character that appears more often rather than the one that means something. If the preview of the result has one column where you expected six, or six where you expected one, that is the setting to change first.

Tabs do occur inside CSV fields, usually because somebody pasted from a web page or another spreadsheet into a cell. In the source file that is harmless: the delimiter is a comma, so the tab is an ordinary character. In the output it is not, and the converter deals with it by wrapping that field in double quotes.

The result is correct and a full parser handles it. The problem is that TSV is chosen precisely by people who are not going to use a full parser — `cut -f` counts tab characters and has never heard of a quotation mark, so from that row onward it sees one field too many and every column after it is off by one. If a pipeline is producing misaligned output on a handful of rows out of thousands, this is nearly always the cause. Strip tabs out of the source before converting rather than patching it downstream.

A semicolon-delimited European export usually writes 1234,56 where an English-locale file writes 1234.56. The conversion does not translate that, and it should not: it has no way of knowing whether 1.234 means one and a bit or one thousand two hundred and thirty-four, and guessing would corrupt every price in the file.

What happens instead is that the value stays exactly as written, and because it does not look like a number to the parser, it stays text. So a European price column comes through the TSV intact and unharmed, and whatever reads the file next has to be told the decimal convention. In R that is `dec = ","` on the read; in pandas it is `decimal=","`. Setting it there is safer than doing a find-and-replace on the file, which will also rewrite the thousands separators and any commas left in the text columns.

The conversion is not a byte-level substitution of one separator for another. The file is parsed into rows and written out again, and on the way through, values that look numeric are read as numbers. `1e5` becomes 100000. `true` and `false` become booleans and are written back as the lowercase words. An empty field becomes a null and is written back as an empty field, which is the one case where nothing visible changes.

The casualty is the leading zero. A postcode column holding 01234 arrives in the TSV as 1234, and a zero-padded part number loses its padding silently. The test for whether a column is at risk is whether adding two of its values together would mean anything — postcodes, phone numbers, account references and part numbers all fail it. If your file has such a column, look at it in the output before you load the result anywhere.

Fields are quoted in the TSV only when they have to be: when they contain a tab, a double quote or a line break. Everything else is written bare. A CSV that arrived with quotes around every field — some exporters do that unconditionally — comes out with almost none, which is a substantial size reduction on a wide file and a large readability gain when you open it.

A double quote inside a field that needs quoting is doubled, the same convention CSV uses. That is worth knowing if the file is going somewhere that splits naively, because a free-text column containing quotation marks is the other thing that produces a quoted field in a format where nobody expects one.

Rows are separated by a single newline rather than a carriage return and newline pair, and the text is UTF-8 with no byte order mark. That is what a Unix pipeline, a Git diff, R and pandas all expect, and it is what most Windows tools now accept as well.

The absence of a byte order mark is worth naming because it has one consequence: double-clicking the file to open it in Excel on Windows will show accented characters as mojibake, since Excel falls back to the system code page when there is no mark to tell it otherwise. Opening the file through Data, Get Data, From Text/CSV and choosing UTF-8 avoids it. If the destination is Excel rather than a script, converting to XLSX instead removes the question entirely.

Both formats are plain text and both readers are plain JavaScript, so the conversion runs entirely on this page. No request carries the file anywhere, there is no queue and there is no tier — which matters because a CSV is very often a customer list, an order export or a system dump that would be awkward to explain uploading.

The limit is memory rather than policy. The file is read into rows and the whole table exists at once before anything is written, so tens of megabytes is routine and hundreds is where a browser tab starts to struggle. Past that, a streaming parser in a script is the right instrument, and saying so is better than failing halfway through a very large export.

If the destination is an import screen that says CSV, leave it as a CSV. A surprising number of them mean the word literally and will reject a tab-delimited file however sensible the choice is, and arguing with an upload form is not a fight worth having.

Tab-separated output earns its place when something is going to split the file on a character rather than parse it: a shell pipeline, an R script, a paste into a program with a tab-delimited import, a quick look with `cut`. If instead the values need to keep their types across the journey, no delimited format can help — a text file cannot tell the number 7 from the string "7" — and JSON, NDJSON or Parquet is the honest destination.