pandas Excel and CSV Files: Read and Write Guide

Python with AI Tutorial · Chapter 39 of 48

The pandas Excel and CSV functions, read_csv(), read_excel(), to_csv() and to_excel(), move data between files and DataFrames in one line each. This chapter loads a sales file, fixes dates and types on the way in, writes a multi-sheet Excel report with ExcelWriter, combines monthly CSV exports and polishes the workbook with openpyxl.

Table of Contents

Install pandas and openpyxl

pandas reads CSV files on its own, but .xlsx files need an engine. Install openpyxl alongside pandas; it handles both reading and writing.

pip install pandas openpyxl

Output:
Successfully installed openpyxl-3.1.5 pandas-2.2.3 ...

Common error: ImportError: Missing optional dependency 'openpyxl' means pandas is installed but the Excel engine is not. Run the pip line above. Old .xls files need xlrd instead, and xlsxwriter is an alternative write-only engine with rich formatting.

Write a DataFrame to CSV and Excel

Start with the sales table from the previous chapter. index=False stops pandas writing the 0, 1, 2 row numbers as an extra column.

import pandas as pd
from pathlib import Path

sales = pd.DataFrame({
    "Region":  ["North", "South", "East", "North", "West", "South", "East", "West"],
    "Product": ["Dashboard", "Tracker", "Dashboard", "Calendar", "Tracker", "Dashboard", "Calendar", "Dashboard"],
    "Units":   [4, 10, 2, 15, 7, 3, 12, 5],
    "Revenue": [3996, 2990, 1998, 1485, 2093, 2997, 1188, 4995],
    "Date": pd.to_datetime(["2026-01-05", "2026-01-06", "2026-01-07", "2026-01-08",
                            "2026-02-02", "2026-02-03", "2026-02-04", "2026-02-05"]),
})

sales.to_csv("sales.csv", index=False)
sales.to_excel("sales.xlsx", sheet_name="Sales", index=False)

print(Path("sales.csv").read_text(encoding="utf-8").splitlines()[:3])

Output:
['Region,Product,Units,Revenue,Date', 'North,Dashboard,4,3996,2026-01-05', 'South,Tracker,10,2990,2026-01-06']

Both files now sit in your working directory. Open sales.xlsx in Excel and you will see a plain sheet named Sales with a header row and eight data rows.

Read a CSV file

read_csv() guesses column types, but it treats dates as text unless you tell it otherwise. parse_dates converts the listed columns to datetime64 on the way in.

df = pd.read_csv("sales.csv", parse_dates=["Date"])
print(df.dtypes)
print(df.shape, df["Revenue"].sum())

Output:
Region             object
Product            object
Units               int64
Revenue             int64
Date       datetime64[ns]
dtype: object
(8, 5) 21742

Real exports are messier than this one. The table lists the read_csv() options that solve the usual problems; most of them also work with read_excel().

Option	Use it when	Example
`sep`	The file uses semicolons or tabs	`sep=";"`
`encoding`	You see `UnicodeDecodeError` or odd characters	`encoding="utf-8-sig"` or `"latin-1"`
`parse_dates`	Date columns arrive as text	`parse_dates=["Date"]`
`dtype`	IDs or postcodes must stay text	`dtype={"OrderID": str}`
`usecols`	You only need some columns	`usecols=["Region", "Revenue"]`
`skiprows` / `header`	Title rows sit above the real header	`skiprows=2`
`nrows`	Preview a huge file	`nrows=1000`
`na_values`	Blanks are written as `-` or `N/A`	`na_values=["-", "N/A"]`
`thousands` / `decimal`	Numbers look like `1,234.50` or `1.234,50`	`thousands=","`

Read an Excel file

read_excel() takes the same idea further with sheet_name. Pass a name or a position, and use usecols to skip columns you do not need.

df = pd.read_excel("sales.xlsx", sheet_name="Sales", usecols=["Region", "Product", "Revenue"])
print(df.head(3))

Output:
   Region    Product  Revenue
0   North  Dashboard     3996
1   South    Tracker     2990
2    East  Dashboard     1998

Excel stores real dates as dates, so a Date column read from .xlsx arrives as datetime64 automatically; only CSV needs parse_dates.

Try it with AI

Describe the layout of a messy workbook and ask for the exact read_excel() call.

I have an Excel file report.xlsx. Sheet "Q1" has a title in row 1, a blank row 2, the real headers in row 3 (Region, Product, Units, Revenue, Date), data from row 4, and a Total row at the bottom that I do not want. Revenue is stored as text like "$3,996". Write the pandas read_excel() call and the cleaning steps to get a DataFrame with Revenue as an integer and Date as datetime, and drop the Total row safely even if its position changes.

Write a multi-sheet report with ExcelWriter

A real report has the raw data on one sheet and summaries on others. pd.ExcelWriter keeps the workbook open while you add sheets, then saves it when the with block ends.

by_region = sales.groupby("Region", as_index=False)["Revenue"].sum()
units_pivot = sales.pivot_table(index="Product", columns="Region", values="Units",
                                aggfunc="sum", fill_value=0)
print(by_region)

with pd.ExcelWriter("sales_report.xlsx", engine="openpyxl") as writer:
    sales.to_excel(writer, sheet_name="Data", index=False)
    by_region.to_excel(writer, sheet_name="By Region", index=False)
    units_pivot.to_excel(writer, sheet_name="Units Pivot")      # keep the Product index

