Reading CSV & Excel Files

Reviewed & published by Brayan K

Spreadsheets run the business world — sales exports, customer lists, financial reports. Python lets you read, transform, and write that tabular data automatically, turning hours of manual spreadsheet work into a script that runs in seconds.

Part of the free Python course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

You'll start with the built-in csv module, then meet openpyxl and pandas for real Excel files.

What You'll Learn in This Lesson

• Reading CSV with csv.reader and csv.DictReader
• Writing CSV with csv.writer and DictWriter
• Why newline='' matters when opening files
• Handling headers, types, and missing values
• Reading Excel files with openpyxl
• When to switch to pandas for power

1 What a CSV File Really Is

A CSV is just a text file. If you open employees.csv in a plain editor, you'll see:

name,department,salary
Ada Lovelace,Engineering,95000
Grace Hopper,Engineering,98000
Katherine Johnson,Research,91000

The first line is usually the header (column names). Each following line is a row, with commas separating fields. That's the whole format.

2 Reading: reader vs DictReader

csv.reader gives each row as a list. csv.DictReader uses the header row to give each row as a dict — usually much clearer:

import csv

# --- reader: rows are lists (access by index) ---
with open("employees.csv", newline="") as f:
    reader = csv.reader(f)
    header = next(reader)          # skip/grab the first row
    print(header)                  # ['name', 'department', 'salary']
    for row in reader:
        print(row[0], "earns", row[2])
        # Ada Lovelace earns 95000

# --- DictReader: rows are dicts keyed by header (clearer!) ---
with open("employees.csv", newline="") as f:
    for row in csv.DictReader(f):
        print(row["name"], "->", row["department"])
        # Ada Lovelace -> Engineering

✍️ Writing CSV Files

Writing mirrors reading. Use writer for lists or DictWriter for dicts. Remember newline="" here too:

import csv

rows = [
    {"name": "Ada", "score": 95},
    {"name": "Grace", "score": 98},
    {"name": "Katherine", "score": 91},
]

with open("scores.csv", "w", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=["name", "score"])
    writer.writeheader()          # writes: name,score
    writer.writerows(rows)        # writes all three data rows

# scores.csv now contains:
# name,score
# Ada,95
# Grace,98
# Katherine,91

writeheader() writes the column names; writerow() writes one row; writerows() writes a whole list at once.

import csv, io

# We simulate a CSV file using io.StringIO so this runs without a real file.
csv_text = """name,department,salary
Ada Lovelace,Engineering,95000
Grace Hopper,Engineering,98000
Katherine Johnson,Research,91000
Dorothy Vaughan,Research,88000"""

# DictReader works on any file-like object, including StringIO
reader = csv.DictReader(io.StringIO(csv_text))

total = 0
count = 0
for row in reader:
    salary = int(row["salary"])     # remember: convert from string!
    total += salary
    count += 1
    print(f'{row["name"]:20} {row["department"]:12} ', salary)

print("-" * 45)
print(f"Average salary: ", round(total / count))
# Average salary:  93000

# ✅ Expected output:
# Ada Lovelace         Engineering   95000
# Grace Hopper         Engineering   98000
# Katherine Johnson    Research      91000
# Dorothy Vaughan      Research      88000
# ---------------------------------------------
# Average salary:  93000

3 Reading Real Excel Files with openpyxl

The csv module can't read .xlsx files — those are zipped XML, not text. The openpyxl library handles them:

An Excel workbook contains one or more worksheets, each a grid of cells addressed like A1, B2. iter_rows(values_only=True) is the cleanest way to loop.

🐼 The Power Tool: pandas

When you need filtering, grouping, or statistics, pandas reads CSV and Excel into a DataFrame — a spreadsheet you can program against:

# pip install pandas openpyxl
import pandas as pd

df = pd.read_csv("employees.csv")     # or pd.read_excel("sales.xlsx")

print(df.head())                       # first 5 rows, nicely formatted
print(df["salary"].mean())             # average of a whole column
print(df.shape)                        # (rows, columns) e.g. (4, 3)

# Filtering reads like a sentence:
engineers = df[df["department"] == "Engineering"]
print(engineers["name"].tolist())      # ['Ada Lovelace', 'Grace Hopper']

# Group and aggregate in one line:
print(df.groupby("department")["salary"].mean())
# department
# Engineering    96500.0
# Research        89500.0

# Write back out
df.to_csv("clean.csv", index=False)
df.to_excel("report.xlsx", index=False)

Real-World Example: Cleaning a Messy Sales Export

A classic task: read a raw export, fix the data, write a clean version. This runs with the built-in csv module — no extra installs:

import csv, io

raw = """product,price,qty
Widget, 9.99 ,3
Gadget,19.5,
Gizmo, 4.00 ,10"""

reader = csv.DictReader(io.StringIO(raw))
cleaned = []

for row in reader:
    name = row["product"].strip()
    price = float(row["price"].strip())          # strip spaces, convert
    qty = int(row["qty"]) if row["qty"].strip() else 0   # blank -> 0
    cleaned.append({
        "product": name,
        "price": price,
        "qty": qty,
        "total": round(price * qty, 2),
    })

# Print the cleaned report
print(f'{"Product":10}{"Price":>8}{"Qty":>5}{"Total":>10}')
for r in cleaned:
    print(f'{r["product"]:10}{r["price"]:>8.2f}{r["qty"]:>5}{r["total"]:>10.2f}')

grand_total = sum(r["total"] for r in cleaned)
print("-" * 33)
print(f'{"GRAND TOTAL":23}{grand_total:>10.2f}')

# Output:
# Product      Price  Qty     Total
# Widget        9.99    3     29.97
# Gadget       19.50    0      0.00
# Gizmo         4.00   10     40.00
# ---------------------------------
# GRAND TOTAL              69.97

