Read Excel Rows into Dictionaries with openpyxl

Turn each row of a sheet into a dictionary keyed by the header — the csv.DictReader pattern for .xlsx files. Stream rows with iter_rows(values_only=True), skip blank rows, clean up missing and duplicate headers, build lookups keyed by ID, and load rows into dataclasses without pandas.

openpyxl Reading Excel files

Plenty of scripts that read a spreadsheet do not need a DataFrame. They loop over the rows once — send an email per line, call an API per customer, check each record against a rule — and what they want is what csv.DictReader gives for CSV files: one dictionary per row, keyed by the column header. openpyxl has no DictReader, but the pattern takes a few lines, works on sheets of any size in read-only mode, and keeps a small script free of pandas and its import time. This page is part of Using openpyxl for Excel File Manipulation.

Prerequisites

Bash

pip install openpyxl

customers.xlsx has a header row and five data rows, with the usual problems of a real export: a blank row in the middle, an unlabelled column holding notes, a second column also called Name, padded text, and a footer line under the data.

Step 1 — The basic pattern

iter_rows(values_only=True) yields each row as a tuple of plain values instead of cell objects. Take the first tuple as the header and zip it with every tuple after it:

Python

from openpyxl import load_workbook

wb = load_workbook("customers.xlsx", read_only=True, data_only=True)
ws = wb["Customers"]
rows = ws.iter_rows(values_only=True)
header = next(rows)
print(header)
records = [dict(zip(header, row)) for row in rows]
wb.close()
print(len(records), "records")
print(records[0])

Two things in the first row show why the basic version is not enough: the unlabelled column arrives as a key of None, and the second Name column silently overwrites the first, because a dictionary cannot hold the same key twice. The record for Arden Ltd has 'Name': 'Arden' — the value from the later column. The count is also wrong — nine records for four customers: the blank row and the two empty rows before the footer became records full of None, and the footer became a record whose ID is a sentence.

Step 2 — A DictReader that handles real sheets

Wrap the pattern in a generator that fixes the header once and filters the rows as they stream past:

Python

def excel_dict_reader(path, sheet=None, header_row=1):
    wb = load_workbook(path, read_only=True, data_only=True)
    try:
        ws = wb[sheet] if sheet else wb.worksheets[0]
        rows = ws.iter_rows(min_row=header_row, values_only=True)
        raw = next(rows)
        header, seen = [], {}
        for i, name in enumerate(raw, start=1):
            name = str(name).strip() if name is not None else f"column_{i}"
            seen[name] = seen.get(name, 0) + 1
            header.append(name if seen[name] == 1 else f"{name}_{seen[name]}")
        for row in rows:
            if all(v is None or (isinstance(v, str) and not v.strip()) for v in row):
                continue
            yield {k: (v.strip() if isinstance(v, str) else v) for k, v in zip(header, row)}
    finally:
        wb.close()

for rec in excel_dict_reader("customers.xlsx"):
    print(rec["ID"], rec["Name"], rec["Name_2"], rec["column_6"])

The header is cleaned once: a missing name becomes column_6, and a repeated name gets a suffix, Name_2, so no value is lost. Blank rows are skipped — including rows of empty strings, which some exporters write instead of truly empty cells — and text values are stripped, which fixes " Dunmore ". Because it is a generator, the workbook is read one row at a time and closed by the finally block when the loop ends, even if the loop body raises.

The footer is still there: Exported 2026-09-28 comes through as a record with that text as its ID. Stop at the first row that does not look like data, or drop records whose key field is not the right type:

Python

records = [r for r in excel_dict_reader("customers.xlsx") if isinstance(r["ID"], int)]
print([r["ID"] for r in records])

The header_row argument covers reports with a title above the table: excel_dict_reader("report.xlsx", header_row=4) starts reading at row 4, treats it as the header and ignores everything above. When the header row moves from one export to the next, find it first by scanning the top rows for a known column name, then pass the row number in.

Step 3 — Build a lookup keyed by ID

The most common use of row dictionaries is a lookup: given an ID from somewhere else, fetch that customer's details. Build a dictionary of dictionaries, and refuse duplicate keys rather than let the last one win:

Python

customers = {}
for rec in records:
    if rec["ID"] in customers:
        raise ValueError(f"duplicate ID {rec['ID']}")
    customers[rec["ID"]] = rec

