Python CSV Files: Read and Write with the csv Module

Python with AI Tutorial · Chapter 31 of 48

Python CSV handling uses the built-in csv module to read and write comma-separated files, the plain-text format that Excel, Google Sheets, banks and almost every business system can export. reader and writer work with lists, DictReader and DictWriter work with dictionaries keyed by column name, and dialect options handle semicolons, quotes and other regional quirks.

CSV looks trivial, just values separated by commas, but customer names contain commas, notes contain quotes, European exports use semicolons and Excel adds a hidden byte-order mark. The csv module deals with all of that so you never have to split strings by hand. For heavy analysis you will move to pandas later in the course; for clean imports and exports, csv is lighter and always available.

Writing a CSV file with csv.writer

Open the file in "w" mode with newline="" and hand the file object to csv.writer. writerow writes one list as one line; writerows writes many. Numbers are converted to text automatically.

import csv
from pathlib import Path

rows = [
    ["invoice_no", "customer", "amount", "status"],
    ["INV-101", "Acme Ltd", 4599.00, "Paid"],
    ["INV-102", "Zen Foods", 1250.75, "Pending"],
    ["INV-103", "Bright Labs", 980.00, "Paid"],
]

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

print(Path("invoices.csv").read_text(encoding="utf-8"), end="")

Output:
invoice_no,customer,amount,status
INV-101,Acme Ltd,4599.0,Paid
INV-102,Zen Foods,1250.75,Pending
INV-103,Bright Labs,980.0,Paid

Reading with csv.reader

csv.reader yields each line as a list of strings. Call next() once to pull off the header row, then loop over the data. Everything comes back as text, including numbers, so convert with float() or int() before doing arithmetic.

import csv

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

print(type(row[2]))

Output:
['invoice_no', 'customer', 'amount', 'status']
['INV-101', 'Acme Ltd', '4599.0', 'Paid']
['INV-102', 'Zen Foods', '1250.75', 'Pending']
['INV-103', 'Bright Labs', '980.0', 'Paid']
<class 'str'>

Reading by column name with DictReader

DictReader uses the first row as keys, so you write row["amount"] instead of row[2]. Code stays readable, and it keeps working when someone inserts a new column in the middle of the sheet.

import csv

paid_total = 0.0
pending = []

