Comma Semicolon Tab or Pipe and How to Pick a Delimiter

The delimiter in a CSV file is a choice, and the default choice is wrong more often than people expect. In 389 sentences I took from three RFCs, 48.6% contained a comma. A comma delimited file of ordinary prose needs quotes on about every second row, and every one of those quotes is a chance for a writer or a reader to get it wrong. The format guide lists the delimiter first among the things the format leaves to agreement, so this article is about how to make that agreement on purpose.

What actually differs between the four

The four common delimiters differ on one axis that matters, which is how often the character shows up inside the data itself. Whenever it does, the writer has to wrap the field in quotes and the reader has to unwrap it, and that is the entire cost of getting it wrong.

The last column is a habit, not a rule. Nothing prevents a pipe delimited file from carrying code with pipes in it, and nothing forces European software to use a semicolon. The point is that each one has a community of tools that expect it.

The measurement

To put a number on "constantly", I took the plain text of RFC 4180, RFC 7111 and RFC 8259, split it into sentences between 20 and 300 characters, and counted how many contain each candidate character. It is a small, formal corpus, so treat the figures as a floor for the comma and a rough guide for the others, since real free text from a form field is messier than a specification.

python - <<'PY'
import re
text = " ".join(open(f"{n}.txt").read() for n in ("rfc4180", "rfc7111", "rfc8259"))
text = re.sub(r"\s+", " ", text)
sentences = [s for s in re.split(r"(?<=[.!?]) ", text) if 20 <= len(s) <= 300]
print(f"{len(sentences)} sentences taken from three RFCs")
for name, ch in [("comma", ","), ("semicolon", ";"), ("pipe", "|"), ("tab", "\t"), ("double quote", '"')]:
    n = sum(ch in s for s in sentences)
    print(f"{name:13} {n:4} sentences  ({n / len(sentences):6.1%})  need quoting if it is the delimiter")
PY

389 sentences taken from three RFCs
comma          189 sentences  ( 48.6%)  need quoting if it is the delimiter
semicolon       13 sentences  (  3.3%)  need quoting if it is the delimiter
pipe             0 sentences  (  0.0%)  need quoting if it is the delimiter
tab              0 sentences  (  0.0%)  need quoting if it is the delimiter
double quote    73 sentences  ( 18.8%)  need quoting if it is the delimiter

About half of the sentences contain a comma. Nearly one in five (18.8%) contains a double quote, so a quote can never be a delimiter and a quote inside a quoted field is common. A semicolon is rare at 3.3%, though real fields with lists in them, like tags or address lines, push it up. No sentence contained a pipe or a tab, which was predictable and also the reason they are popular. The corpus cannot tell you how often a tab shows up when somebody pastes from a spreadsheet cell into a form, because that never happened in a specification.

The practical reading is simple. If your fields hold identifiers, codes and numbers, any delimiter works and the comma is fine. If any field holds free text, a comma delimited file will be mostly quoted fields, and a tab or pipe delimited file mostly will not.

The decimal comma problem

The more important reason for semicolons has nothing to do with text. In a locale where one and a half is written 1,5, a spreadsheet cannot use a comma as a field separator without breaking every number, so it uses a semicolon. The Microsoft documentation for importing and exporting text files describes the list separator setting that Excel uses when it saves a .csv, and notes that changing the decimal separator to a comma makes Excel use a semicolon.

That explains the file below. It is the kind of export a German accounting package writes.

sku;name;price;qty
A-100;Kabel, 2 m;1.234,56;3
A-101;Stecker;9,90;10

Read with defaults, pandas splits on the commas it can see. The header has one field and the rows have more, so it quietly promotes the leading fields into the index and leaves a single column that holds 56;3:

python -c "
import pandas as pd
df = pd.read_csv('prices-eu.csv')
print(df.shape); print(df.iloc[0].to_dict())"

(2, 1)
{'sku;name;price;qty': '56;3'}

That is the failure mode of assuming a comma. The parse succeeded, no error was raised, and the shape is wrong. Tell read_csv about the delimiter, the decimal separator and the thousands separator together and the dtypes come out right, with price a float and qty an integer:

python -c "
import pandas as pd
df = pd.read_csv('prices-eu.csv', sep=';', decimal=',', thousands='.')
print(df.dtypes.to_string()); print(df)"

sku          str
name         str
price    float64
qty        int64
     sku        name    price  qty
0  A-100  Kabel, 2 m  1234.56    3
1  A-101     Stecker     9.90   10

DuckDB's read_csv has delim and decimal_separator options too, but a number that contains both a thousands dot and a decimal comma is not something its decimal option can read, so it stays text. A cast after cleaning the text does the job in SQL, and it keeps Kabel, 2 m intact, because with a semicolon as the delimiter the comma in a name needs no quotes at all:

duckdb -box -c "SELECT sku, name, CAST(replace(replace(price, '.', ''), ',', '.') AS DECIMAL(10,2)) AS price, qty FROM read_csv('prices-eu.csv', delim=';', all_varchar=true)"

┌───────┬────────────┬─────────┬─────┐
│  sku  │    name    │  price  │ qty │
├───────┼────────────┼─────────┼─────┤
│ A-100 │ Kabel, 2 m │ 1234.56 │ 3   │
│ A-101 │ Stecker    │ 9.90    │ 10  │
└───────┴────────────┴─────────┴─────┘

