CSV: the format everybody sends you and nobody agrees on

Why not to split on commas

for line in open("sales.csv"):
    parts = line.split(",")      # wrong

This breaks on the first row containing "Kumar, Asha". It breaks on a field containing a newline inside quotes. It breaks on an empty trailing field. CSV looks trivial and is not: quoting, escaping and embedded newlines are all part of it, and the standard library already implements them.

Reading

import csv
from pathlib import Path

with Path("sales.csv").open(newline="", encoding="utf-8-sig") as f:
    for row in csv.DictReader(f):
        print(row["customer"], row["amount"])

DictReader reads the first line as the header and yields each row as a dictionary. csv.reader yields lists instead, which is right when there is no header.

Two arguments in that open call are doing real work.

newline="" is not optional. Without it, Python's universal newline translation converts line endings before the CSV module sees them, and a field containing a newline inside quotes gets split into two broken rows. It fails only on files that have such fields, so it passes every small test. Pass newline="" every time, for reading and writing both.

encoding="utf-8-sig" handles the byte order mark from the previous lesson, and is harmless when there is none.

Writing

with Path("out.csv").open("w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=["customer", "amount"])
    writer.writeheader()
    writer.writerows(rows)

DictWriter raises ValueError if a row contains a key not in fieldnames, which is a feature — it catches the typo that would otherwise silently drop a column. extrasaction="ignore" turns that off when you deliberately want a subset.

For a file destined for a spreadsheet on a Windows machine, write with encoding="utf-8-sig".

Everything is a string

csv does no type conversion. row["amount"] is "1250", not 1250. Adding two of them concatenates. This is the same trap as input(), at file scale, and it is why a total comes out as "12501340".

Convert at the boundary, and decide there what a bad value means:

def to_amount(value, row_number):
    try:
        return float(value)
    except ValueError:
        raise ValueError(f"row {row_number}: bad amount {value!r}")

Empty cells arrive as "", not None, so float("") raises. That is usually the largest single source of failures when reading a spreadsheet somebody filled in by hand.

Not every file called CSV is comma-separated

Europe and much of the world use ; because the comma is the decimal separator. Tab-separated files are common in bioinformatics and in database exports. The module handles all of them:

csv.reader(f, delimiter=";")
csv.reader(f, delimiter="\t")

csv.Sniffer().sniff(sample) guesses from a sample of the text. It is convenient and it is a guess; on a file with few rows or unusual quoting it gets it wrong. For a recurring job, hard-code the delimiter you were given and let the file fail loudly if it changes.

The rows that ruin a parse

Real exports contain things the format does not describe:

• A title line above the header, so the first row is not the header.
• Merged cells, which arrive as empty strings.
• Thousands separators: "1,250" is one field only if quoted, and float("1,250") raises either way.
• A trailing total row, which becomes a record with a name like TOTAL and no id.
• Two files from the same source with columns in a different order — which is exactly why DictReader is safer than positional indexing.

Check the row count and the column set before trusting the data:

rows = list(csv.DictReader(f))
print(len(rows), rows[0].keys())

When the file is too large

DictReader streams — it reads one row at a time, so a 4 GB file works as long as you do not call list() on it. The next-but-one lesson covers that properly.

Appending, and the header you write twice

new_file = not path.exists()
with path.open("a", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=FIELDS)
    if new_file:
        writer.writeheader()
    writer.writerows(rows)

Opening with "a" adds to the end. Without the new_file check, every run writes another header row into the middle of the file, and the reader turns it into a data row whose amount is the word amount. It is a small thing that quietly corrupts a growing dataset over weeks.

And when to stop hand-rolling it

Everything above is the standard library, needs no installation, and runs on a phone. Once you are doing filtering, grouping and joining across columns, pandas.read_csv does the same reading in one line with type inference and is the subject of the next module. The reason to know the csv module anyway is that it streams without loading everything into memory, it has no dependencies, and when read_csv produces something strange, it is csv that tells you what is really in the file.