CSV Delimiters Explained: Comma, Semicolon, Tab, and Pipe

A CSV delimiter is the character that separates one value from the next in a row, and while it defaults to a comma, plenty of files use a semicolon, tab, or pipe instead. If your data looks fine in a text editor but collapses into one column when you open it, the delimiter is almost always the reason.

Below is what each delimiter is, why the comma breaks on certain data, and how to detect and change the one a file uses without corrupting anything.

What a delimiter is and why CSV needs one

A delimiter is just a character that marks a boundary. In a CSV file, it tells the parser where one field ends and the next begins. CSV stands for “comma-separated values,” so the comma is the historical default, but nothing in the format physically requires a comma. The delimiter is a convention, not a law.

Here is a plain row with commas:

name,city,role
Ana,Bengkulu,Designer

The parser reads that line, splits on each comma, and hands back three fields per row. Without a delimiter, a row is one long string and every tool would have to guess where the columns are. The delimiter removes the guessing. That is its whole job.

The comma default, and why it breaks on European data

The comma works fine until your data itself contains commas. The most common place this bites you is numbers. In many European locales, the decimal separator is a comma, not a period. So 1,5 means one and a half, and 1.000 means one thousand.

Now imagine a price column written as 1.299,50. A comma-delimited parser sees that comma and splits the value in two, shoving 50 into the next column and knocking every field after it out of alignment. One row of European-formatted numbers can shift an entire spreadsheet sideways.

This is exactly why so many files from German, French, or Indonesian systems arrive with a different separator. When the comma is already busy being a decimal point, you cannot also use it to separate columns. Something has to give, and the column delimiter is what changes.

Semicolon, tab, and pipe delimiters (when each shows up)

When the comma is taken, the semicolon is the usual replacement. A CSV semicolon delimiter is standard across most of continental Europe, and it is what Excel writes by default when your operating system uses a comma decimal separator. Open a “CSV” export from a European tool and you will often find semicolons throughout.

name;price;city
Ana;1.299,50;Bengkulu

Tab-delimited files are the next common variant, usually saved as .tsv or .tab. Tabs rarely appear inside real text values, so they collide with your data far less often than commas do. If you want the full comparison of when to reach for one over the other, see tsv vs csv.

The pipe character (|) is the heavy-duty option. It shows up in data exports from banks, telecoms, and older enterprise systems, precisely because almost nobody types a pipe inside a name or address. When your values are messy and full of punctuation, a pipe delimiter is the safest bet you can pick.

How quoting rescues values that contain the delimiter

You do not always have to abandon the comma. The CSV spec has a built-in escape hatch: wrap any value that contains the delimiter in double quotes, and the parser treats everything inside the quotes as one field.

name,note
Ana,"Lives in Bengkulu, Indonesia"

Here the comma inside "Lives in Bengkulu, Indonesia" is protected. The quotes tell the parser “this comma is data, not a boundary.” If the value itself contains a double quote, you escape it by doubling it: "She said ""hi""" becomes She said "hi".

Quoting is why a well-formed comma CSV can hold addresses, sentences, and other comma-heavy text without falling apart. The catch is that not every tool writes quotes correctly, and a single missing quote can throw off every row that follows. When quoting fails, switching to a delimiter your data never contains is the more durable fix.

Why Excel sometimes guesses the wrong delimiter

Excel does not read a header that announces the delimiter, because CSV files do not carry one. It guesses, and the guess depends on your regional settings. On a machine set to United States English, Excel expects commas. On a machine set to a locale with a comma decimal separator, it expects semicolons.

So the same file can open perfectly on one laptop and land entirely in column A on another. Nothing changed in the file. Only the reader’s assumptions did.

There is a lesser-known override for this. Excel honors a first line reading sep=; (or sep=,, sep=\t) as an explicit instruction for which delimiter to use. Add that line to the top of a file and Excel will stop guessing. Most other parsers ignore it, so use it only when Excel is your target.

How to detect and change a file’s delimiter safely

To detect the delimiter, open the file in a plain text editor, not a spreadsheet. A spreadsheet hides the raw characters behind its own parsing. A text editor shows you the truth: look at a data row and see what actually sits between the values. If you see semicolons, it is semicolon-delimited, whatever the .csv extension claims.

To change the CSV delimiter safely, do not find-and-replace one character with another. A blind replace will also swap delimiters that live inside quoted values, which quietly breaks those rows. Instead, re-parse the file with the correct source delimiter and re-export with the one you want. A proper parser respects the quotes and only touches real field boundaries.

If you understand what CSV actually is under the hood, the delimiter stops being mysterious. Our companion piece on what is a csv file walks through the whole structure, and the guide to csv encoding and UTF-8 covers the other thing that garbles imports: character encoding.

Delimiters, special characters, and clean conversion

Delimiters are one member of a small family of CSV special characters that all need careful handling: the delimiter itself, the quote character, the escape for a doubled quote, and the line break between rows. Get any of them wrong and a file that looks fine turns into scrambled columns downstream.

The cleanest way to see whether a file parsed correctly is to view it as a real table rather than a wall of separators. Paste it into our CSV to Markdown converter and it turns the rows into an aligned Markdown table you can read at a glance. If a column is off by one or a value spilled into the wrong cell, the misaligned table shows you instantly, which makes a delimiter or quoting problem obvious before you ship the data anywhere.

FAQ

What is the default CSV delimiter? The comma. CSV means “comma-separated values,” so a plain .csv file uses commas unless it was written by a tool or locale that defaults to something else, most commonly a semicolon.

Why do some CSV files use semicolons instead of commas? Because their numbers use a comma as the decimal separator. In much of Europe and in Indonesian formatting, 1.299,50 is a valid price, so the comma is already taken and the semicolon becomes the column separator instead.

How do I know which delimiter a CSV file uses? Open it in a plain text editor and look at what sits between the values in a data row. A comma, semicolon, tab, or pipe will be visible directly. Do not check in a spreadsheet, because the spreadsheet parses the file before you see it.

Can a CSV value contain the delimiter character? Yes, if it is quoted. Wrapping the value in double quotes, like "Bengkulu, Indonesia", tells the parser that the comma inside is data and not a field boundary. A value with a doubled quote inside escapes it as "".