If you receive files like this regularly, the delimiter and the decimal separator travel together in your documentation: "semicolon, decimal comma, dot for thousands". Forgetting the second half is how a revenue figure ends up a thousand times too small.

A delimiter that never appears: the unit separator

ASCII reserves control characters for exactly this job. The unit separator, 0x1F, was designed to separate fields, and it will not show up in any text a person types. I tested it against five readers with a record that holds every ordinary delimiter plus a tab and a pair of quotes.

00000000: 6964 1f6e 6f74 651f 7174 790a 311f 6861  id.note.qty.1.ha
00000010: 732c 2063 6f6d 6d61 733b 2070 6970 6573  s, commas; pipes
00000020: 207c 2074 6162 7309 6865 7265 2022 7175   | tabs.here "qu

The text fields are untouched in every reader. Python and pandas need delimiter="\x1f" and quoting disabled, and DuckDB needs delim=chr(31) with an empty quote:

[['id', 'note', 'qty'], ['1', 'has, commas; pipes | tabs\there "quotes"', '3'], ['2', 'plain', '5']]
[{'id': 1, 'note': 'has, commas; pipes | tabs\there "quotes"', 'qty': 3}, {'id': 2, 'note': 'plain', 'qty': 5}]

┌────┬───────────────────────────────────────────────┬─────┐
│ id │                     note                      │ qty │
├────┼───────────────────────────────────────────────┼─────┤
│ 1  │ has, commas; pipes | tabs	here "quotes" │ 3   │
│ 2  │ plain                                         │ 5   │
└────┴───────────────────────────────────────────────┴─────┘

PostgreSQL's COPY accepts any single-byte delimiter and quote character, so the pair E'\x1f' and E'\x01' gives it a format with no quoting in practice:

DROP TABLE
CREATE TABLE
COPY 2
 id |                     note                      | qty 
----+-----------------------------------------------+-----
  1 | has, commas; pipes | tabs       here "quotes" |   3
  2 | plain                                         |   5
(2 rows)

And the SQLite shell can import it after you set .separator:

╭────┬───────────────────────────────────────────────┬─────╮
│ id │                     note                      │ qty │
╞════╪═══════════════════════════════════════════════╪═════╡
│ 1  │ has, commas; pipes | tabs       here "quotes" │ 3   │
│ 2  │ plain                                         │ 5   │
╰────┴───────────────────────────────────────────────┴─────╯

It works everywhere I tried, and I still would not send it to anyone. I did not test spreadsheet software with it, and in many text editors the character is invisible or drawn as a box, which makes the file hard to eyeball. I use it for files that live inside one pipeline, between two programs I control, and nowhere a human might open them.

Pipes, and what happens to their quotes

Pipe delimited files often skip quoting entirely, which is fine until a field contains a quote. This is a small pipe file with a quote and a comma in a text field:

id|name|note
1|Ana|likes tabs, commas; and "quotes"
2|Bo|plain

DuckDB's sniffer picks the delimiter correctly, finds a header, and decides there is no quote character at all, since the double quotes in the data are not a consistent wrapper:

duckdb -box -c "SELECT Delimiter, Quote, HasHeader FROM sniff_csv('pipes.txt')"

┌───────────┬─────────┬───────────┐
│ Delimiter │  Quote  │ HasHeader │
├───────────┼─────────┼───────────┤
│ |         │ (empty) │ true      │
└───────────┴─────────┴───────────┘

That is the right call for this file, and it is the kind of judgment a sniffer can get wrong in the other direction. The more pipe files you accept, the more you want the quote setting written down next to the delimiter.

So which one

For a file that crosses a team or company boundary, I use a comma and quote everything that needs it, because every tool on earth can read that without configuration. For a file that moves between two programs I control, and where the fields hold free text, I use a tab when I can promise that no field contains one, and say so in the documentation. TSV compared with CSV covers when that promise is safe. I use a semicolon only when the consumer demands it, which means an accounting system or Excel in a decimal comma locale. I use a pipe when a partner's specification says pipe.

Whatever the choice, quote any field that contains the delimiter, a quote or a line break, even when your data does not have those today. A delimiter that never appears in the data you have seen will appear the first time somebody pastes from a document, and the writer should survive that without anyone noticing. The escaping article shows how to write that quoting correctly.

When you do not get to choose

If you are the reader, the delimiter is whatever the writer picked, and the useful skill is detecting it without trusting it. Count the candidates on the first lines, run a sniffer, and compare. Telling what delimiter an unknown CSV uses gives the commands and shows where the common sniffers disagree. If the file comes from Excel on a machine you do not control, the delimiter depends on that machine's regional settings, so the same export can arrive with commas from one person and semicolons from the next. In that case, ask for the settings or ask for the file in a fixed form, and write the answer into a contract. The article on data contracts with a provider lists what to include.

If the data is nested or has long free text with line breaks, no delimiter will make a flat file comfortable, and the better answer is a format with structure, such as JSON Lines. Choosing a delimiter is a way to reduce quoting, not to remove the limits of a flat file.