Real data is messy: stray spaces, blank cells, numbers stored as text. The cleaning loop here (strip, convert, default missing values) is the pattern you'll use again and again.

import csv, io

# Read this CSV and find the top scorer
data = """student,subject,score
Maya,Math,88
Leo,Math,72
Maya,Science,95
Leo,Science,81
Zara,Math,90"""

reader = csv.DictReader(io.StringIO(data))

best = None
for row in reader:
    score = int(row["score"])
    if best is None or score > best["score"]:
        best = {"name": row["student"], "subject": row["subject"], "score": score}

print(f"Top score: {best['name']} got {best['score']} in {best['subject']}")
# Top score: Maya got 95 in Science

# ✅ Expected output:
# Top score: Maya got 95 in Science

# 🎯 YOUR TURN — replace each ___ using the hint beside it.

import csv
import io

# A string standing in for a file, so this runs anywhere.
raw = "title,pages,year\nDune,412,1965\nEmma,474,1815\nBee,96,2021\n"

# 1) Rows as dictionaries keyed by the header row — far easier to read
#    than csv.reader's plain lists.
reader = csv.___(io.StringIO(raw))       # 👉 replace ___ with DictReader
rows = list(reader)
print("Rows read:", len(rows))
print("Columns:", reader.fieldnames)

for row in rows:
    print(row["title"], "-", row["pages"], "pages")

# 2) Every value from a CSV is a STRING, so convert before comparing.
long_ones = [r for r in rows if ___(r["pages"]) > 100]   # 👉 replace ___ with int
print("Longer than 100:", [r["title"] for r in long_ones])

out = io.StringIO()
# 3) Writing needs the column names up front, in the order you want them.
#    lineterminator is here on purpose: csv writes CRLF (\r\n) by default,
#    because that is what RFC 4180 says. Leave it out and every line of the
#    file you produce ends in an invisible \r.
writer = csv.DictWriter(out, ___=["title", "pages"], lineterminator="\n")   # 👉 replace ___ with fieldnames

# 4) Without this the file has no header row at all.
writer.___()                             # 👉 replace ___ with writeheader
for r in long_ones:
    # 5) One dict, one row.
    writer.___({"title": r["title"], "pages": r["pages"]})   # 👉 replace ___ with writerow

print("Written back:")
print(out.getvalue().strip())

total = sum(int(r["pages"]) for r in rows)
print("Total pages:", total)

# ✅ Expected output:
# Rows read: 3
# Columns: ['title', 'pages', 'year']
# Dune - 412 pages
# Emma - 474 pages
# Bee - 96 pages
# Longer than 100: ['Dune', 'Emma']
# Written back:
# title,pages
# Dune,412
# Emma,474
# Total pages: 982

🧩 Reorder Challenge

These lines write a CSV file with a header and one data row, but they're scrambled. Find the order:

A)     writer.writeheader()
B) import csv
C)     writer = csv.DictWriter(f, fieldnames=["name", "age"])
D)     writer.writerow({"name": "Ada", "age": 36})
E) with open("out.csv", "w", newline="") as f:

Correct order: B → E → C → A → D

import csv
with open("out.csv", "w", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=["name", "age"])
    writer.writeheader()
    writer.writerow({"name": "Ada", "age": 36})

Import, open the file (note newline=""), create the writer with field names, write the header, then write the row.

🧠 Quick Recall — Predict the Output

row = {"qty": "5"}   # a value read from a CSV
print(row["qty"] + 3)

TypeError — CSV values are strings, so "5" + 3 mixes str and int. You must convert first: int(row["qty"]) + 3 gives 8.

header = ["name", "score"]
row = ["Ada", "95"]
record = dict(zip(header, row))
print(record["score"])

95 — zip pairs the header with the row and dict() builds a mapping. This is exactly what DictReader does for you under the hood.

import pandas as pd
# df has a "price" column: [10, 20, 30]
prices = [10, 20, 30]
print(sum(prices) / len(prices))   # mimics df["price"].mean()

20.0 — the average of 10, 20, 30. In pandas this is just df["price"].mean(), which is why it's so handy for analysis.

❓ Frequently Asked Questions

You can automate the spreadsheet world now!

You can read and write CSV with the built-in module, parse real Excel files with openpyxl, and unleash pandas for analysis. Cleaning a messy export by hand is now a script you run in seconds.

Practice quiz

Continue this course

• Previous: HTTP Requests & APIs
• Next: Web Scraping with BeautifulSoup — Extract data from web pages with requests and BeautifulSoup
• Quick reference: Python cheat sheet

Frequently asked questions

What is a CSV file?

CSV stands for Comma-Separated Values. It's a plain-text table where each line is a row and commas separate the columns. It's the universal export format for spreadsheets and databases.

What is the difference between csv.reader and csv.DictReader?

csv.reader gives you each row as a list of strings (indexed by position). csv.DictReader uses the header row as keys, giving each row as a dict so you can write row['name'] instead of row[0]. DictReader is usually clearer.

Why should I pass newline='' when opening a CSV file?

The csv module handles line endings itself. Opening with newline='' prevents the file object from translating newlines too, which would otherwise insert blank rows on Windows. It's the recommended way to open CSV files for reading and writing.

How do I read Excel (.xlsx) files in Python?

The standard library can't read .xlsx. Use the third-party openpyxl library (pip install openpyxl), or pandas (pip install pandas) which reads both CSV and Excel with a single read_excel / read_csv call.

When should I use pandas instead of the csv module?

Use the built-in csv module for simple row-by-row reading and writing with no dependencies. Reach for pandas when you need filtering, grouping, statistics, or to work with both CSV and Excel using the same powerful DataFrame API.