CSV Delimiters Explained (Comma, Tab, Semicolon)

A file named .csv may separate fields with commas, semicolons, tabs or pipes. The choice often comes from regional spreadsheet settings or the system that produced the export. The separator matters because choosing the wrong one can put an entire row into one column or split values in the wrong places.

What is a Delimiter?

A delimiter is the character that marks the boundary between two data fields. It tells the computer: "This column ends here, the next one starts there."

The Common Delimiters

1. The Comma (,)

• Standard: The default for US, UK, and most of the world.
• Pros: Universal, understood by everything.
• Cons: If your data contains commas (e.g., "Smith, John"), you must wrap the field in quotes ("Smith, John"). This causes parsing errors if done wrong.

2. The Semicolon (;)

• Standard: Common in Europe (France, Germany, Italy, etc.).
• Why? In these countries, the comma is used as the decimal separator (e.g., 10,50 instead of 10.50). Using a comma as a delimiter would be confusing.
• Behavior: Excel in these regions defaults to saving/opening CSVs with semicolons.

3. The Tab (\t)

• File Extension: Often .tsv or .txt.
• Pros: Very safe. Data rarely contains tab characters, so you almost never need quotes.
• Cons: Hard to "see" (looks like whitespace).

4. The Pipe (|)

• Usage: Technical data, legacy systems.
• Pros: Less common in ordinary prose than commas or semicolons.
• Cons: Harder to type.

How to Handle Different Delimiters

In Excel

Opening: If you open a Semicolon-CSV in US Excel, it puts everything in Column A. Fix:

1. Data > Text to Columns.
2. Choose Delimited.
3. Check Semicolon.
4. Finish.

Saving: Excel saves based on your Windows/Mac Region Settings.

• If your region is US, it saves Commas.
• If your region is Germany, it saves Semicolons.

To force a specific delimiter, you often have to change your system region settings temporarily, or use a macro.

In Google Sheets

Google Sheets is smart. File > Import > Upload. It usually auto-detects the delimiter. If it fails, you can select "Custom" and type the character (; or |).

In Python

Pandas defaults to comma. Specify others manually:

# Semicolon
df = pd.read_csv('file.csv', sep=';')

# Tab
df = pd.read_csv('file.tsv', sep='\t')

# Pipe
df = pd.read_csv('file.csv', sep='|')

Which Delimiter Should You Use?

• Default: Use Comma (,). It's the standard.
• If data has lots of text/commas: Use Tab or Pipe.
• If targeting European users: Be aware they might prefer Semicolon.

The "Text Qualifier" (Quotes)

Regardless of delimiter, you need a strategy for when the delimiter appears inside the data. Standard practice is to wrap that field in double quotes (").

Name,Address John,"123 Main St, Apt 4"

The parser ignores the comma inside the quotes.

Quotes inside a quoted field are represented by doubling them:

name,note
Ada,"She said ""send it today"""

A quoted field may also contain a line break. That means a valid CSV record can occupy more than one physical line. Use a CSV parser for counting or splitting records rather than treating every newline as a row boundary.

Detecting a delimiter safely

Automatic detection is an informed guess. Sample several records, count candidate separators outside quoted fields, and reject a result that produces inconsistent field counts. A comma in prose or a pipe in a log message can otherwise be mistaken for the separator.

If a file opens in one spreadsheet column, import it through the application's data-import dialog and select the encoding and delimiter explicitly. Do not replace every semicolon or comma in a text editor; the same character may be part of a legitimate value.

Locale and spreadsheet behaviour

Spreadsheet applications may use the operating system's list separator when saving a delimited file. In locales where a comma is the decimal separator, semicolon-delimited exports are common. The filename may still end in .csv.

When producing a file for another system, follow that system's specification. A pipe or tab is not automatically safer: any chosen separator must still be quoted or escaped correctly when it occurs in the data.

Summary

• CSV doesn't always mean Comma.
• US/UK: Comma ,
• Europe: Semicolon ;
• Safe/Technical: Tab or Pipe |
• If your file opens weirdly (all in one column), check the delimiter!

---

Wrong delimiter? HappyCSV auto-detects delimiters and converts them to standard formats in your browser.