Semicolon, Tab, Pipe: Choosing a Delimiter That Survives the Trip

One character cannot do two jobs

The whole subject reduces to a collision. A CSV file needs one character to mark the boundary between fields. A written number needs one character to mark the boundary between the whole part and the fraction. In English-language conventions those are the comma and the point, and there is no conflict. In French, German, Spanish, Italian and Portuguese conventions the decimal mark is the comma, and the conflict is total: the character that separates twelve from fifty inside a price is the same character that would separate the price from the next column.

There are only two ways out of a collision like that, and the industry took the second one. The first is to keep the comma as the field separator and quote every value that contains one, which is legal and which the format explicitly provides for. The second is to change the separator. Operating systems in those regions carry a list separator setting alongside the decimal separator, and it is set to the semicolon precisely so the two never meet; spreadsheets read it when they save and open a CSV. The result is that a file exported by a colleague in Lyon and a file exported by a colleague in Chicago are different formats with the same extension, and each opens as one column of text on the other machine.

The same export, five ways, counted

Here is a small French export, four columns wide: Produit, Prix, Remise, Ville. Two rows: Café at 12,50 with a 5,0% discount in Lyon, Rhône, and Thé at 8,90 with 0,0% in Paris. It contains everything that makes this question real — decimal commas, a percentage written with a comma, and a city name that legitimately contains a comma and a space.

Run it through the delimiter converter five times and count the cells that come back wrapped in quotes. With semicolons, tabs or pipes: zero. Not one cell in the file needs protecting, because none of the values contains any of those three characters. With commas: five. Café's price, Café's discount, Café's city, Thé's price and Thé's discount all have to be quoted, and the file goes from clean to littered. With spaces: one cell is quoted, the city, and the rest of the file becomes a trap for anything that treats runs of whitespace as one boundary.

Watch what the converter does to the quoting, because this is the part hand-written scripts get wrong. Going from semicolons to commas, it adds quotes around 12,50 and 5,0% — values that were perfectly safe before and are not any more. Going from commas to semicolons, it removes the quotes around Lyon, Rhône, because a comma inside a field is no longer a boundary and the protection is now noise. A script that swaps the characters with a search and replace does neither: it rewrites the comma inside Lyon, Rhône into a semicolon, splitting the city in two, and it leaves 12,50 unquoted so the next reader finds an extra column. Parsing and re-emitting is the only correct implementation, and the visible sign that a tool does it is that the quoting changes when the delimiter does.

Tab: safest on paper, fragile in transit

The argument for the tab is that no number format contains one, in any locale. Neither does any postal address, product name or person's name in ordinary use, so a tab-separated file of real business data usually needs no quoting at all — which is exactly what the five-way count above showed. It is also the format a spreadsheet itself puts on the clipboard: copy a block of cells and what lands there as plain text is tab-separated, which is why pasting a selection into a text editor hands you a ready-made TSV. Command-line tools lean the same way, cut being the obvious one — it splits on the tab unless told otherwise. On the merits of the character, it wins.

It loses on transport. A tab is a control character with a job in every text-entry interface it passes through, and that job is not to be data. Typed into a browser field it moves the focus to the next control. Pasted into a chat message or an issue tracker it is often rendered as a run of spaces and sometimes converted into them. Many editors are configured to expand a typed tab into spaces, so a person who opens the file to fix one cell can destroy the structure of every line they touch without seeing anything change on screen. And because a tab looks like a stretch of blank, a tab that has become spaces is invisible until a parser tells you the row has one field.

One detail about how this converter writes tab-separated output is worth knowing, because it surprises people who expect a raw TSV. It applies CSV quoting rules to the tab format: a cell that contains a tab, a line break or a double quote is wrapped in quotes and its internal quotes are doubled. So a cell holding say "hi" comes out as "say ""hi""" even though there is no tab in it. Spreadsheets read that correctly. Tools that split each line on the tab character and take the pieces literally do not — they see the quotes as part of the value. If your consumer is a simple field splitter rather than a CSV reader, check what it does with a quoted cell before you rely on the file.

The sep= line, and the thing this converter gets right by accident

Excel accepts a first line of the form sep=; and reads it as an instruction: the character after the equals sign is the field separator for this file, whatever the machine's own list separator setting says. It is the only mechanism in common use that lets a CSV file declare its own delimiter, and it is not part of RFC 4180 or of the text/csv media type registration — it is a vendor convention that spread because it solves a real problem and costs one line.

That last part is the catch. Nothing that has not been taught the convention will recognise it, and there is no way for the file to signal that the line is metadata rather than data. A generic CSV reader treats it as the first record, and if the reader is also using the first row as the header, your column names become sep= and a set of positional placeholders while your real header row slides down into the data. That is exactly what happens if you feed such a file to a CSV to JSON converter without stripping the line first.

The delimiter converter handles it correctly, and the reason is worth telling because nobody designed it. The tool has never heard of the convention: it parses sep=; as an ordinary record, which with the semicolon as the delimiter yields two cells, sep= and an empty one, and then writes that record back with the new delimiter. Converting to commas produces sep=, converting to tabs produces sep= followed by a tab, and converting to pipes produces sep=|. In every direction the hint stays true to the file it labels. The same accident helps the detection: the hint line contains exactly one occurrence of the character it names and none of the other three candidates, and only characters seen on the first record are ever considered — so a file carrying the hint offers exactly one candidate, and it is the character the line names.

