CSV Files

Intermediate ~30 min read

CSV (Comma-Separated Values) is one of the most common data formats - every spreadsheet application can export to it, databases can import it, and it's human-readable. Python's built-in csv module handles all the tricky parts: quoting, escaping, different delimiters, and headers. Whether you're processing financial data, user records, or scientific measurements, mastering CSV is essential!

Reading CSV Files

The csv.reader() returns an iterator that yields each row as a list of strings. For more convenient access by column names, csv.DictReader() yields each row as a dictionary with column headers as keys. This makes code more readable and maintainable.

# Reading CSV Files

import csv
import os

# Create a sample CSV file first
sample_data = """name,age,city,salary
Alice,30,New York,75000
Bob,25,Los Angeles,65000
Charlie,35,Chicago,85000
Diana,28,Houston,70000"""

with open("employees.csv", "w") as f:
    f.write(sample_data)

# 1. Basic reading with csv.reader
print("=== csv.reader() - Basic ===")
with open("employees.csv", "r") as f:
    reader = csv.reader(f)
    for row in reader:
        print(row)  # Each row is a list
print()

# 2. Skip header row
print("=== Skip Header ===")
with open("employees.csv", "r") as f:
    reader = csv.reader(f)
    header = next(reader)  # Get and skip header
    print(f"Header: {header}")
    print("Data:")
    for row in reader:
        print(f"  {row[0]} is {row[1]} years old")
print()

# 3. Using DictReader - rows as dictionaries
print("=== csv.DictReader() ===")
with open("employees.csv", "r") as f:
    reader = csv.DictReader(f)
    for row in reader:
        print(f"{row['name']}: ${row['salary']} in {row['city']}")
print()

# 4. DictReader with custom fieldnames
print("=== DictReader with Custom Fields ===")
# CSV without header
no_header = """Alice,30,NY
Bob,25,LA"""
with open("no_header.csv", "w") as f:
    f.write(no_header)

with open("no_header.csv", "r") as f:
    reader = csv.DictReader(f, fieldnames=["name", "age", "city"])
    for row in reader:
        print(row)
print()

# 5. Reading into a list
print("=== Read All into List ===")
with open("employees.csv", "r") as f:
    reader = csv.reader(f)
    all_rows = list(reader)
    print(f"Total rows: {len(all_rows)}")
    print(f"Header: {all_rows[0]}")
    print(f"First data: {all_rows[1]}")
print()

# 6. Reading specific columns
print("=== Extract Specific Columns ===")
with open("employees.csv", "r") as f:
    reader = csv.DictReader(f)
    names = [row['name'] for row in reader]
    print(f"Names: {names}")

# Cleanup
os.remove("employees.csv")
os.remove("no_header.csv")
print("\n(Cleaned up CSV files)")

Output

Click Run to execute your code

reader vs DictReader:
csv.reader(f) - Rows as lists: row[0], row[1]
csv.DictReader(f) - Rows as dicts: row['name'], row['age']
Use reader for simple, positional access.
Use DictReader when columns have meaning - code is clearer!

Writing CSV Files

Use csv.writer() to write lists of values, and csv.DictWriter() to write dictionaries. Always open files with newline="" on Windows to prevent blank lines between rows. The writeheader() method writes column names.

# Writing CSV Files

import csv
import os

# 1. Basic writing with csv.writer
print("=== csv.writer() - Basic ===")
data = [
    ["name", "age", "city"],
    ["Alice", 30, "New York"],
    ["Bob", 25, "Los Angeles"],
    ["Charlie", 35, "Chicago"]
]

with open("output.csv", "w", newline="") as f:
    writer = csv.writer(f)
    for row in data:
        writer.writerow(row)

# Read back
with open("output.csv", "r") as f:
    print(f.read())
print()

# 2. writerows() - write multiple at once
print("=== writerows() - Batch Write ===")
with open("batch.csv", "w", newline="") as f:
    writer = csv.writer(f)
    writer.writerows(data)  # Write all at once

with open("batch.csv", "r") as f:
    print(f.read())
print()

# 3. DictWriter - write from dictionaries
print("=== csv.DictWriter() ===")
employees = [
    {"name": "Alice", "department": "Engineering", "salary": 75000},
    {"name": "Bob", "department": "Marketing", "salary": 65000},
    {"name": "Charlie", "department": "Engineering", "salary": 85000}
]

with open("employees.csv", "w", newline="") as f:
    fieldnames = ["name", "department", "salary"]
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    writer.writeheader()  # Write column names
    writer.writerows(employees)

