CSV: Complete Technical Guide to Comma-Separated Values

CSV: Complete Technical Guide to Comma-Separated Values

CSV (Comma-Separated Values) is the oldest and most universal format for tabular data exchange. Defined informally for decades, it was partially formalised in RFC 4180 (2005) — though implementations still diverge significantly. CSV's strength is radical simplicity: any spreadsheet, database, programming language, and command-line tool can read and write it. Its weakness is the same: no standard encoding, no types, no schema, and no canonical way to handle edge cases.

RFC 4180 Specification

RFC 4180 defines the rules that most CSV producers and consumers follow:

1. Each record occupies exactly one line, terminated by CRLF (\r\n).
2. The last record may optionally omit the trailing CRLF.
3. An optional header row contains column names and must have the same number of fields as data records.
4. Fields may or may not be enclosed in double quotes.
5. Fields containing double quotes, commas, or CRLF must be enclosed in double quotes.
6. A double quote inside a quoted field is represented by two consecutive double quotes ("").
7. The MIME type is text/csv; the file extension is .csv.

The Double-Quote Escaping Rule

The most critical rule: to embed a literal double-quote character inside a quoted field, you must double it:

"He said, ""Hello, world!"""

Parses to: He said, "Hello, world!"

Fields with embedded newlines are legal in RFC 4180:

"line one
line two",value2

This is one complete record with a field containing a literal newline — the field is wrapped in quotes.

What RFC 4180 Does Not Define

• Character encoding: RFC 4180 specifies US-ASCII. Real-world CSV uses UTF-8 (preferred), Windows-1252, or Latin-1. Always specify encoding explicitly.
• BOM: Some Excel-generated CSVs include a UTF-8 BOM (\xEF\xBB\xBF). Python's csv module does not strip it by default; use encoding='utf-8-sig' to handle it.
• Delimiter: The name says "comma" but TSV (tab), SSV (semicolon in European locales), and pipe-delimited variants are extremely common.
• Date/number formatting: No standard. Use ISO 8601 (2025-04-26) for dates and unambiguous decimal notation.
• NULL vs empty: An empty field ,, is ambiguous — it could mean NULL or empty string. Most databases treat them differently.

Python csv Module

import csv
from pathlib import Path

# --- Reading ---
# Always specify encoding; utf-8-sig handles Excel's BOM
with open('data.csv', encoding='utf-8-sig', newline='') as fh:
    reader = csv.DictReader(fh)
    for row in reader:
        print(row['name'], row['score'])

# --- Writing ---
fieldnames = ['id', 'name', 'score', 'active']
rows = [
    {'id': 1, 'name': 'Alice', 'score': 98.5, 'active': True},
    {'id': 2, 'name': 'Bob, Jr.', 'score': 87.0, 'active': False},
    {'id': 3, 'name': 'Carol "CC" Smith', 'score': 95.5, 'active': True},
]

with open('output.csv', 'w', encoding='utf-8', newline='') as fh:
    writer = csv.DictWriter(fh, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(rows)

# output.csv:
# id,name,score,active
# 1,Alice,98.5,True
# 2,"Bob, Jr.",87.0,False
# 3,"Carol ""CC"" Smith",95.5,True

# --- Custom dialects ---
# Semicolon-delimited (European locale Excel CSVs)
with open('european.csv', encoding='windows-1252', newline='') as fh:
    reader = csv.DictReader(fh, delimiter=';')
    for row in reader:
        print(row)

# Tab-separated (TSV)
with open('data.tsv', encoding='utf-8', newline='') as fh:
    reader = csv.reader(fh, delimiter='\t')
    for row in reader:
        print(row)

Pandas for Larger CSV Files

import pandas as pd

# Read with type inference
df = pd.read_csv(
    'large.csv',
    encoding='utf-8',
    dtype={'id': 'int64', 'score': 'float64'},
    parse_dates=['created_at'],
    na_values=['', 'NULL', 'N/A'],
    low_memory=False,      # avoids mixed-type warnings on large files
)

print(df.dtypes)
print(df.head())
print(df.describe())

# Filter and export
result = df[df['score'] > 90][['id', 'name', 'score']]
result.to_csv('high_scorers.csv', index=False, encoding='utf-8')

# Process in chunks (streaming large files)
for chunk in pd.read_csv('huge.csv', chunksize=10_000):
    process(chunk)

# Read TSV / semicolon-delimited
df_tsv = pd.read_csv('data.tsv', sep='\t')
df_semi = pd.read_csv('data.csv', sep=';', decimal=',')  # European format

Command-Line CSV Processing with csvkit and Miller

csvkit

# Install
pip install csvkit

# Column statistics
csvstat data.csv

# Select specific columns
csvcut -c name,score data.csv

# Filter rows
csvgrep -c score -m 98 data.csv        # exact match
csvgrep -c name -r "^A" data.csv       # regex match

# Join two CSV files on a common column
csvjoin -c id users.csv scores.csv

# Sort
csvsort -c score -r data.csv           # sort by score descending

# Convert to JSON
csvjson data.csv
csvjson --stream data.csv              # NDJSON output

# SQL queries against CSV
csvsql --query "SELECT name, AVG(score) FROM data GROUP BY name" data.csv

Miller (mlr)

# Reorder fields
mlr --csv reorder -f id,name,score data.csv

# Compute derived column
mlr --csv put '$grade = if $score >= 90 then "A" else "B"' data.csv

# Aggregate
mlr --csv stats1 -a mean,count -f score -g active data.csv

# Convert CSV to JSON
mlr --icsv --ojson cat data.csv

Common Edge Cases and Traps

The Newline Problem

RFC 4180 mandates CRLF line endings (\r\n) but Unix CSV files use LF (\n). Windows Excel writes CRLF; Linux tools write LF. The Python csv module handles this with newline='' on open() — the module manages line endings internally. Forgetting newline='' causes double newlines on Windows.

Excel's UTF-8 BOM Requirement

Excel (pre-365) opens CSV files using the system locale encoding, not UTF-8. Non-ASCII characters (accents, Chinese, Japanese) will be garbled unless the file starts with a UTF-8 BOM. Write with encoding='utf-8-sig' to include the BOM. Note that some tools that read the file later may be confused by the BOM — encoding='utf-8-sig' on read strips it.

Numeric vs String Ambiguity

CSV has no types. Phone numbers (07700900000), ZIP codes (01234), and product IDs stored as plain digits will lose their leading zeros when parsed by tools that auto-detect numbers. Always quote numeric-looking strings or specify column types explicitly in your parser.

CSV vs Other Tabular Formats

Related conversions

Document conversions that follow this topic naturally:

• PDF to DOCX
• DOCX to PDF
• MD to PDF
• HTML to PDF
• ODT to PDF
• PDF to TXT