CSV (Comma-Separated Values) is one of the most common formats for exchanging tabular data. Despite its apparent simplicity, CSV files regularly cause problems during import and processing.
What is CSV, and why does it break?
CSV is a text format in which each line represents a record and fields are separated by a specified character. The format is so simple that the RFC 4180 standard was written only in 2005—decades after the format had already been in use.
The main reasons CSV files “break”:
- Unequal number of columns — some rows contain 5 fields, others 4
- Incorrect encoding — a Windows-1251 file opens as gibberish
- BOM (Byte Order Mark) — Excel adds an invisible character at the beginning of a UTF-8 file
- Unescaped commas — a field containing a comma is not enclosed in quotes
- Line breaks inside fields — a field contains
\nwithout quotes
Delimiters: when to use each one
| Delimiter | Character | Use case |
|---|---|---|
| Comma | , | International standard, APIs, JSON-like data |
| Semicolon | ; | Excel in Russian/European locales |
| TAB | \t | Data with commas in fields, TSV format |
| Pipe | | | Data containing both commas and semicolons |
The Excel problem: in Russian-language Windows, Excel uses ; as the default delimiter because , is the decimal separator. As a result, a file that complies with the international standard opens in a single column.
Quoting rules under RFC 4180
RFC 4180 defines the mandatory escaping rules:
- Fields containing a delimiter, quotation mark, or line break must be enclosed in double quotes
- Double quotes inside a field are escaped by doubling them:
"" - Quotes around a field are optional when it contains no special characters
# Корректный CSV
name,description,price
"Иванов, И.И.","Товар ""Особый""",1500.00
Петров П.П.,Обычный товар,750.00
# Ошибка — запятая внутри поля без кавычек
Иванов, И.И.,Товар,1500.00Common CSV problems
Problem 1: BOM in UTF-8 files
When saving as UTF-8, Excel adds a BOM (\xEF\xBB\xBF) at the beginning of the file. This breaks imports into programs that do not expect a BOM.
Solution when reading in Python:
import csv
with open('data.csv', encoding='utf-8-sig') as f: # utf-8-sig убирает BOM
reader = csv.reader(f)Problem 2: Unequal number of columns
# Ошибочный файл
name,age,city
Иванов,30,Москва
Петров,25 ← только 2 поля вместо 3
Сидоров,40,Сочи,extra ← 4 поля вместо 3Most parsers will return an error or lose data. Check the file before importing it.
Problem 3: Mixed line endings
Different operating systems use different line-ending characters:
- Windows:
\r\n(CRLF) - Unix/Linux:
\n(LF) - Older Mac:
\r(CR)
RFC 4180 specifies \r\n, but in practice most parsers accept any variant.
Tips for creating reliable CSV files
- Always specify the encoding — UTF-8 without a BOM is universal for modern systems
- Use a header — a first row with column names prevents confusion
- Quote fields consistently — either all fields or only those containing special characters
- Check before importing — run the file through a validator to avoid unexpected results
- Document the delimiter — especially when sharing the file with other teams
To convert CSV to structured JSON, use the CSV to JSON converter. To work with JSON data, use the JSON formatter.
Tool on reChecker
The CSV validator on reChecker checks the file structure: equal numbers of fields in every row, correct quoting, BOM detection, and automatic identification of the delimiter used.