with open("employees.csv", "r") as f:
    print(f.read())
print()

# 4. Appending to existing CSV
print("=== Append to CSV ===")
new_employee = {"name": "Diana", "department": "HR", "salary": 70000}

with open("employees.csv", "a", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=["name", "department", "salary"])
    writer.writerow(new_employee)

with open("employees.csv", "r") as f:
    print(f.read())
print()

# 5. Writing with different delimiter
print("=== Custom Delimiter (Tab) ===")
with open("tabbed.tsv", "w", newline="") as f:
    writer = csv.writer(f, delimiter="\t")
    writer.writerows(data)

with open("tabbed.tsv", "r") as f:
    print(f.read())

# Cleanup
for fname in ["output.csv", "batch.csv", "employees.csv", "tabbed.tsv"]:
    os.remove(fname)
print("(Cleaned up CSV files)")

Output

Click Run to execute your code

Windows Users! Always use newline="" when opening CSV files for writing: open("file.csv", "w", newline=""). Without this, you'll get extra blank lines between rows. This is because the csv module handles line endings itself.

Dialects and Options

Not all CSV files use commas! European files often use semicolons, tab-separated values (TSV) are common, and different applications quote fields differently. The csv module supports various dialects and lets you customize delimiter, quoting, and other options.

# CSV Dialects and Options

import csv
import os

# 1. Different delimiters
print("=== Delimiter Options ===")

# Semicolon separated (common in Europe)
data = [["Name", "Price", "Quantity"], ["Apple", "1.50", "10"]]
with open("semicolon.csv", "w", newline="") as f:
    writer = csv.writer(f, delimiter=";")
    writer.writerows(data)

with open("semicolon.csv", "r") as f:
    print(f"Semicolon: {f.read().strip()}")

# Tab separated (TSV)
with open("tabs.tsv", "w", newline="") as f:
    writer = csv.writer(f, delimiter="\t")
    writer.writerows(data)

with open("tabs.tsv", "r") as f:
    print(f"Tab: {f.read().strip()}")
print()

# 2. Quoting options
print("=== Quoting Options ===")
tricky_data = [
    ["name", "description", "price"],
    ["Widget", "A great, useful item", "9.99"],
    ["Gadget", 'Has "special" features', "19.99"],
    ["Thing", "Contains, commas, inside", "5.00"]
]

# QUOTE_MINIMAL (default) - only quote when necessary
with open("quote_minimal.csv", "w", newline="") as f:
    writer = csv.writer(f, quoting=csv.QUOTE_MINIMAL)
    writer.writerows(tricky_data)

print("QUOTE_MINIMAL:")
with open("quote_minimal.csv", "r") as f:
    print(f.read())

# QUOTE_ALL - quote everything
with open("quote_all.csv", "w", newline="") as f:
    writer = csv.writer(f, quoting=csv.QUOTE_ALL)
    writer.writerows(tricky_data)

print("QUOTE_ALL:")
with open("quote_all.csv", "r") as f:
    print(f.read())

# QUOTE_NONNUMERIC - quote strings only
with open("quote_nonnumeric.csv", "w", newline="") as f:
    writer = csv.writer(f, quoting=csv.QUOTE_NONNUMERIC)
    writer.writerows(tricky_data)

print("QUOTE_NONNUMERIC:")
with open("quote_nonnumeric.csv", "r") as f:
    print(f.read())

# 3. Excel dialect
print("=== Excel Dialect ===")
with open("excel.csv", "w", newline="") as f:
    writer = csv.writer(f, dialect="excel")
    writer.writerows(tricky_data)

with open("excel.csv", "r") as f:
    reader = csv.reader(f, dialect="excel")
    for row in reader:
        print(row)
print()

# 4. Custom dialect
print("=== Custom Dialect ===")
csv.register_dialect(
    "custom",
    delimiter="|",
    quoting=csv.QUOTE_ALL,
    quotechar="'",
    lineterminator="\n"
)

with open("custom.csv", "w", newline="") as f:
    writer = csv.writer(f, dialect="custom")
    writer.writerows([["A", "B"], ["1", "2"]])

with open("custom.csv", "r") as f:
    print(f"Custom format: {f.read().strip()}")
print()

# 5. List available dialects
print("=== Available Dialects ===")
print(f"Dialects: {csv.list_dialects()}")

# Cleanup
for fname in ["semicolon.csv", "tabs.tsv", "quote_minimal.csv",
              "quote_all.csv", "quote_nonnumeric.csv", "excel.csv", "custom.csv"]:
    if os.path.exists(fname):
        os.remove(fname)