print(pd.ExcelFile("sales_report.xlsx").sheet_names)

Output:
   Region  Revenue
0    East     3186
1   North     5481
2   South     5987
3    West     7088
['Data', 'By Region', 'Units Pivot']

For the pivot we keep the index because the Product names live there. For the other two sheets the index is just row numbers, so index=False keeps the workbook clean.

Read every sheet at once

sheet_name=None returns a dictionary of DataFrames keyed by sheet name, which is ideal for workbooks where each tab holds a month or a branch.

book = pd.read_excel("sales_report.xlsx", sheet_name=None)
for name, frame in book.items():
    print(name, frame.shape)

Output:
Data (8, 5)
By Region (4, 2)
Units Pivot (3, 5)

Combine many CSV files

Monthly exports usually arrive as separate files. Collect them with Path.glob(), read each one and stack them with pd.concat().

sales[sales["Date"].dt.month == 1].to_csv("sales_2026-01.csv", index=False)
sales[sales["Date"].dt.month == 2].to_csv("sales_2026-02.csv", index=False)

files = sorted(Path(".").glob("sales_2026-*.csv"))
combined = pd.concat((pd.read_csv(f, parse_dates=["Date"]) for f in files), ignore_index=True)

print([f.name for f in files])
print(combined.shape, combined["Units"].sum())

Output:
['sales_2026-01.csv', 'sales_2026-02.csv']
(8, 5) 58

ignore_index=True renumbers the rows 0 to 7 instead of repeating 0 to 3 twice. If the files have slightly different columns, concat aligns them by name and fills the gaps with NaN.

Format the workbook with openpyxl

pandas writes values, not formatting. Reopen the file with openpyxl to set column widths, number formats and frozen panes so the report looks finished.

from openpyxl import load_workbook

wb = load_workbook("sales_report.xlsx")
ws = wb["Data"]
ws.column_dimensions["B"].width = 14
ws.column_dimensions["E"].width = 12
for cell in ws["D"][1:]:                 # Revenue column, skip header
    cell.number_format = "#,##0"
for cell in ws["E"][1:]:                 # Date column
    cell.number_format = "yyyy-mm-dd"
ws.freeze_panes = "A2"
wb.save("sales_report.xlsx")
print(ws.max_row, ws.max_column)

Output:
9 5

Nine rows is the header plus eight records. You can go much further with openpyxl: bold headers, conditional fills, Excel tables and even native charts.

Try it with AI

Ask for a complete, reusable report script and then adapt the paths.

Write a Python script using pandas and openpyxl that: reads every CSV matching data/sales_*.csv (columns Region, Product, Units, Revenue, Date), concatenates them, adds a Month column, and writes report.xlsx with three sheets: "Data" (all rows), "Monthly" (pivot of Revenue by Region rows and Month columns with totals) and "Top Products" (Units and Revenue per Product sorted by Revenue). Auto-fit column widths, apply #,##0 to money columns, bold the header row and freeze it. Wrap it in a main() function with a docstring.

Common mistakes

Forgetting index=False, which adds an unnamed column of row numbers to every export.
Reading a CSV without parse_dates and then wondering why .dt fails. Strings are not dates.
Letting pandas turn IDs such as 00123 into the integer 123. Use dtype={"ID": str}.
Writing to a workbook that is open in Excel, which raises PermissionError. Close the file first.
Calling to_excel() twice on the same path without ExcelWriter; the second call overwrites the first sheet instead of adding one.

Exercise

Read sales.xlsx, add a Month column formatted as YYYY-MM, and write monthly_report.xlsx with two sheets: Raw holding all rows without the index, and Revenue by Product holding a pivot of Revenue with Products as rows and Months as columns, zeros for gaps. Print the sheet names to confirm.

Show answer

import pandas as pd

df = pd.read_excel("sales.xlsx", sheet_name="Sales")
df["Month"] = df["Date"].dt.strftime("%Y-%m")
pivot = df.pivot_table(index="Product", columns="Month", values="Revenue",
                       aggfunc="sum", fill_value=0)

with pd.ExcelWriter("monthly_report.xlsx") as writer:
    df.to_excel(writer, sheet_name="Raw", index=False)
    pivot.to_excel(writer, sheet_name="Revenue by Product")

print(pd.ExcelFile("monthly_report.xlsx").sheet_names)
print(pivot)

Output:
['Raw', 'Revenue by Product']
Month      2026-01  2026-02
Product                    
Calendar      1485     1188
Dashboard     5994     7992
Tracker       2990     2093

Related chapters

Python pandas Basics – the DataFrame skills these files feed into.
Python CSV – the standard-library csv module for row-by-row work.
Automate Excel with Python and AI – turn this into a scheduled report.
Python with AI course hub – all 48 chapters in order.

FAQ

Do I need openpyxl to read Excel files with pandas?

Yes for .xlsx files. pandas delegates Excel reading and writing to an engine, and openpyxl is the default for .xlsx. Install it with pip install openpyxl; CSV files need no extra package.

How do I read a specific sheet with pandas?

Pass sheet_name to read_excel(): a string for the tab name, an integer for its position, a list to get several sheets, or None to load every sheet into a dictionary of DataFrames.

How do I write several DataFrames to one Excel file?

Open a pd.ExcelWriter in a with block and call to_excel() on each DataFrame with a different sheet_name. The workbook is saved when the block ends.

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

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