CSV: The Complete Guide to Comma-Separated Values Format
CSV (Comma-Separated Values) is the lingua franca of data exchange. It is not flashy, it is not powerful, and it cannot store formulas, formatting, or multiple sheets — but it is universally supported, human-readable, and has survived for over 50 years because of those very limitations. Every spreadsheet application, database, data science tool, and programming language can read and write CSV. When you need to move tabular data between systems, CSV is usually the answer.
What Is CSV?
A CSV file stores tabular data — rows and columns — as plain text. Each row is a line; values within each row are separated by commas (or another delimiter). The first row typically contains column headers.
Name,Email,Age,City
Alice Smith,alice@example.com,32,New York
Bob Jones,bob@example.com,28,London
Carol Williams,carol@example.com,45,Toronto
That's it. Three columns, three data rows, one header row, all in plain text. A 3-row, 3-column spreadsheet in Excel would be saved as a multi-kilobyte .xlsx file with XML metadata; as CSV, it's 105 bytes.
The "Standard" That Isn't Quite One
CSV has no formal standard — or rather, it has RFC 4180 (2005), a "de facto" description that many implementations violate. The practical rules most parsers follow:
- Each record on its own line, terminated by CRLF or LF
- The last record may or may not have a trailing newline
- The first record may contain a header row (by convention, not requirement)
- Fields may be enclosed in double quotes — required if the field contains commas, newlines, or double quotes
- A double quote inside a quoted field is escaped by a second double quote:
"He said ""Hello""" - Fields may contain any character (though many parsers struggle with embedded newlines)
What RFC 4180 does NOT define:
- The delimiter character (commas are conventional but semicolons, tabs, pipes are common)
- Character encoding (UTF-8 is best practice but legacy tools use Latin-1, Windows-1252)
- Whether a header row exists
- How to handle null/empty values
- Number and date formats
This ambiguity is why CSV parsing is harder than it looks and why "CSV" means slightly different things to different tools.
Delimiters: CSV is Not Always Comma-Separated
Confusingly, the CSV "standard" is often applied to files with non-comma delimiters:
- TSV (Tab-Separated Values): tabs as delimiters — avoids the quoting problem for data that contains commas
- Semicolon-separated: common in European Excel (countries that use comma as decimal separator use semicolon as CSV delimiter to avoid ambiguity)
- Pipe-separated (
|): used in some database exports and legacy systems - Colon-separated: rare but exists
Excel and LibreOffice Calc adapt automatically: when opening a CSV, they detect or ask for the delimiter.
Quoting and Escaping
The quoting rules are where CSV gets complicated:
"Name","Favorite Quote","Score"
"Alice","He said ""I love CSV""",95
"Bob","Line 1
Line 2",82
"Carol","Commas, commas, commas",78
- Fields with commas are quoted:
"Commas, commas, commas" - Fields with double quotes escape them by doubling:
"He said ""I love CSV""" - Fields with embedded newlines are quoted: the value spans multiple physical lines
- Fields without special characters can be unquoted:
Aliceor"Alice"— both valid
Parsers that do not handle quoting correctly are a major source of CSV bugs.
Character Encoding
CSV files are plain text — the encoding matters enormously:
UTF-8: the best practice encoding for new CSV files. Handles any language/script.
UTF-8 with BOM: Excel (pre-2016) opens UTF-8 CSV incorrectly without the BOM (Byte Order Mark: 0xEF 0xBB 0xBF at the start of the file). Adding the BOM fixes Excel but can confuse other tools.
Windows-1252 / Latin-1: legacy encoding used by older Windows tools. Cannot represent non-Western characters. Causes garbled output when opened as UTF-8.
How to check encoding:
file -i data.csv # Linux/macOS — shows detected encoding
chardet data.csv # Python chardet CLI tool
How to convert encoding:
iconv -f windows-1252 -t utf-8 input.csv -o output.csv
Reading CSV in Every Language
Python (pandas)
import pandas as pd
df = pd.read_csv('data.csv', encoding='utf-8')
print(df.head())
# With options
df = pd.read_csv('data.csv',
delimiter=';', # semicolon-separated
encoding='windows-1252',
decimal=',', # European decimal separator
thousands='.', # European thousands separator
parse_dates=['Date'], # auto-parse date columns
na_values=['N/A', 'NULL', ''] # null value strings
)
Python (built-in csv module)
import csv
with open('data.csv', newline='', encoding='utf-8') as f:
reader = csv.DictReader(f)
for row in reader:
print(row['Name'], row['Email'])
JavaScript (Papa Parse)
Papa.parse(csvText, {
header: true,
dynamicTyping: true, // auto-convert numbers and booleans
skipEmptyLines: true,
complete: function(results) {
console.log(results.data);
}
});
SQL (PostgreSQL COPY)
COPY users (name, email, age, city)
FROM '/path/to/data.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'utf8');
Converting CSV
CSV → Excel (.xlsx)
Excel opens .csv files directly (File → Open, or double-click). To preserve formatting and types:
- In Excel: Data → Get Data → From Text/CSV
- The Power Query import wizard lets you specify delimiter, encoding, and column types
- Then Close & Load to create a proper Excel table
Or use Python: df.to_excel('output.xlsx', index=False) (requires openpyxl)
CSV → JSON
import csv, json
with open('data.csv', encoding='utf-8') as f:
rows = list(csv.DictReader(f))
with open('data.json', 'w', encoding='utf-8') as f:
json.dump(rows, f, indent=2, ensure_ascii=False)
CSV → SQL INSERT statements
import csv
with open('data.csv') as f:
reader = csv.DictReader(f)
for row in reader:
cols = ', '.join(row.keys())
vals = ', '.join(f"'{v}'" for v in row.values())
print(f"INSERT INTO table ({cols}) VALUES ({vals});")
Database → CSV export
-- PostgreSQL
\COPY (SELECT * FROM users) TO 'output.csv' CSV HEADER;
-- MySQL
SELECT * FROM users
INTO OUTFILE '/var/lib/mysql-files/output.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
CSV Best Practices
- Always specify encoding explicitly — UTF-8 for new files; document if otherwise
- Always include a header row — makes the file self-describing
- Use double-quoting for string fields that might contain commas or newlines
- Use ISO 8601 for dates:
2025-04-25is unambiguous;04/25/25is not - Do not rely on column order — use the header row as the contract
- Validate on import: check row counts, data types, null handling
- For large files: prefer streaming parsers (csv.reader in Python) over loading everything into memory at once
- For complex data: consider JSON, Parquet, or Arrow instead — CSV cannot represent nested structures or typed columns reliably
CSV's strength is its universality. It is the only format you can open with a text editor, import into Excel, read with Python, load into PostgreSQL, and pass to an R data frame — all without conversion or special tools. For tabular data that needs to travel between systems, nothing beats it.
Related conversions
Document conversions that follow this topic naturally: