CSV Delimiters Explained (Comma, Tab, Semicolon)

"CSV" stands for Comma Separated Values.

So why do you open a file and see Name;Email;Phone? That's a semicolon! Or Name|Email|Phone? That's a pipe!

Welcome to the confusing world of delimiters. Here is why they exist and how to handle them.

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: Extremely rare in normal text, making it a very robust delimiter.
• 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.

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 instantly.