Python's csv Module: Reading and Writing Tabular Data

Reading rows with csv.reader

Splitting a line on a comma looks like a one-liner until a field contains a comma of its own, wrapped in quotes to protect it. The csv module exists because that edge case is common enough — addresses, product descriptions, free-text survey answers — that hand-rolled parsing quietly breaks in production. The basic reader turns each line into a list of strings:

import csv

with open("orders.csv", newline="", encoding="utf-8") as f:
    reader = csv.reader(f)
    header = next(reader)
    for row in reader:
        print(row)
        # ['1042', 'Widget, Large', '3', '19.99']

Note that "Widget, Large" stayed as one field even though it contains a comma — the surrounding quotes told the parser to treat it as a single value. Every value comes back as a string, so numeric fields like the quantity or price need an explicit int() or float() call before you can do arithmetic on them.

DictReader and column access by name

Indexing into row[3] to get the price works until someone reorders the columns in the source spreadsheet, at which point every index silently points at the wrong field. csv.DictReader reads the first row as headers and returns each subsequent row as an OrderedDict-like mapping instead:

with open("orders.csv", newline="", encoding="utf-8") as f:
    reader = csv.DictReader(f)
    for row in reader:
        price = float(row["price"])
        qty = int(row["qty"])
        print(row["product"], price * qty)

This is more resilient to column reordering and more readable at the call site — row["price"] tells you what the value means without cross-referencing a header list. If the source file has no header row, pass fieldnames=[...] explicitly rather than letting the first data row get consumed as headers by mistake.

Writing rows with writer and DictWriter

Writing mirrors reading. csv.writer takes a sequence per row; csv.DictWriter takes a dict and needs the column order specified up front via fieldnames:

rows = [
    {"product": "Widget, Large", "qty": 3, "price": 19.99},
    {"product": "Gadget", "qty": 1, "price": 42.50},
]

with open("export.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=["product", "qty", "price"])
    writer.writeheader()
    writer.writerows(rows)

The module handles quoting the "Widget, Large" value on write automatically, so the file round-trips correctly through another read with DictReader. Forgetting writer.writeheader() is the most common mistake here — the file writes fine but the resulting CSV has no column names, which breaks any downstream tool expecting them.

Delimiters, quoting, and dialects

Not every CSV-like file uses a comma. Tab-separated exports, semicolon-separated exports from locales where the comma is a decimal separator, and pipe-separated logs are all common. The reader and writer constructors accept delimiter and quotechar keyword arguments to match:

reader = csv.reader(f, delimiter=";", quotechar='"')

For repeated use, wrap a set of options in a named dialect with csv.register_dialect() rather than passing the same keyword arguments at every call site. The module ships with an excel dialect (the default) and an excel-tab dialect for tab-delimited files, which covers most real-world spreadsheet exports without any custom configuration. See the internal dictionary techniques guide for more on shaping the dict rows that DictReader hands back once they're in memory.

Common quirks: newlines, encodings, and Excel

Three things trip up almost everyone the first time. First, always pass newline="" when opening a file for csv reading or writing — without it, the module's own line-ending handling fights with Python's universal newline translation, and quoted fields containing line breaks get mangled. Second, files saved from Excel on Windows are frequently encoded as cp1252 or carry a UTF-8 byte-order mark, not plain UTF-8; if you see a stray  at the start of your first header name, open with encoding="utf-8-sig" instead. Third, Excel's regional settings can silently swap the delimiter to a semicolon in some locales, so a file that looks fine when opened in Excel may not parse correctly with the default excel dialect. When you're passing parsed rows on to a web API or storing them, the JSON handling article covers the serialisation side once the tabular data has been converted into plain Python objects. The official csv module documentation lists every dialect parameter if you need finer control than the defaults provide.