Reading and Writing CSV Files in Python

CSV (comma-separated values) is the universal exchange format for tabular data: exports from Excel and Google Sheets, bank statements, database dumps, reports. Python's built-in csv module reads and writes CSV correctly — handling quoted fields, commas inside values, and different delimiters — which naive split(",") code gets wrong.

This lesson covers csv.reader and csv.writer, the dictionary-based DictReader and DictWriter, delimiters and quoting, encodings for Excel, processing large files, and when to use pandas instead.

reader and writer

Open the file with newline="" (required by the csv module to handle line endings correctly) and an explicit encoding. csv.reader(f) yields each row as a list of strings; csv.writer(f) writes rows with writerow() and writerows(), quoting values that contain commas or quotes automatically. Remember that all values come back as strings — convert numbers yourself.

DictReader and DictWriter

DictReader uses the header row as keys, giving you row["email"] instead of row[2] — robust against column reordering. DictWriter(f, fieldnames=[...]) writes dictionaries; call writeheader() first. extrasaction="ignore" skips dict keys that are not columns.

Dialects, Delimiters and Encodings

Use delimiter=";" or "\t" for semicolon- or tab-separated files, and quoting=csv.QUOTE_ALL or QUOTE_NONNUMERIC to control quoting. Excel on Windows opens UTF-8 CSV files correctly when written with encoding="utf-8-sig" (UTF-8 with a BOM) — important for names and the ₹ symbol.

Large Files and pandas

The csv module streams row by row, so it handles files larger than memory. For analysis — grouping, filtering, joining — pandas.read_csv() is more convenient (see the pandas lesson).

Examples

Writing and reading with csv.writer and csv.reader

Python

import csv

rows = [
    ["name", "city", "marks"],
    ["Asha", "Pune", 91],
    ["Ravi Kumar", "New Delhi, India", 72],
    ['Meera "M"', "Mumbai", 88],
]
with open("students.csv", "w", newline="", encoding="utf-8") as f:
    csv.writer(f).writerows(rows)

print(open("students.csv", encoding="utf-8").read())

with open("students.csv", newline="", encoding="utf-8") as f:
    reader = csv.reader(f)
    header = next(reader)
    for name, city, marks in reader:
        print(f"{name:<12} {city:<18} {int(marks) + 5}")

Output

name,city,marks
Asha,Pune,91
Ravi Kumar,"New Delhi, India",72
"Meera ""M""",Mumbai,88

Asha         Pune               96
Ravi Kumar   New Delhi, India   77
Meera "M"    Mumbai             93

DictReader and DictWriter

Python

import csv

orders = [
    {"order_id": "WN-1", "customer": "Asha", "amount": 2999, "internal_note": "vip"},
    {"order_id": "WN-2", "customer": "Ravi", "amount": 499, "internal_note": ""},
]
with open("orders.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=["order_id", "customer", "amount"], extrasaction="ignore")
    writer.writeheader()
    writer.writerows(orders)

with open("orders.csv", newline="", encoding="utf-8") as f:
    reader = csv.DictReader(f)
    print(reader.fieldnames)
    total = 0
    for row in reader:
        total += int(row["amount"])
        print(row["order_id"], row["customer"])
print("total:", total)

Output

['order_id', 'customer', 'amount']
WN-1 Asha
WN-2 Ravi
total: 3498

Semicolons, tabs, quoting and Excel-friendly UTF-8

Python

import csv

with open("prices_eu.csv", "w", newline="", encoding="utf-8") as f:
    w = csv.writer(f, delimiter=";", quoting=csv.QUOTE_NONNUMERIC)
    w.writerow(["item", "price"])
    w.writerow(["Kaffee", 3.5])
print(open("prices_eu.csv", encoding="utf-8").read().strip())

with open("report_excel.csv", "w", newline="", encoding="utf-8-sig") as f:
    csv.writer(f).writerow(["Course", "Price"])
with open("report_excel.csv", "rb") as f:
    print(f.read()[:3])

tsv = "name\tmarks\nAsha\t91\n"
print(list(csv.reader(tsv.splitlines(), delimiter="\t")))

Output

"item";"price"
"Kaffee";3.5
b'\xef\xbb\xbf'
[['name', 'marks'], ['Asha', '91']]

Common Mistakes

Splitting lines on "," manually, which breaks on quoted fields containing commas.
Opening files without newline="", producing blank lines between rows on Windows.
Forgetting that every value read from CSV is a string.
Writing UTF-8 without a BOM and seeing garbled characters when opening in Excel.

Key Points to Remember

Open CSV files with newline="" and an explicit encoding.
csv.reader/writer handle quoting and embedded commas correctly.
DictReader/DictWriter work with column names instead of positions.
Set delimiter and quoting for other formats; utf-8-sig for Excel.
Use pandas.read_csv for analysis-heavy work.

Practice the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

Try Writing and reading with csv.writer and csv.reader in Webnest Codelab Try DictReader and DictWriter in Webnest Codelab Try Semicolons, tabs, quoting and Excel-friendly UTF-8 in Webnest Codelab