Reading and Writing CSV Files in Python

A CSV file is plain text with values separated by commas, which is exactly why it is used everywhere and why hand-written parsers break. Real exports contain names with commas in them, addresses with line breaks, quotes inside quotes, a byte-order mark added by Excel, or semicolons instead of commas. Python's csv module handles all of that (Python docs: csv); this guide shows each problem and the one-line fix.

Why splitting on commas fails

The CSV convention, written down in RFC 4180, puts a field in double quotes when it contains a comma, a double quote or a line break, and doubles any quote inside it (RFC 4180). A naive split(",") knows none of this:

import csv
from pathlib import Path

Path("customers.csv").write_text(
    'id,name,address,note\n'
    '1,"Rao, Asha","Flat 4B\nBanjara Hills",VIP\n'
    '2,Ravi Kumar,Madhapur,"said ""call after 6"""\n',
    encoding="utf-8", newline="",
)

with open("customers.csv", encoding="utf-8", newline="") as f:
    print(f.readline().rstrip().split(","), f.readline().rstrip().split(","))

with open("customers.csv", encoding="utf-8", newline="") as f:
    for row in csv.reader(f):
        print(row)

['id', 'name', 'address', 'note'] ['1', '"Rao', ' Asha"', '"Flat 4B']
['id', 'name', 'address', 'note']
['1', 'Rao, Asha', 'Flat 4B\nBanjara Hills', 'VIP']
['2', 'Ravi Kumar', 'Madhapur', 'said "call after 6"']

Splitting broke the name in two and cut the address at its line break. The csv module read every record correctly, including the doubled quotes. Always open CSV files with newline="" so that line breaks inside quoted fields reach the parser intact; the documentation requires it.

Rows as dictionaries

DictReader uses the header row as keys, so code refers to columns by name and keeps working if a column is added or moved:

with open("customers.csv", encoding="utf-8", newline="") as f:
    reader = csv.DictReader(f)
    print(reader.fieldnames)
    names = [row["name"] for row in reader]
print(names)

['id', 'name', 'address', 'note']
['Rao, Asha', 'Ravi Kumar']

Writing: DictWriter and quoting

Writing is the mirror image, and the module adds quotes wherever a value needs them:

rows = [
    {"sku": "PEN-01", "name": "Gel pen, blue", "price": 25.5},
    {"sku": "PAD-02", "name": 'Notepad "A5"', "price": 120},
]
with open("products.csv", "w", encoding="utf-8", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=["sku", "name", "price"])
    writer.writeheader()
    writer.writerows(rows)

print(Path("products.csv").read_text(encoding="utf-8"))

sku,name,price
PEN-01,"Gel pen, blue",25.5
PAD-02,"Notepad ""A5""",120

Only the fields that need quoting were quoted. newline="" matters when writing too: without it, Windows gains an extra blank line between rows.

The Excel byte-order mark

Files saved by Excel as "CSV UTF-8" start with an invisible byte-order mark. Read them as plain UTF-8 and the mark becomes part of the first column's name:

Path("from_excel.csv").write_bytes("city,orders\nPune,12\n".encode("utf-8"))

with open("from_excel.csv", encoding="utf-8", newline="") as f:
    print(csv.DictReader(f).fieldnames)
with open("from_excel.csv", encoding="utf-8-sig", newline="") as f:
    print(next(csv.DictReader(f))["city"])

['city', 'orders']
Pune

With plain UTF-8, row["city"] raises a KeyError, because the key is actually "city", which prints as though it were city. The utf-8-sig encoding skips the mark when reading and writes one when writing (Python docs: utf_8_sig). Reading with utf-8-sig is safe even for files without the mark, and writing with it makes Excel open non-English characters correctly.

Other delimiters

Spreadsheet software in many European locales exports with semicolons, because the comma is the decimal separator there. Pass the delimiter, or let Sniffer guess it from a sample:

Path("eu.csv").write_text("city;revenue\nParis;1234,50\n", encoding="utf-8")
with open("eu.csv", encoding="utf-8", newline="") as f:
    dialect = csv.Sniffer().sniff(f.read(1024))
    f.seek(0)
    print(dialect.delimiter, list(csv.reader(f, dialect)))

; [['city', 'revenue'], ['Paris', '1234,50']]

Detection is a heuristic, so when you know the format, pass delimiter=";" explicitly. Note that 1234,50 is still text: converting it means replacing the decimal comma before calling float().

Everything is a string

The csv module never converts types (Python docs: csv): "25.5", "120" and "" all arrive as strings. Convert each column deliberately, and decide what an empty field means:

with open("products.csv", encoding="utf-8", newline="") as f:
    prices = [float(row["price"]) for row in csv.DictReader(f) if row["price"]]
print(prices, sum(prices))

[25.5, 120.0] 145.5

For money, prefer decimal.Decimal over float to avoid binary rounding. Dates need parsing too; datetime in Python covers that.

Validate before you trust a file

CSV files usually come from somewhere else, and their format drifts: a column is renamed, a new one appears, a field is left empty in some rows. Check the structure before processing the data, and report problems with line numbers so someone can fix the source:

• Compare the header with the columns you expect, using reader.fieldnames. A missing or renamed column should stop the job with a message naming it, not produce a KeyError halfway through.
• Check required fields in each row, and collect the failures instead of stopping at the first. reader.line_num gives the current line in the file, which is the number to report, since quoted fields can span several lines.
• Convert types in one place, catching ValueError per row and recording which value failed to parse.
• Decide on a threshold. A few bad rows can be quarantined to a separate file; a file where half the rows fail is probably the wrong file.

For pipelines that run on a schedule, these checks grow into proper data quality tests.

Designing CSV exports other people will use

When your program writes CSV for someone else, a few conventions save them hours:

• Always include a header row with stable, lowercase, unambiguous column names, and put units in the name, such as amount_inr or duration_s.
• Write one record per row, and never mix summary rows or blank separator lines into the data.
• Write dates in ISO format (2026-09-25), which sorts correctly and is not ambiguous between day-month and month-day readers.
• Leave numbers unformatted: no thousands separators or currency symbols inside numeric fields.
• Use UTF-8, with utf-8-sig if the audience will open the file in Excel.

Large files and when to use pandas

csv.reader and DictReader read one row at a time, so a multi-gigabyte file can be filtered or aggregated in constant memory by looping over it. When the job is analysis (types inferred, dates parsed, grouping, joining), pandas.read_csv does in one call what the loops above do by hand, with sep, encoding, parse_dates and chunksize covering the same concerns (pandas docs: read_csv). The pandas guide walks through that workflow. Use the csv module for simple transformations, streaming and scripts without dependencies; use pandas for analysis.

CSV files are one of the formats in Python file handling; for nested data, JSON is usually the better format, covered in working with JSON in Python. Reading and writing files, including CSV and JSON handling, form the file handling module of the Python Programming course.

Sources

• csv — CSV File Reading and Writing — Python documentation
• encodings.utf_8_sig — Python documentation
• RFC 4180: Common Format and MIME Type for CSV Files — RFC Editor
• pandas.read_csv — pandas documentation