Auto-detection weighs five records, and consistency beats frequency

Detection here counts the comma, semicolon, tab and pipe, skipping anything inside quotes, and it does so record by record — up to five records, never the whole file. A candidate whose count repeats identically on every record read is given a large bonus, so consistency outweighs sheer frequency on the header line. Feed it a header of a,b,c|d above rows of 1|2 and 3|4 and the answer is the pipe, even though that header holds two commas against a single pipe: the pipe count repeats on every record, while the comma count collapses to zero on the second. Consistency is the better signal, because a genuine column boundary occurs the same number of times in every row of a well-formed file, whereas a character that merely happens to sit in a heading occurs where it happens to occur.

Two limits survive that rule, and both are worth knowing before you leave the setting on automatic. The first: only characters that appear on the first record are candidates at all. A header of a,b above a row of 1;2;3;4 still returns the comma, because the semicolon never appears on the line that draws up the shortlist — the rows below cannot promote a character the header never showed. The second: five records is a window, not a proof. Give it a header of a,b,c|d over four rows of the shape 1|2 and it answers pipe; append a sixth record reading 9|10|11, which breaks the pattern, and the answer is still pipe, because that record is never read. Move that same row up into fifth place and the answer flips to the comma. The symptom of a wrong guess is the same either way — rows come back as one wide cell with the real separator still sitting inside it — and the fix is one click: set the source delimiter explicitly instead of leaving it on automatic. A file with no delimiter at all falls back to the comma, which changes nothing and is the right default.

Two last practical notes. The space option exists in the tool and should almost never be chosen: any field containing a space gets quoted, which in real data is most of them, and no reader treats a run of spaces the way you expect. And the conversion is reversible in both directions on well-formed input — take a comma file whose city is quoted as Paris, France, convert it to semicolons and back, and you get the original file character for character, because the quoting is recomputed each way rather than carried along.

Frequently asked questions

My colleague opens my CSV and everything is in column A. What do I send instead?

Their machine expects a different field separator from the one you used, and nine times out of ten that means you sent commas to someone whose list separator is the semicolon. Convert the file to semicolons and send it again. If you do not know which they need, or if the file goes to several people at once, there are two robust options: add a first line reading sep= followed by your delimiter, which Excel honours and which makes the file self-describing, or send a real spreadsheet file instead of a CSV, since that format records its own structure and has no separator to guess. Note that the sep= line only helps a reader that knows the convention — a script that reads the file will treat it as your header row.

Is a comma-separated file with quoted numbers still correct?

Yes, entirely. Quoting a field that contains the delimiter is what the format is for, and a correctly quoted file parses correctly everywhere. The objections are practical rather than formal. It is much harder to read by eye, so a person scanning the file for a mistake has to work through the quotes. It is larger, by two characters per quoted value plus a doubled quote for each internal one. And it is fragile in the hands of whoever edits it next: a person who opens the file in a text editor and removes what looks like a stray quote around a price has just corrupted every row below the point where the columns shifted. Choosing a delimiter that does not appear in the data removes the need for quoting entirely, and a file that never needs quoting cannot be broken by someone tidying it up.

Should I use tab or pipe when I control both ends?

Tab, if the file only ever moves between programs, and pipe if a human will look at it or move it around. Both are equally safe against the data — neither appears in a number, an address or a name in ordinary use — so the difference is entirely about handling. Tab wins on tooling: spreadsheets and command-line utilities open a tab-separated file without an import dialogue. Pipe wins on visibility and on survival: it is visible in a terminal, it is unambiguous in a screenshot, and it goes through a chat message, a form field and a text editor unchanged, none of which is true of a tab. If the file is going to be pasted anywhere at any point in its life, pick the pipe.

Why does the converter add quotes when I switch from semicolons to commas?

Because those cells contain a comma and the comma has just become a boundary. A price written 12,50 was an ordinary value in a semicolon file and would be read as two columns in a comma file, so it has to be wrapped. The same happens to a percentage written 5,0% and to any address containing a comma. It is not the converter being cautious — it is the minimum needed to keep the file meaning what it meant. The reverse operation removes quotes for the same reason: convert back to semicolons and the quotes around a value whose only special character was a comma disappear, because that character no longer separates anything. If you would rather not carry the quotes, that is the argument for converting to tabs or pipes instead, where nothing in the data needs protecting at all.

Does the decimal separator itself get converted?

No, and it should not be. The delimiter converter changes the structure of the file and never the contents of a cell: a price written 12,50 stays 12,50 whichever delimiter you choose, and a price written 12.50 stays 12.50. Converting the numbers as well would be a different and much more dangerous operation, because it would require deciding, cell by cell, whether a comma is a decimal mark or a thousands separator — and 1,500 is either one and a half or fifteen hundred depending on the answer. If you need the numbers reformatted for a reader in another convention, do that as a separate, deliberate step where you can see which columns are affected, and check a value you know the answer to before you accept the result.