Lesson 25: Working with CSV and Excel Files

Why CSV and Excel Matter

CSV and Excel files are among the most common formats for storing structured data. Python provides powerful tools for reading, writing, and manipulating these files, making it ideal for data analysis, automation, and reporting.

Working with CSV Files

CSV (Comma-Separated Values) files store data in plain text. Python’s built‑in csv module makes them easy to handle.

Reading a CSV File

import csv

with open("data.csv") as file:
    reader = csv.reader(file)
    for row in reader:
        print(row)

Writing to a CSV File

import csv

rows = [
    ["Name", "Age"],
    ["Alice", 30],
    ["Bob", 25]
]

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

Using DictReader and DictWriter

These classes treat each row as a dictionary:

import csv

with open("data.csv") as file:
    reader = csv.DictReader(file)
    for row in reader:
        print(row["Name"], row["Age"])

Working with Excel Files

Excel files (.xlsx) require external libraries. The most common is openpyxl.

Installing openpyxl

pip install openpyxl

Reading an Excel File

from openpyxl import load_workbook

wb = load_workbook("data.xlsx")
sheet = wb.active

for row in sheet.iter_rows(values_only=True):
    print(row)

Writing to an Excel File

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

sheet["A1"] = "Name"
sheet["B1"] = "Age"
sheet.append(["Alice", 30])
sheet.append(["Bob", 25])

wb.save("output.xlsx")

Using pandas for CSV and Excel

pandas is the most powerful tool for working with tabular data.

Reading CSV

import pandas as pd

df = pd.read_csv("data.csv")
print(df)

Reading Excel

df = pd.read_excel("data.xlsx")
print(df)

Writing CSV and Excel

df.to_csv("output.csv", index=False)
df.to_excel("output.xlsx", index=False)

Why pandas Is Useful

• Handles large datasets efficiently.
• Provides powerful filtering and transformation tools.
• Supports both CSV and Excel seamlessly.
• Ideal for data analysis and automation workflows.

Next Steps

Now that you can work with CSV and Excel files, you're ready to explore data visualization in Lesson 26: Introduction to Data Visualization (matplotlib).

← Back to Lesson Index