Is a semicolon-separated file still a CSV?

By common usage, yes. The extension and the mental model stay the same, and every serious parser lets you set the separator. RFC 4180 only describes the comma variant, so strictly speaking a semicolon file is outside the spec, which is why some tools call the general case DSV, delimiter separated values.

Which delimiter is safest?

Tab, for machine to machine transfer, because tab characters almost never appear inside a data value. Its weakness is that a tab is invisible, so a human editing the file by hand can destroy a row without seeing it happen.

What is the sep= line I see at the top of some files?

A Microsoft convention. A first line reading sep=; tells Excel the separator regardless of regional settings. Excel consumes it. Most other parsers treat it as a one-column data row, so it helps in exactly one place and hurts everywhere else.

Why did my file detect as pipe delimited when it is commas?

Sniffers score candidate delimiters by how consistently they split rows into the same number of fields. A file with commas inside many quoted values and one stray pipe per row can score the pipe higher. Sniffing is a heuristic, and pinning the delimiter beats arguing with it.

Can a delimiter be more than one character?

Some tools support multi-character separators like double pipe. It is portable to almost nothing, so treat it as a last resort for a specific internal pipeline rather than as a format you hand to someone else.

Guide September 3, 2026 by Arif Aslam 7 min read

CSV delimiters: comma, semicolon, tab, pipe, and how to choose

The C in CSV stands for comma, and a startling share of the files people call CSVs are not comma separated. That is not sloppiness. It is a rational response to the fact that half the world writes one and a half as 1,5.

This guide covers the four separators you will actually meet, why sniffers get it wrong, and what to pick when you are the one producing the file.

On this page: comma · semicolon · tab · pipe and the exotics · how sniffing works and fails · check what is really in the file

Comma: the default, and its one real weakness

A comma is what RFC 4180 describes and what most English-locale tools produce. Its weakness is that commas are extremely common inside data: Smith, John, Berlin, Germany, 1,234.56. That is handled by quoting, which works, but every hop through a badly written writer is a chance for the quoting to be dropped.

This is delimiter collision, and it is the single most common way CSVs break. A value containing the separator must be quoted, and a quote inside a quoted value must be doubled:

id,name,note
1,"Smith, John","said ""fine"" and left"
2,Jane Doe,ok

Use a comma when the destination is unknown, when the file will be read by many different tools, or when it is going to a US or UK locale. It is the safest default even with its weakness, because it is the one every parser handles without configuration.

Fix it: the quoting guide, including how to spot broken escaping →

Semicolon: what Europe actually exports

In locales that use a comma as the decimal separator, which is most of continental Europe and much of South America, a comma-separated file of numbers is unreadable. So Excel in those locales writes semicolons, and expects semicolons on import, and there is no marker in the file to tell you that happened.

The practical consequence is a specific, very common failure: someone in Munich exports a report, sends it to someone in Boston, and it opens as one column. Nothing is corrupt. Two machines simply have different list separators.

Semicolon files often travel with a second surprise, a comma decimal separator inside the numbers:

artikel;menge;preis
Schrauben;12;1,50
Muttern;40;0,35

A parser that splits correctly on semicolons but then reads 1,50 as text gives you a price column you cannot sum. Both settings have to move together. DuckDB, which is our engine, takes a decimal_separator option for exactly this, and our parse-options dialog exposes it.

Fix it: convert a semicolon export to comma separated →

Tab: the machine-to-machine choice

Tab separated values, usually with a .tsv or .tab extension, are the closest thing to a safe default for automated transfer. Tabs essentially never occur inside real data values, so collisions are rare and quoting is often unnecessary. Genomics, bioinformatics and a lot of scientific tooling default to TSV for this reason.

The catch is invisibility. A tab and a run of spaces look identical in most editors. A well-meaning person who lines up a column by hand has just destroyed a row, and neither of you will see it in the file. Copy and paste between applications is another hazard: some pasteboards convert tabs to spaces.

Use tab when the file goes machine to machine, when values commonly contain commas and semicolons, and when nobody will hand-edit it.

Fix it: convert tab separated or fixed width text into CSV →

Pipe and the exotics

The pipe, |, is common in banking, telecoms and older enterprise exports. It is a reasonable choice: it is visible, it is rare in data, and it does not collide with decimal separators. Its downside is that fewer tools default to it, so it usually has to be specified.

Below that you get into genuinely obscure territory. ASCII defines a unit separator at 0x1F and a record separator at 0x1E, designed for exactly this job in 1963 and used by almost nobody. Caret and tilde show up in legacy healthcare and insurance formats. All of these work, and all of them will need a configuration step at every hop.

My rule: pick the least exotic delimiter that does not collide with your data. Exotic separators buy you safety at the price of a support conversation with every recipient.

Fix it: confirm which delimiter a file really uses →

How delimiter sniffing works, and how it fails

Automatic detection is a scoring exercise. The sniffer takes a sample from the head of the file, tries each candidate separator, splits the sampled lines, and asks which candidate produces the most consistent field count across rows. The most consistent winner is the delimiter. Most sniffers also consider quoting rules and the plausibility of the resulting column types.

This works well and fails in three specific situations, all worth recognizing.

A preamble. Bank and brokerage exports put an address block, an account summary and a disclaimer above the actual table. The sniffer samples that prose, finds no consistent structure, and settles on a one-column reading of the whole file.
Ragged rows. If field counts genuinely vary, no candidate scores well, and the sniffer picks something close to arbitrary while reporting success.
A tiny sample. Detection reads a fixed number of bytes. If the first rows are unrepresentative, the whole file is parsed on a bad guess.

We got bitten by the first one often enough to write a fix. Before DuckDB's sniffer sees a CSV, our loader scans up to the first 5,000 lines of files under 8 MB looking for the region that actually holds a delimited table, and trims the prose above and below it. Then it hands the trimmed bytes to the parser. The user gets a notice saying how many lines were skipped and can turn the whole thing off if the guess was wrong.

There is a second layer behind that, because a sniffer that loses does not raise its hand. A file with ragged columns can come back as a single VARCHAR column holding whole lines, or as one enormous row with the newlines swallowed. Both parse without error and both are useless, so the loader checks for those two shapes explicitly and retries with more forgiving options rather than showing you nonsense.

Fix it: load a file with an awkward preamble and see the trim notice →

Check what is really in the file

Detecting a delimiter by eye takes ten seconds and settles most arguments.

head -3 yourfile.csv

Read the header line. Whatever is sitting between the column names is your delimiter. If the first line is not a header but a title or an address, that is your real problem and no delimiter setting will fix it: you need to skip rows first.

To count how often each candidate appears in the first line:

head -1 yourfile.csv | tr -cd ',' | wc -c
head -1 yourfile.csv | tr -cd ';' | wc -c
head -1 yourfile.csv | tr -cd '\t' | wc -c

The winner should be one less than your column count. If two candidates score similarly, you have a file with real collision risk, and pinning the delimiter explicitly at every hop is the only reliable answer.

Fix it: re-write the file with the delimiter your destination wants →