Lesson 2: Loading CSV and Excel Files

Why loading is where analyses go wrong

In a spreadsheet, opening a file feels like nothing happened. Excel guesses the separator and the text encoding, and you see the result straight away. pandas also guesses, but it guesses from a few fixed defaults: a comma separator, UTF-8 text, and column names on the first line. Real exports often break all three. A banking system might give you semicolons, a two-line title banner and Windows-1252 text. A CRM workbook might put a report title above the header row.

Think of read_csv and read_excel as Excel's Text Import Wizard written down as code. Each option you would click in the wizard becomes a keyword argument. Because those choices sit in your notebook, you make them once and they run the same way every time.

The code here is written for pandas 2.x. pandas 3.0 came out in January 2026, and every option in this lesson works the same way there. One visible difference is that text columns show as dtype str in 3.0 and object in 2.x.

Set up the files

Run this cell once. It writes the two messy files we'll load. Writing files is covered later, so treat this cell as a copy-and-paste step.

# One-off setup: create a messy bank CSV and a CRM workbook
from openpyxl import Workbook

csv_text = (
    "Bank export - Account 12345678\n"
    "Generated 01/10/2026\n"
    "Date;Payee;Reference;Amount;Balance\n"
    "01/09/2026;Café Rouge;INV-101;-23.50;1976.50\n"
    "03/09/2026;Müller GmbH;INV-102;-410.00;1566.50\n"
    "05/09/2026;Client Payment;REC-88;1200.00;2766.50\n"
    "09/09/2026;Café Rouge;INV-103;-18.20;2748.30\n"
)
with open("bank_export.csv", "w", encoding="cp1252") as f:   # Windows-1252, like many older systems
    f.write(csv_text)

wb = Workbook()
wb.remove(wb.active)
for region, rows in {"North": [("A-1", 120), ("A-2", 340), ("A-3", 90), ("A-4", 410)],
                     "South": [("B-1", 75), ("B-2", 220), ("B-3", 180)]}.items():
    ws = wb.create_sheet(region)
    ws.append([f"Q3 sales - {region}"])        # row 1: title
    ws.append(["Source: CRM export"])          # row 2: note
    ws.append(["Order", "Value"])              # row 3: real header
    for r in rows:
        ws.append(list(r))
wb.save("sales.xlsx")

If this cell raises ModuleNotFoundError: No module named 'openpyxl', skip ahead to the Excel section and install it, then come back.

Loading a CSV: try the default first

import pandas as pd

df = pd.read_csv("bank_export.csv")   # defaults: sep=",", encoding="utf-8", header from the first line

This call fails. Depending on which problem pandas hits first, you'll get a UnicodeDecodeError or a ParserError: Error tokenizing data. Both tell you something about the file:

• UnicodeDecodeError means the bytes are not valid UTF-8. The é in Café was saved as Windows-1252.
• Error tokenizing data means the rows don't have the same number of fields. The one-field title line doesn't match the five-field header.

Practical tip: open the file in a plain-text editor like VS Code or Notepad before you guess at options. Excel tidies the file before you see it, so it hides these problems.

Fixing it, one option at a time

df = pd.read_csv(
    "bank_export.csv",
    sep=";",                 # fields are split by semicolons, not commas
    skiprows=2,              # skip the two banner lines; line 3 becomes the header
    encoding="cp1252",       # the file was written by a Windows system
    usecols=["Date", "Payee", "Reference", "Amount"],   # leave out Balance
)
df

         Date           Payee Reference   Amount
0  01/09/2026      Café Rouge   INV-101   -23.50
1  03/09/2026     Müller GmbH   INV-102  -410.00
2  05/09/2026  Client Payment    REC-88  1200.00
3  09/09/2026      Café Rouge   INV-103   -18.20

What each option does:

• sep sets the delimiter. You'll see ";" in European exports, where the comma is the decimal mark, and "\t" in tab-separated files.
• skiprows takes either a number (skip that many lines from the top) or a list of 0-based line numbers, such as skiprows=[0, 1].
• encoding defaults to "utf-8". If you get a decode error, try "cp1252" or "latin-1". If the first column name starts with odd characters like , the file begins with a byte-order mark, and "utf-8-sig" removes it.
• usecols keeps only the columns you name. You can also pass positions like [0, 1, 3]. On wide exports this saves memory.

Gotcha: there is also encoding_errors="replace". It makes the error disappear by turning the bad characters into replacement symbols, so Müller turns into garbage without any warning. Find the correct encoding instead of hiding the error.

Loading Excel: sheets and header rows

To read .xlsx files, pandas uses the openpyxl package. It is an optional dependency, so a basic pandas install doesn't include it. If it's missing, read_excel raises an ImportError telling you to install it:

pip install openpyxl          # or: pip install "pandas[excel]" for every Excel engine

In a notebook, %pip install openpyxl installs it into the kernel you're actually using. After installing, restart the kernel.

# One sheet, by name. The header is on Excel row 3, so skip the 2 rows above it.
north = pd.read_excel("sales.xlsx", sheet_name="North", skiprows=2)
north

  Order  Value
0   A-1    120
1   A-2    340
2   A-3     90
3   A-4    410

sheet_name decides which sheets you get and what kind of object comes back:

sheets = pd.read_excel("sales.xlsx", sheet_name=None, skiprows=2)  # every sheet
sheets.keys()            # dict_keys(['North', 'South'])
sheets["South"].shape    # (3, 2)

Gotcha: if you pass a list or None and then call .head() on the result, you get AttributeError: 'dict' object has no attribute 'head'. That's because you got a dict back, not a DataFrame. Pick one sheet out of it with sheets["South"]. You can also use header=2 to say "the column names are on row index 2" in place of skiprows=2. Here both give the same result.

Check the counts before you trust the load

The equivalent in a spreadsheet is glancing at the row numbers. In Excel, the North header is on row 3 and the last order is on row 7, so you expect 7 − 3 = 4 data rows and 2 columns. In the CSV, the text editor shows 7 lines: 2 banner lines, 1 header and 4 data rows.

print(df.shape)       # (4, 4)  -> 4 rows, 4 columns (we dropped Balance)
print(north.shape)    # (4, 2)

# Make the check fail loudly if a future export changes shape
assert north.shape == (4, 2), f"Expected (4, 2), got {north.shape}"
assert sheets["South"].shape == (3, 2)

If the count is off by one, a header or title row is usually being read as data, or a real header has been skipped. If you get a single column with everything squashed into it, the sep is wrong.

Your check: run the cells. df.shape should print (4, 4), north.shape (4, 2), and both asserts should pass with no output.

Key takeaways

• read_csv assumes commas, UTF-8 and a header on the first line. Real exports often need sep, skiprows, encoding and usecols.
• A UnicodeDecodeError means you need a different encoding, often cp1252 or utf-8-sig. Don't hide it with encoding_errors.
• read_excel needs openpyxl installed to read .xlsx. sheet_name takes a name or position, and a list or None gives you back a dict of DataFrames.
• Use skiprows or header to get past title rows so the real header becomes the column names.
• Always compare .shape with what the source file shows, and use assert so the check runs every time.