CSV Delimiters Explained: Commas, Semicolons, and Tabs

Meta description: Why does your CSV file use semicolons instead of commas? Learn about CSV delimiters, how they work, and how to handle delimiter issues in your data.

The "C" in CSV stands for Comma. But half the CSV files out there don't actually use commas. They use semicolons. Or tabs. Or pipes. It's a mess, and it trips people up constantly.

Understanding delimiters is the difference between data that loads cleanly and data that's an unreadable blob in a single column.

What Is a Delimiter?

A delimiter is the character that separates values in each row of a CSV file. It tells software where one field ends and the next begins.

Here's the same data with different delimiters:

Comma-separated:

name,age,city
Alice,30,Paris

Semicolon-separated:

name;age;city
Alice;30;Paris

Tab-separated (TSV):

name	age	city
Alice	30	Paris

They all represent the same data. The only difference is the separator character.

Why Don't All CSVs Use Commas?

Short answer: decimal separators.

In the US and UK, decimals use a dot: 3.14. A comma works fine as a delimiter because it doesn't conflict.

But in France, Germany, Spain, Brazil, and most of continental Europe, decimals use a comma: 3,14. If you use a comma as both the decimal separator AND the field delimiter, chaos ensues:

produit,prix,quantité
Widget,12,50,100    ← Is that 12.50 and 100? Or 12 and 50 and 100?

So European locales typically use the semicolon (;) as the delimiter instead. It's not a bug — it's a feature.

The Most Common Delimiters

| Delimiter | Character | When You'll See It |

|-----------|-----------|-------------------|

| Comma | , | US/UK default, most international data |

| Semicolon | ; | European locales (France, Germany, etc.) |

| Tab | \t | TSV files, database exports, scientific data |

| Pipe | \| | Legacy systems, data that contains commas and semicolons |

| Space | | Fixed-width data, some scientific formats |

How to Detect the Delimiter

Visually

Open the file in a text editor (not Excel!) and look at the first few lines. The delimiter is usually obvious.

With a CSV Viewer

Load the file into CSV Viewer Online. It automatically detects the delimiter and shows your data in a clean table. If it looks right, the detection worked.

Programmatically

Python's csv module has a Sniffer class that auto-detects delimiters:

python
import csv
with open('mystery.csv', 'r') as f:
sample = f.read(4096)
dialect = csv.Sniffer().sniff(sample)
print(f"Delimiter: '{dialect.delimiter}'")
print(f"Quote char: '{dialect.quotechar}'")

Pandas also auto-detects in most cases:

python
import pandas as pd

'sep=None' with 'engine=python' triggers auto-detection
df = pd.read_csv('mystery.csv', sep=None, engine='python')

The Quoting Problem

What happens when your data itself contains the delimiter character? Like a comma in an address:

name,address,city
"Smith, John","123 Main St, Apt 4",New York

The solution is quoting: wrap the field in double quotes. Any comma inside quotes is treated as data, not a delimiter.

Quotes Inside Quoted Fields

What if the data contains quotes too? You escape them by doubling:

name,quote
Alice,"She said ""hello"" and left"

The "" inside the quoted field represents a single " character.

Common Quoting Styles

| Style | Example | Used By |

|-------|---------|---------|

| Double quotes (RFC 4180) | "value with, comma" | Most software |

| Single quotes | 'value with, comma' | Some databases |

| Backslash escape | value with\, comma | Unix tools, some APIs |

| No quoting | Delimiter cannot appear in data | Simple datasets |

How to Handle Delimiter Issues

Problem: Everything in One Column

Your CSV opened and all data is in column A. The software used the wrong delimiter.

Fix: Re-import with the correct delimiter. In Excel, use Data > From Text/CSV and select the delimiter manually. In Google Sheets, use Data > Split text to columns and choose the right separator.

Problem: Too Many Columns

Your data has unquoted commas in text fields, creating extra columns.

Fix: The source data needs to properly quote fields containing the delimiter. If you can't fix the source, try a different delimiter or use a parser that handles edge cases:

python
import pandas as pd

Pandas handles quoted fields correctly
df = pd.readcsv('messy.csv', quoting=1)  # QUOTEALL

Problem: Mixed Delimiters

Different rows use different delimiters. This usually means the file was badly generated.

Fix: Preprocess the file to standardize:

python
with open('mixed.csv', 'r') as f:
content = f.read()

Replace semicolons with commas (if that's the intended delimiter)
content = content.replace(';', ',')
with open('fixed.csv', 'w') as f:
f.write(content)

Warning: This is destructive if semicolons appear in your actual data. Always inspect first.

Choosing a Delimiter for Your Own CSVs

If you're creating CSV files, here's a simple decision tree:

1. Will the data contain commas? (addresses, descriptions, free text)

• Yes → Use tab or pipe delimiter. Or use commas with proper quoting.

• No → Use commas (the universal default).

1. Will European users open this in Excel?

• Yes → Consider semicolons, since their Excel expects it.

• No → Stick with commas.

1. Is the data going into a database or API?

• Yes → Commas with RFC 4180 quoting. Every parser handles this.

1. Does the data contain every possible delimiter?

• Use tab. It almost never appears in natural text data.

The RFC 4180 Standard

There is actually a formal standard for CSV files: RFC 4180. The key rules:

1. Delimiter is comma

1. Records are separated by CRLF (line break)

1. Fields containing commas, double quotes, or line breaks must be enclosed in double quotes

1. Double quotes inside quoted fields are escaped by doubling them

Most modern software follows this standard. But plenty of legacy systems, European exports, and database dumps don't. That's life.

Quick Reference

| I need to... | Do this |

|-------------|---------|

| Find out what delimiter a file uses | Open in text editor or CSV viewer |

| Open a semicolon CSV in Excel | Data > From Text > select Semicolon |

| Change delimiter in a file | sed 's/;/,/g' input.csv > output.csv (simple) or Python |

| Create a CSV with commas in data | Use proper quoting: "field, with comma" |

| Handle international CSV files | Check locale, expect semicolons from Europe |

Delimiters are one of those "boring but essential" things about CSV files. Get them right and everything works. Get them wrong and your data is unreadable. When in doubt, open the raw file in a text editor first — three seconds of inspection saves thirty minutes of debugging.