Working with CSV files

CSV looks like the easiest format there is — values separated by commas, one row per line. That impression is why so much broken CSV handling exists. The format has real complications, and the standard library handles all of them.

Never split on commas

The tempting approach:

for line in file:
    fields = line.strip().split(",")

That works until a field contains a comma:

name,address,city
Priya,"703, Jubilation, Wagholi",Pune

Splitting that gives five fields instead of three, silently, and every row afterwards is misaligned. Quoted commas are legal CSV and extremely common in addresses, descriptions and anything a human typed.

Fields can also contain newlines, quotes escaped as "", and there is no single agreed standard. Use the csv module. It handles all of it.

Reading

import csv

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

['name', 'age', 'city']
['Priya', '28', 'Pune']
['Arjun', '34', 'Delhi']

Each row is a list of strings. Note the header arrives as an ordinary row — skip it if you do not want it:

    reader = csv.reader(file)
    next(reader)                  # discard the header
    for row in reader:
        ...

Two details that matter

newline="" is required. Not optional, not stylistic. Without it, fields containing line breaks are read incorrectly on some platforms and you may get blank rows between every row on Windows. The csv module handles line endings itself and needs Python not to interfere.

Every value is a string. '28', not 28. Convert what you need:

age = int(row[1])

Forgetting this is the most common CSV bug. Sorting ['10', '9', '100'] numerically without converting gives ['10', '100', '9'], because that is correct alphabetical order.

DictReader

row[1] is the positional problem from module 4 all over again. DictReader uses the header row for names:

with open("people.csv", newline="", encoding="utf-8") as file:
    reader = csv.DictReader(file)
    for row in reader:
        print(f"{row['name']} is {row['age']} and lives in {row['city']}")

Priya is 28 and lives in Pune
Arjun is 34 and lives in Delhi

The header is consumed automatically. row['name'] survives somebody inserting a column; row[1] does not.

Use DictReader by default. Positional reader is for files with no header or where you genuinely want raw rows.

Writing

rows = [
    ["name", "age", "city"],
    ["Priya", 28, "Pune"],
    ["Arjun", 34, "Delhi"],
]

with open("output.csv", "w", newline="", encoding="utf-8") as file:
    writer = csv.writer(file)
    writer.writerows(rows)

writerow for one, writerows for many. Quoting and escaping are handled — a value containing a comma comes out correctly quoted, which hand-built CSV gets wrong.

With dictionaries:

people = [
    {"name": "Priya", "age": 28, "city": "Pune"},
    {"name": "Arjun", "age": 34, "city": "Delhi"},
]

with open("output.csv", "w", newline="", encoding="utf-8") as file:
    writer = csv.DictWriter(file, fieldnames=["name", "age", "city"])
    writer.writeheader()
    writer.writerows(people)

fieldnames sets both the header and the column order. A dictionary with an extra key raises ValueError unless you pass extrasaction="ignore"; a missing key writes empty unless you set restval.

Other separators

Despite the name, CSV files are not always comma-separated. Tabs and semicolons are common — semicolons especially in countries where the comma is the decimal separator.

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

If a file reads as one enormous field per row, the delimiter is wrong.

Real-world messiness

Files from the outside world are rarely clean. The usual problems:

Whitespace around values. " Priya ". Strip on the way in.

Empty rows, especially at the end:

for row in reader:
    if not any(row):
        continue

A byte order mark. Files exported from Excel often begin with an invisible marker, so your first column is named '\ufeffname' rather than 'name' and every lookup fails. The fix is one word:

with open("export.csv", newline="", encoding="utf-8-sig") as file:

utf-8-sig strips it if present and behaves like utf-8 otherwise. When a CSV came from Excel, use it by default — this bug costs people hours and the symptom is baffling.

Encoding that is not UTF-8. Older Windows exports are often cp1252 or latin-1. If you get UnicodeDecodeError, that is the likely cause; try encoding="cp1252".

Numbers with commas or currency symbols. "₹1,25,000" will not convert. Clean it:

value = int(raw.replace("₹", "").replace(",", "").strip())

A worked example

Reading a file, filtering, computing, and writing a report — with the error handling from module 6:

import csv

results = []

with open("sales.csv", newline="", encoding="utf-8-sig") as file:
    for line_number, row in enumerate(csv.DictReader(file), start=2):
        try:
            quantity = int(row["quantity"])
            price = float(row["price"])
        except (KeyError, ValueError) as error:
            print(f"Skipping line {line_number}: {error}")
            continue

        results.append({
            "product": row["product"].strip(),
            "total": quantity * price,
        })

results.sort(key=lambda item: item["total"], reverse=True)

with open("report.csv", "w", newline="", encoding="utf-8") as file:
    writer = csv.DictWriter(file, fieldnames=["product", "total"])
    writer.writeheader()
    writer.writerows(results)

print(f"Wrote {len(results)} rows.")

Points worth noticing:

• start=2 in enumerate so reported line numbers match what the user sees in Excel, where row 1 is the header.
• One bad row is skipped with a message rather than killing the run. For a thousand-row import, that is the difference between a usable tool and a frustrating one.
• utf-8-sig because the file came from a spreadsheet.

When to stop using the csv module

For anything analytical — grouping, joining files, statistics — pandas does in one line what takes twenty here:

import pandas as pd
df = pd.read_csv("sales.csv")

It is a large dependency and worth it when you are doing data work. For reading a file, transforming rows and writing one back, the standard library is lighter and has no install step. Know both exist.

Practice

1. Create people.csv with a header and three rows. Read it with csv.reader and print each row.
2. Read it again with DictReader and print a formatted sentence per person.
3. Confirm the ages are strings. Sum them without converting and see what happens.
4. Write a new CSV from a list of lists, including a value containing a comma. Open the file and check the quoting.
5. Write one from a list of dictionaries with DictWriter.
6. Omit newline="" on Windows, or read a file with a quoted newline, and observe the difference.
7. Make a tab-separated file and read it with the right delimiter. Then read it with the wrong one.
8. Add a blank row and a row with a non-numeric age. Make your reader skip both with a clear message rather than crashing.
9. Save a file from Excel or LibreOffice as CSV, read it with utf-8, and see whether you hit the byte order mark. Fix it with utf-8-sig.
10. Write a program that reads a CSV, filters rows by a condition, and writes the matches to a new file.

Next: file paths, and why building them with string concatenation eventually breaks.