CSV Format: The Complete Guide to Comma-Separated Values

What Is CSV?

CSV stands for Comma-Separated Values, a plain-text format for representing tabular data — rows and columns — where each row is a line of text and columns within that row are separated by commas. Despite its apparent simplicity, CSV has more edge cases, gotchas, and interoperability problems than almost any other ubiquitous format in computing.

CSV has no single official standard. The closest thing to a specification is RFC 4180 (2005), which documents common practice without being a formal standard. Different applications implement CSV differently in ways that cause frequent data corruption when files are exchanged between systems.

RFC 4180 Rules

RFC 4180 defines the following structure:

1. Each record (row) is on a separate line delimited by CRLF (\r\n)
2. The last record in the file may or may not have an ending line break
3. An optional header record may appear as the first line, containing column names
4. Within each record, fields are separated by commas
5. Each field may or may not be enclosed in double quotes
6. Fields containing special characters (commas, double quotes, or line breaks) must be enclosed in double quotes
7. A double quote inside a double-quoted field is escaped by preceding it with another double quote ("")

name,age,city
"Smith, John",42,"New York"
"O'Brien, Mary",""Unknown"",London

Wait — the second line has a bug. The correct escape for a literal double quote is "":

name,age,city
"Smith, John",42,"New York"
"O'Brien, Mary","""Unknown""",London

The Quoting Rules in Detail

Understanding quoting is essential for handling CSV correctly:

When are quotes required?

• Field contains the delimiter character (comma by default)
• Field contains a double quote character
• Field contains a newline or carriage return

When are quotes optional?

• Any other field (including purely numeric fields)

Quoting examples:

Delimiter Variants

Despite the "C" in CSV meaning "comma", CSV files frequently use other delimiters:

European users frequently encounter the semicolon delimiter because in locales where the decimal separator is , (e.g., Germany: 1.234,56 for one thousand two hundred thirty-four point fifty-six), using commas as CSV delimiters would be ambiguous. Microsoft Excel uses the system's list separator character when importing/exporting CSV, which is ; in European locales — a persistent source of confusion.

The Encoding Problem

CSV files have no built-in mechanism to declare their character encoding. This is one of the biggest practical problems with the format. Common scenarios:

UTF-8 with BOM: Some applications (notably Excel) add a UTF-8 Byte Order Mark (0xEF 0xBB 0xBF) at the start of the file to signal UTF-8 encoding. Other applications treat the BOM as visible characters, introducing garbage at the start of the first field. Python's csv module ignores the BOM when using encoding='utf-8-sig'.

Windows-1252 vs UTF-8: Excel on Windows historically saved CSVs in the Windows ANSI code page (often Windows-1252 in English locales). When these files are opened in UTF-8 environments, characters like é, ü, ñ become garbled (é, ü, ñ). Always specify the encoding explicitly when reading CSV programmatically.

Excel's automatic type conversion: Excel aggressively interprets CSV fields as dates, numbers, or scientific notation:

• 1-2 → January 2 (date)
• 1e10 → 10,000,000,000 (scientific notation)
• 0001 → 1 (leading zeros stripped)
• 007 → 7 (leading zeros stripped)

Gene names like SEPT2 and MARCH1 were famously renamed by bioinformaticians because Excel kept converting them to dates. This is not a bug that can be fixed in the CSV file — it is Excel's parsing behaviour.

CSV vs. Excel (XLSX)

When to use CSV over XLSX:

• Data interchange between different software systems
• Command-line processing (grep, awk, sed, csvkit)
• Database import/export
• Log file format
• Configuration files with tabular data
• When file size matters

CSV Parsing: Edge Cases

Correctly parsing CSV requires handling:

Multi-line fields: A field enclosed in quotes may span multiple lines. A naive line-by-line parser fails on these.

Trailing delimiter: Some generators add a trailing comma after the last field on each row. RFC 4180 does not permit this, but many parsers tolerate it.

Whitespace around delimiters: field1 , field2 — should the spaces be part of the field values? RFC 4180 says yes; some parsers strip them.

Inconsistent quoting: "field1,field2 (unclosed quote) — how parsers handle this varies.

NULL vs empty string: ,, contains two empty strings. There is no standard way to represent NULL differently from empty string in CSV.

A robust CSV parser (Python's csv.reader, pandas read_csv, JavaScript's Papa Parse) handles all these cases. Writing ad-hoc CSV parsing with str.split(',') is a guaranteed bug farm.

Working with CSV Programmatically

Python:

import csv

# Reading
with open('data.csv', newline='', encoding='utf-8') as f:
    reader = csv.DictReader(f)
    for row in reader:
        print(row['name'], row['age'])

# Writing
with open('output.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.DictWriter(f, fieldnames=['name', 'age', 'city'])
    writer.writeheader()
    writer.writerow({'name': 'Alice', 'age': 30, 'city': 'Paris'})

pandas:

import pandas as pd

df = pd.read_csv('data.csv', encoding='utf-8', dtype=str)  # dtype=str prevents auto-conversion
df.to_csv('output.csv', index=False)

Specifying encoding when opening in Excel: Use Data → From Text/CSV wizard (not double-click open) to specify encoding, delimiter, and column types. Alternatively, prepend sep=; on the first line to hint the delimiter to Excel.

Converting CSV

CSV → XLSX: Open in Excel, verify data types, save as XLSX. Programmatically: pandas.DataFrame.to_excel() or openpyxl.

CSV → JSON: Each row becomes an object; the header row provides key names. Many tools handle this: csvkit, jq, Python csv + json modules.

CSV → SQL INSERT statements: Tools like csvkit's csvsql generate CREATE TABLE and INSERT statements from CSV.

CSV → Parquet/Arrow: For analytics workloads, converting CSV to columnar formats dramatically speeds up queries. pandas.to_parquet() or Apache Arrow's csv.read_csv() + parquet.write_table().

Summary

CSV is simultaneously the simplest and most treacherous data exchange format in common use. Its simplicity — plain text, no dependencies, human-readable — makes it the universal fallback for tabular data interchange. Its ambiguities — encoding undeclared, delimiter undefined, NULL indistinguishable from empty string, Excel's type coercions — make it the source of more silent data corruption than almost any other format. Use a proper CSV library, always specify encoding explicitly, and verify data types after import. When structure and types matter more than compatibility, use XLSX or JSON instead.

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