print(customers[1004]["Name"], customers[1004]["Renewal"])
due = [c["Name"] for c in customers.values() if c["Renewal"].strftime("%Y-%m") == "2026-10"]
print("renewing in October:", due)

Dates arrive as datetime objects, because openpyxl converts cells with a date format; a value stored as text stays a string. Check the type before calling date methods on values from sheets people edit by hand.

Step 4 — Load rows into a dataclass

Dictionaries are loose: a typo in a key is a KeyError at runtime, and nothing says which fields exist. For anything bigger than a throwaway script, convert each row into a dataclass that names the fields and their types:

Python

from dataclasses import dataclass
from datetime import datetime

@dataclass(frozen=True)
class Customer:
    id: int
    name: str
    email: str | None
    plan: str
    renewal: datetime

def to_customer(r):
    return Customer(id=r["ID"], name=r["Name"], email=r["Email"], plan=r["Plan"], renewal=r["Renewal"])

typed = [to_customer(r) for r in records]
print(typed[2])
missing_email = [c.name for c in typed if c.email is None]
print("no email:", missing_email)

The mapping function is the one place where sheet headers meet Python names, so a renamed column is fixed in one line. A dataclass does not check types at runtime; when the data must be validated as it is loaded — email formats, allowed plans, required fields — use pydantic instead, as shown in Validate Excel Rows with Pydantic.

Compared with pandas

pandas does the same in one line — pd.read_excel(path).to_dict("records") — and for data that is going to be grouped, joined or summarised, a DataFrame is the better tool. The dictionary approach wins when the work is per-row and the script is small: there is no pandas import, rows stream instead of loading at once, and values keep the types openpyxl gives them (Python int, str, datetime) instead of NumPy types that need converting before they go into JSON or an API call. to_dict("records") also turns empty cells into NaN, a float, where openpyxl gives None; code that checks if value is None behaves differently on the two.

Common pitfalls

Duplicate headers. dict(zip(...)) keeps the last value for a repeated key. Rename duplicates before zipping.
Formulas instead of values. Without data_only=True, formula cells return strings such as =B2*1.2.
Treating max_row as the end of the data. Formatted empty rows and footers extend it; filter rows by content instead.
Not closing read-only workbooks. Read-only mode keeps the file open until close(). The generator above closes it in finally; if you break out of the loop early, the generator is closed when it is garbage-collected, or call .close() on it yourself.
Merged cells. Only the top-left cell of a merged range has a value; the others read as None. See Unmerge Cells and Fill Values with openpyxl.

Performance notes

In read-only mode memory stays flat however long the sheet is, because only the current row is held. Speed is roughly that of openpyxl's parser — tens of thousands of rows per second — which is similar to read_excel with the openpyxl engine, since pandas uses the same parser underneath. Collecting every record into a list, as Step 3 does, brings the memory back; for very large sheets keep the processing inside the loop.

Conclusion

Read the header row with next(iter_rows(values_only=True)), clean it once — name the blanks, suffix the duplicates — and zip it with every following row, skipping blank rows and stripping text as they stream past. Filter out footers by checking the key field, build ID lookups that refuse duplicates, and move to dataclasses or pydantic models once the script grows. Reach for pandas when the work is about columns rather than rows.

Frequently asked questions

Does openpyxl have a DictReader like the csv module? No, but the pattern is short: take the first row from ws.iter_rows(values_only=True) as the header and zip it with each following row. Wrapping that in a generator gives a DictReader equivalent that streams the sheet.

How do I skip empty rows when reading with openpyxl? Check each row before converting it: skip it when every value is None or an empty string. Relying on max_row does not work, because formatted empty rows at the bottom count as part of the sheet.

Why do some of my dictionary keys come out as None? The header cell for that column is empty. Replace missing header names with a placeholder such as column_6 before zipping, otherwise every unlabelled column shares the key None and only the last one survives.

Is this faster than pandas read_excel? Roughly the same for parsing, since pandas uses openpyxl for .xlsx files. The gains are a lighter import, flat memory use when streaming, and plain Python types instead of NumPy ones.

Up one level: Using openpyxl for Excel File Manipulation — the cell-level API these readers are built on.
Iterate Over Rows and Columns with openpyxl — every way to walk a sheet.
Write a List of Dictionaries to Excel Without pandas — the same records going back out.
Speed Up openpyxl with Read-Only Mode — why streaming keeps memory flat.

More on openpyxl