print("\n(Cleaned up files)")

Output

Click Run to execute your code

Quoting Constants:
csv.QUOTE_MINIMAL - Only quote when needed (default)
csv.QUOTE_ALL - Quote all fields
csv.QUOTE_NONNUMERIC - Quote non-numeric fields
csv.QUOTE_NONE - Never quote (escape special chars)
Use QUOTE_ALL for maximum compatibility with other tools.

Practical Examples

In real projects, you'll process CSV data: calculate totals, filter rows, group by categories, add computed columns, and transform data. The csv module combines well with Python's built-in functions and collections for powerful data processing.

# Practical CSV Examples

import csv
import os
from collections import defaultdict

# Create sample sales data
sales_data = """date,product,quantity,price,region
2024-01-15,Widget,10,9.99,North
2024-01-15,Gadget,5,19.99,South
2024-01-16,Widget,8,9.99,North
2024-01-16,Gadget,12,19.99,East
2024-01-17,Widget,15,9.99,South
2024-01-17,Thing,20,5.00,North
2024-01-18,Gadget,7,19.99,West
2024-01-18,Thing,25,5.00,East"""

with open("sales.csv", "w") as f:
    f.write(sales_data)

# 1. Calculate totals
print("=== Calculate Revenue by Product ===")
revenue = defaultdict(float)

with open("sales.csv", "r") as f:
    reader = csv.DictReader(f)
    for row in reader:
        product = row["product"]
        total = int(row["quantity"]) * float(row["price"])
        revenue[product] += total

for product, total in sorted(revenue.items(), key=lambda x: -x[1]):
    print(f"  {product}: ${total:.2f}")
print()

# 2. Filter and transform
print("=== Filter: High-Volume Sales ===")
high_volume = []

with open("sales.csv", "r") as f:
    reader = csv.DictReader(f)
    for row in reader:
        if int(row["quantity"]) >= 10:
            high_volume.append(row)

print(f"Found {len(high_volume)} high-volume sales:")
for sale in high_volume:
    print(f"  {sale['date']}: {sale['quantity']} x {sale['product']}")
print()

# 3. Group by region
print("=== Sales by Region ===")
by_region = defaultdict(list)

with open("sales.csv", "r") as f:
    reader = csv.DictReader(f)
    for row in reader:
        by_region[row["region"]].append(row)

for region, sales in sorted(by_region.items()):
    total_qty = sum(int(s["quantity"]) for s in sales)
    print(f"  {region}: {total_qty} units ({len(sales)} orders)")
print()

# 4. Add calculated column and write new file
print("=== Add Calculated Column ===")
with open("sales.csv", "r") as infile, \
     open("sales_with_total.csv", "w", newline="") as outfile:

reader = csv.DictReader(infile)
    fieldnames = reader.fieldnames + ["total"]
    writer = csv.DictWriter(outfile, fieldnames=fieldnames)

writer.writeheader()
    for row in reader:
        row["total"] = f"{int(row['quantity']) * float(row['price']):.2f}"
        writer.writerow(row)

# Show result
with open("sales_with_total.csv", "r") as f:
    print(f.read())
print()

# 5. Convert CSV to list of objects
print("=== CSV to Objects ===")
class Sale:
    def __init__(self, date, product, quantity, price, region):
        self.date = date
        self.product = product
        self.quantity = int(quantity)
        self.price = float(price)
        self.region = region

def revenue(self):
        return self.quantity * self.price

def __repr__(self):
        return f"Sale({self.product}, {self.quantity}x${self.price})"

sales = []
with open("sales.csv", "r") as f:
    reader = csv.DictReader(f)
    for row in reader:
        sales.append(Sale(**row))

print(f"Loaded {len(sales)} sales")
print(f"First sale: {sales[0]}")
print(f"Total revenue: ${sum(s.revenue() for s in sales):.2f}")

# Cleanup
os.remove("sales.csv")
os.remove("sales_with_total.csv")
print("\n(Cleaned up files)")

Output

Click Run to execute your code

Common Mistakes

1. Forgetting newline="" on Windows

# Wrong - creates extra blank lines on Windows!
with open("data.csv", "w") as f:
    writer = csv.writer(f)
    writer.writerows(data)

# Correct - always use newline=""
with open("data.csv", "w", newline="") as f:
    writer = csv.writer(f)
    writer.writerows(data)

2. Not handling commas in data

# Wrong - manual string building breaks on commas!
data = ["Alice", "New York, NY", "30"]
line = ",".join(data)  # "Alice,New York, NY,30" - 4 fields!