with open("invoices.csv", newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        amount = float(row["amount"])
        if row["status"] == "Paid":
            paid_total += amount
        else:
            pending.append(row["invoice_no"])

print("Paid total:", paid_total)
print("Pending:", pending)

Output:
Paid total: 5579.0
Pending: ['INV-102']

Writing dictionaries with DictWriter

When your data is already a list of dictionaries, which is how JSON and database rows usually arrive, DictWriter maps keys to columns. fieldnames controls the column order and writeheader writes the first row.

import csv
from pathlib import Path

employees = [
    {"name": "Priya Sharma", "dept": "Finance", "salary": 85000},
    {"name": "Rahul Verma", "dept": "Sales", "salary": 62000},
    {"name": "Anita Desai", "dept": "Sales", "salary": 71000},
]

with open("employees.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=["name", "dept", "salary"])
    writer.writeheader()
    writer.writerows(employees)

print(Path("employees.csv").read_text(encoding="utf-8"), end="")

Output:
name,dept,salary
Priya Sharma,Finance,85000
Rahul Verma,Sales,62000
Anita Desai,Sales,71000

Summarising a CSV by group

A defaultdict is the simplest way to total and count by category while reading. This is the same result as an Excel pivot table, written in a dozen lines and repeatable every month.

import csv
from collections import defaultdict

total = defaultdict(float)
count = defaultdict(int)

with open("employees.csv", newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        total[row["dept"]] += float(row["salary"])
        count[row["dept"]] += 1

for dept in sorted(total):
    print(f"{dept:<8} {count[dept]} staff  avg {total[dept] / count[dept]:,.0f}")

Output:
Finance  1 staff  avg 85,000
Sales    2 staff  avg 66,500

Delimiters and quoting

Excel in many European locales exports with semicolons because the comma is the decimal separator. Values containing the delimiter, a quote or a newline are wrapped in quotes automatically, and quotes inside a value are doubled. Pass the same delimiter when reading the file back.

import csv
from pathlib import Path

rows = [
    ["product", "price", "note"],
    ["Laptop, 15 inch", "64.999,00", 'Says "premium"'],
]

with open("catalog_de.csv", "w", newline="", encoding="utf-8") as f:
    csv.writer(f, delimiter=";").writerows(rows)

print(Path("catalog_de.csv").read_text(encoding="utf-8"), end="")

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

Output:
product;price;note
Laptop, 15 inch;64.999,00;"Says ""premium"""
['product', 'price', 'note']
['Laptop, 15 inch', '64.999,00', 'Says "premium"']

Detecting the format and keeping Excel happy

csv.Sniffer inspects a sample of the file and guesses the dialect, which is useful when files arrive from many sources. Going the other way, write with encoding="utf-8-sig" so Excel recognises non-English characters when a user double-clicks the file.

import csv
from pathlib import Path

sample = Path("catalog_de.csv").read_text(encoding="utf-8")[:1024]
dialect = csv.Sniffer().sniff(sample)
print("Delimiter detected:", repr(dialect.delimiter))

with open("for_excel.csv", "w", newline="", encoding="utf-8-sig") as f:
    writer = csv.writer(f)
    writer.writerow(["city", "revenue"])
    writer.writerow(["Zürich", 12500])

print(Path("for_excel.csv").read_bytes()[:3])

Output:
Delimiter detected: ';'
b'\xef\xbb\xbf'

Those three bytes are the byte-order mark. When reading a file that Excel produced, open it with encoding="utf-8-sig" as well; otherwise the first column name arrives as invoice_no and row["invoice_no"] raises KeyError.

Filtering one CSV into another

Reading and writing in the same with statement is the standard pattern for filtering, cleaning or reordering columns. reader.fieldnames gives you the original header so the output matches the input.

import csv

with open("invoices.csv", newline="", encoding="utf-8") as src, \
     open("pending.csv", "w", newline="", encoding="utf-8") as dst:
    reader = csv.DictReader(src)
    writer = csv.DictWriter(dst, fieldnames=reader.fieldnames)
    writer.writeheader()
    kept = 0
    for row in reader:
        if row["status"] == "Pending":
            writer.writerow(row)
            kept += 1

print(kept, "pending row(s) written")

Output:
1 pending row(s) written

I export a CSV from my accounting software every week. It uses semicolons, the amounts look like "1.25.000,50" (Indian grouping with a comma decimal), dates are dd-mm-yyyy and the header row is on line 3 after two title lines. Write Python with the csv module that reads it, converts amount to float and date to datetime.date, skips rows where amount is blank, and writes a clean comma-separated UTF-8 file with ISO dates. Show how to test it on 5 sample lines.

Write a Python script using pathlib and csv that finds all files matching sales_2026-*.csv in a folder, checks that every file has the same header, appends all their rows into combined_2026.csv with an extra source_file column, and prints how many rows came from each file. Use DictReader and DictWriter and open every file with newline="".

Common mistakes

• Forgetting newline="", which produces blank lines between records on Windows.
• Doing maths on the strings a reader returns. Convert with float() or int() first.
• Splitting lines with line.split(",") instead of using csv.reader; the first customer named “Sharma, Priya” breaks it.
• Reading an Excel-generated file with plain utf-8, leaving a  prefix on the first column name.
• Writing with the wrong delimiter for the reader’s locale, so Excel shows everything in one column.

Exercise

Using employees.csv from above, write salary_bands.csv with columns name, dept and band, where band is “Senior” for salaries of 70,000 or more and “Junior” otherwise.

import csv

with open("employees.csv", newline="", encoding="utf-8") as src, \
     open("salary_bands.csv", "w", newline="", encoding="utf-8") as dst:
    writer = csv.DictWriter(dst, fieldnames=["name", "dept", "band"])
    writer.writeheader()
    for row in csv.DictReader(src):
        band = "Senior" if int(row["salary"]) >= 70000 else "Junior"
        writer.writerow({"name": row["name"], "dept": row["dept"], "band": band})

# salary_bands.csv:
# name,dept,band
# Priya Sharma,Finance,Senior
# Rahul Verma,Sales,Junior
# Anita Desai,Sales,Senior

Related chapters

• Python File Handling
• Python Dictionaries
• Python Pandas with Excel
• Python with AI course hub

FAQ

Why does my CSV have blank lines between rows on Windows?

The file was opened without newline=””, so Python and the csv module both translated line endings. Always pass newline=”” to open() when working with csv.

Should I use the csv module or pandas to read CSV files?

Use csv for imports, exports and row-by-row processing with no extra installs. Use pandas when you need filtering, grouping, joins or Excel output on larger tables.

How do I read a CSV file into a list of dictionaries in Python?

Open the file with newline=”” and call list(csv.DictReader(f)). Each row becomes a dictionary whose keys are the header names and whose values are strings.

Working with spreadsheets too? Ready-made Excel, Google Sheets and Power BI templates are at NextGenTemplates.com.

Chapter 31 of 48 · Python with AI: all 48 chapters