CSV Format: A Complete Guide for Developers

Master the CSV format — RFC 4180 rules, common gotchas with delimiters and encoding, conversion to JSON, and best practices for data pipelines.

By Belqassim Djafer · Published · Updated

What Is CSV?

CSV (Comma-Separated Values) is the simplest and most universally supported format for tabular data. Despite its simplicity — or because of it — CSV is used everywhere: spreadsheets, database exports, data science pipelines, ETL tools, and legacy system integrations.

A basic CSV file:

id,name,email,age
1,Alice,alice@example.com,30
2,Bob,bob@example.com,25
3,Charlie,"charlie@example.com",35

RFC 4180: The Informal Standard

CSV has no single formal standard, but RFC 4180 defines the most widely adopted rules:

  1. Each record on a separate line ending with CRLF (\r\n)
  2. The last record may or may not have a line ending
  3. An optional header row as the first line
  4. Fields separated by commas
  5. Fields containing commas, newlines, or double-quotes must be enclosed in double-quotes
  6. Double-quotes inside a quoted field are escaped as two double-quotes: ""
name,description,price
Widget A,"A red, round widget",9.99
Widget B,"A ""premium"" widget with a handle",24.99
Widget C,"Multi-line
description",14.99

Common CSV Gotchas

Delimiters Vary by Region

Despite being named "comma-separated," CSV files often use other delimiters:

Context Delimiter Reason
Most of the world , (comma) Standard
Germany, France, Spain ; (semicolon) Comma is decimal separator
Tab-delimited (TSV) \t (tab) Avoids comma conflicts
Pipe-delimited ` ` (pipe)

Always detect or specify the delimiter explicitly rather than assuming comma.

Encoding Issues

The most common CSV pain point is character encoding:

  • Windows/Excel generates Latin-1 (ISO-8859-1) or UTF-16 with BOM
  • Unix tools expect UTF-8
  • Accented characters (é, ü, ñ) become garbled when encoding mismatches
# Always specify encoding when reading CSV in Python
import csv

with open("data.csv", encoding="utf-8-sig") as f:  # utf-8-sig handles BOM
    reader = csv.DictReader(f)
    for row in reader:
        print(row)

Numeric Types and Locales

Excel interprets values like 1,234 as numbers in some locales, breaking fields that contain commas in quoted strings. Scientific notation (1E+15) can corrupt IDs when opened in Excel.

# To protect numeric IDs from Excel corruption
id          → "id"           (force text with leading =)
1234567890123  → ="1234567890123"  (Excel formula to keep as text)

Leading/Trailing Whitespace

name, age,  city
Alice, 30, Paris

Whether 30 is stored as integer 30 or string " 30" depends on the parser. Always trim values explicitly.

Empty Fields vs Missing Fields

name,email,phone
Alice,alice@example.com,
Bob,,555-1234

An empty field (alice@example.com, followed by nothing) is distinct from a missing field. Parsers that do not handle variable-length rows will error.

CSV in Practice

Node.js with PapaParse

import Papa from "papaparse";

// Parse CSV string
const result = Papa.parse(csvString, {
  header: true,       // Use first row as field names
  skipEmptyLines: true,
  transformHeader: h => h.trim(),
  transform: value => value.trim()
});

console.log(result.data);  // Array of objects
console.log(result.errors); // Parse errors

// Generate CSV from objects
const csv = Papa.unparse(result.data);

Python with csv Module

import csv

# Read
with open("data.csv", newline="", encoding="utf-8") as f:
    reader = csv.DictReader(f)
    rows = list(reader)

# Write
with open("output.csv", "w", newline="", encoding="utf-8") as f:
    fieldnames = ["id", "name", "email"]
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(rows)

SQL: Import and Export

-- PostgreSQL: import CSV
COPY users(id, name, email)
FROM '/tmp/users.csv'
CSV HEADER
DELIMITER ','
ENCODING 'UTF8';

-- PostgreSQL: export CSV
COPY (SELECT id, name, email FROM users WHERE active = true)
TO '/tmp/active_users.csv'
CSV HEADER;

# MySQL
mysqlimport --local --fields-terminated-by=',' --lines-terminated-by='\n' \
  --ignore-lines=1 mydb /tmp/users.csv

CSV vs JSON vs Excel

Feature CSV JSON Excel (.xlsx)
Tabular data ✅ ❌ (nested) ✅
Hierarchical data ❌ ✅ Limited
Human readable ✅ ✅ ❌ (binary)
Schema ❌ JSON Schema ❌
Multi-sheet ❌ ❌ ✅
Formulas ❌ ❌ ✅
File size Smallest Small Largest
Universal support ✅ ✅ ❌ (requires Excel/Sheets)

Use CSV when: exporting/importing bulk tabular data, working with data science tools, feeding ETL pipelines.

Use JSON when: data is hierarchical or nested, working with REST APIs.

Best Practices for CSV in Data Pipelines

  1. Always include a header row — it makes the format self-documenting
  2. Specify encoding explicitly — never rely on platform defaults
  3. Validate before loading — check row count, column count, and type expectations
  4. Log and quarantine bad rows — don't fail the entire import on one malformed row
  5. Use a real CSV parser — never split on commas with str.split(","); it breaks on quoted fields
# Wrong: breaks on quoted fields containing commas
rows = [line.split(",") for line in file.readlines()]

# Right: use a proper parser
import csv
reader = csv.reader(file)
rows = list(reader)

Use our CSV to JSON and JSON to CSV converters to transform between formats, and our CSV Compare tool to diff two CSV files.

Conclusion

CSV's universal support makes it indispensable for data interchange, but its lack of a strict standard means developers must handle delimiter variations, encoding mismatches, and quoting rules carefully. Always use a robust parser, specify encoding explicitly, and validate data on both import and export.

Related Tools