# Correct - let csv module handle quoting
with open("data.csv", "w", newline="") as f:
    writer = csv.writer(f)
    writer.writerow(data)  # "Alice","New York, NY","30"

3. Assuming all fields are strings

# Wrong - CSV values are always strings!
with open("data.csv", "r") as f:
    reader = csv.DictReader(f)
    for row in reader:
        total = row['quantity'] * row['price']  # TypeError!

# Correct - convert to numbers
with open("data.csv", "r") as f:
    reader = csv.DictReader(f)
    for row in reader:
        total = int(row['quantity']) * float(row['price'])

4. Reading the same file twice

# Wrong - reader is exhausted after first loop!
with open("data.csv", "r") as f:
    reader = csv.reader(f)
    for row in reader:
        print(row)
    for row in reader:  # Empty! Iterator exhausted
        process(row)

# Correct - read into list or open again
with open("data.csv", "r") as f:
    rows = list(csv.reader(f))  # Store in memory
for row in rows:
    print(row)
for row in rows:  # Works!
    process(row)

5. DictWriter with missing keys

# Wrong - KeyError if dict missing a field!
fieldnames = ["name", "age", "city"]
data = {"name": "Alice", "age": 30}  # Missing 'city'!

with open("data.csv", "w", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    writer.writerow(data)  # KeyError: 'city'

# Correct - use extrasaction and restval
with open("data.csv", "w", newline="") as f:
    writer = csv.DictWriter(
        f, fieldnames=fieldnames,
        extrasaction='ignore',  # Ignore extra keys
        restval=""  # Default for missing keys
    )
    writer.writerow(data)  # Works!

Exercise: Sales Report Generator

Task: Process sales data and generate a summary report CSV.

Requirements:

Read a sales CSV with columns: product, quantity, price
Calculate total revenue per product
Write a summary CSV with: product, total_quantity, total_revenue
Sort by revenue (highest first)

Output

Click Run to execute your code

Show Solution

import csv
import os
from collections import defaultdict

# Create sample sales data
sales_csv = """product,quantity,price
Widget,10,9.99
Gadget,5,19.99
Widget,8,9.99
Gadget,12,19.99
Thing,20,5.00
Widget,15,9.99
Thing,25,5.00"""

with open("sales.csv", "w") as f:
    f.write(sales_csv)

# Process sales data
totals = defaultdict(lambda: {"quantity": 0, "revenue": 0.0})

with open("sales.csv", "r") as f:
    reader = csv.DictReader(f)
    for row in reader:
        product = row["product"]
        qty = int(row["quantity"])
        price = float(row["price"])
        totals[product]["quantity"] += qty
        totals[product]["revenue"] += qty * price

# Convert to sorted list (by revenue, descending)
summary = [
    {"product": p, "total_quantity": d["quantity"], "total_revenue": f"{d['revenue']:.2f}"}
    for p, d in sorted(totals.items(), key=lambda x: -x[1]["revenue"])
]

# Write summary report
with open("summary.csv", "w", newline="") as f:
    fieldnames = ["product", "total_quantity", "total_revenue"]
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(summary)

# Display result
print("=== Sales Summary ===")
with open("summary.csv", "r") as f:
    print(f.read())

# Cleanup
os.remove("sales.csv")
os.remove("summary.csv")

Summary

Read with reader: csv.reader(f) - rows as lists
Read with DictReader: csv.DictReader(f) - rows as dicts
Write with writer: csv.writer(f) with writerow()
Write with DictWriter: csv.DictWriter(f, fieldnames=[])
Write header: writer.writeheader()
Batch write: writer.writerows(data)
Custom delimiter: delimiter=";" or delimiter="\t"
Windows fix: Always use newline="" when writing
Quoting: quoting=csv.QUOTE_ALL for safety
Convert types: CSV values are strings - convert with int/float

What's Next?

CSV is great for tabular data, but for more complex structures you'll need JSON. JSON supports nested objects, arrays, and multiple data types - perfect for configuration files, API responses, and hierarchical data. Let's learn how to read and write JSON with Python!

Previous Working with Paths

Next JSON Files

Reading CSV Files

Writing CSV Files

Dialects and Options

Practical Examples

Common Mistakes

1. Forgetting newline="" on Windows

2. Not handling commas in data

3. Assuming all fields are strings

4. Reading the same file twice

5. DictWriter with missing keys

Exercise: Sales Report Generator

Summary

What's Next?

Enjoying these tutorials?