Python Excel AutomationPractical guides for developers
Start learning
Guide
Getting Started With Python Excel AutomationDeep dive

Fix "openpyxl does not support the old .xls file format"

Why openpyxl refuses .xls files, how to read them with xlrd or calamine, how to convert a whole folder to .xlsx, and how to spot a mislabelled file before it fails.

openpyxl does not support the old .xls file format, please use xlrd to read this file, or convert it to the more recent .xlsx file format is an unusually helpful error message — it names the cause and two fixes in one line. Underneath it is a hard fact: .xls and .xlsx are different file formats that happen to share three letters. This guide reads the legacy file, converts it properly, and adds the check that stops the error reaching production at all. It is one branch of Troubleshooting Common Python Excel Errors.

Two unrelated file formats behind one familiar icon A legacy xls is an OLE2 compound document of binary BIFF records read by xlrd; a modern xlsx is a zip of XML parts read by openpyxl. Neither library can read the other format. .xls (1997–2003) .xlsx (2007 onward) OLE2 compound document binary BIFF8 records read by xlrd or calamine 65,536 rows maximum zip container XML parts per sheet read by openpyxl or calamine 1,048,576 rows maximum

Prerequisites

Install a reader for the legacy format alongside pandas:

Bash
pip install pandas openpyxl xlrd

xlrd 2.x reads .xls only — that restriction is deliberate. If you would rather carry one dependency that reads every format, python-calamine is a good alternative and is covered below.

Read the file as it is

The minimal fix is to name the engine. pandas guesses from the extension, and the guess is right here — but being explicit documents the intent and survives a file arriving without an extension:

Python
import pandas as pd

df = pd.read_excel("legacy_report.xls", engine="xlrd")
print(df.head())

For a workbook with several sheets, read them all at once and get a dictionary keyed by sheet name:

Python
sheets = pd.read_excel("legacy_report.xls", sheet_name=None, engine="xlrd")
for name, frame in sheets.items():
    print(f"{name}: {len(frame)} rows")

If you need cell-level access rather than a DataFrame — merged regions, per-cell formats, the sheet's own date mode — use xlrd directly:

Python
import xlrd

book = xlrd.open_workbook("legacy_report.xls")
sheet = book.sheet_by_index(0)
print(sheet.nrows, sheet.ncols)
print(sheet.cell_value(0, 0))

Note that xlrd returns dates as floats. Convert them with xlrd.xldate_as_datetime(value, book.datemode); the datemode matters because workbooks saved on classic Macs use a 1904 epoch. The same trap in a pandas context is covered in Fix Excel serial numbers showing instead of dates.

Convert once instead of special-casing forever

If the file will be read more than a handful of times, convert it. Everything downstream — formatting, charts, tables, conditional formatting — needs .xlsx, and the conversion removes both the extra dependency and the 65,536-row ceiling.

Python
"""Convert every .xls in a folder to .xlsx, preserving sheet names."""
from pathlib import Path

import pandas as pd

src_dir = Path("legacy")
out_dir = Path("converted")
out_dir.mkdir(exist_ok=True)

for src in sorted(src_dir.glob("*.xls")):
    sheets = pd.read_excel(src, sheet_name=None, engine="xlrd")
    dst = out_dir / f"{src.stem}.xlsx"
    with pd.ExcelWriter(dst, engine="openpyxl") as writer:
        for name, frame in sheets.items():
            frame.to_excel(writer, sheet_name=name[:31], index=False)
    print(f"{src.name} -> {dst.name}  ({len(sheets)} sheet(s))")

Two details keep this safe: sheet_name=None preserves every sheet rather than only the first, and name[:31] respects Excel's sheet-name limit so the write cannot produce a file Excel offers to repair. A pandas round trip carries values, not formulas or macros — when you need a faithful conversion, drive LibreOffice headlessly instead, as shown in Convert xls to xlsx with Python.

Convert once at the boundary, then use one code path Legacy files are converted to xlsx as they arrive, so every downstream step — cleaning, formatting, charting and delivery — sees a single modern format instead of branching on the extension. One conversion at the edge removes every later branch .xls inbox .xlsx inbox normalise step route on magic bytes one format .xlsx only clean and style chart and deliver Downstream code never asks which format it is reading

Read every format with one engine

If your inputs are a mixed bag — some .xls, some .xlsx, the occasional .xlsb — python-calamine reads all of them through a single Rust-backed engine, and it is usually faster than either pure-Python reader:

Bash
pip install python-calamine
Python
import pandas as pd

for name in ("legacy_report.xls", "modern_report.xlsx", "binary_report.xlsb"):
    df = pd.read_excel(name, engine="calamine")
    print(name, df.shape)

That collapses the engine table to one line of code. The trade-off is that calamine reads values only — no styles, no charts, no writing — so keep openpyxl for anything that produces or edits a workbook. Its speed characteristics are measured in Speed up pandas Excel reads with the calamine engine.

Choose between reading, converting and replacing the source

Three responses to a legacy file are all defensible, and the right one depends on who owns the producer. Reading in place is the smallest change but leaves an extra dependency in every environment. Converting at ingest costs one script and pays back on every later step. Getting the source system to emit .xlsx is the only fix that removes the problem entirely — worth asking for when the export is a config option rather than a code change.

Cost and reach of the three responses to a legacy .xls Reading in place is cheapest but keeps the dependency, converting at ingest costs one script and unlocks every downstream feature, and changing the source system removes the problem permanently. Effort now versus friction later Read in place one engine= argument xlrd in every environment no styling or charts 65,536-row ceiling stays Convert at ingest one script, run once all later tools available row limit lifted macros need .xlsm Fix the source often a config toggle problem gone for good needs another team slowest to land Convert at ingest unless the file is read once and thrown away

In practice most teams end up with a hybrid: convert at ingest today, and ask the upstream owner to change the export format in parallel. The conversion script then becomes a no-op the day the source starts producing .xlsx, because the byte-sniffing reader above already handles both.

Common pitfalls and gotchas

  • Passing engine="xlrd" to an .xlsx. This is the mirror-image error and appears in a lot of pre-2020 tutorials. Since xlrd 2.0 it raises immediately; use openpyxl for modern files.
  • Trusting the extension. A file named .xls that starts with PK is really an .xlsx, and one that starts with <html is a web export. Check the bytes — see Fix BadZipFile when reading an Excel file in Python.
  • Losing precision on dates. xlrd hands back serial floats; converting with the wrong datemode shifts every date by four years and a day.
  • Hitting the row ceiling. A legacy sheet cannot hold more than 65,536 rows, so a large export may already be truncated at source. Compare the row count against the system that produced it.
  • Assuming conversion preserves macros. It does not. A .xls with VBA becomes .xlsm, not .xlsx; see Work with macro-enabled xlsm files in openpyxl.

Performance and scale notes

xlrd parses the whole workbook into memory — there is no streaming mode, and no equivalent of openpyxl's read_only=True. A 60,000-row .xls typically costs a few hundred megabytes while parsing, so converting large legacy files in a loop can exhaust a small container. Two mitigations work well: convert one file per process so memory is reclaimed between files, or switch the read to calamine, whose parser is both faster and markedly leaner. For folders of hundreds of files, the parallel pattern in Process multiple Excel files in parallel with Python applies unchanged — the conversion is CPU-bound, so processes beat threads.

Detect the mismatch before it fails

The durable fix is to stop dispatching on the filename. This helper picks the engine from the file's own bytes, so a mislabelled export is read correctly instead of raising:

Python
"""Choose a pandas engine from content, not from the extension."""
from pathlib import Path

import pandas as pd

def read_spreadsheet(path: str) -> pd.DataFrame:
    head = Path(path).read_bytes()[:4]
    if head == b"\xd0\xcf\x11\xe0":          # OLE2 -> legacy .xls
        return pd.read_excel(path, engine="xlrd")
    if head[:2] == b"PK":                     # zip -> .xlsx/.xlsm/.ods
        return pd.read_excel(path, engine="openpyxl")
    raise ValueError(f"{path}: not a spreadsheet ({head!r})")

df = read_spreadsheet("mystery_export.xls")

Drop that into the ingest layer of a reporting job and the "does not support" error disappears permanently, whatever the upstream system decides to call its exports next.

Conclusion

openpyxl refuses .xls because it is a genuinely different format, not because of a version problem. Read legacy files with engine="xlrd" (or calamine for a single engine across every format), and convert them to .xlsx at the edge of your pipeline so the rest of the code has one format to think about. Dispatch on magic bytes rather than the extension, and the error cannot come back through a mislabelled export.

Frequently asked questions

Why can't openpyxl just read .xls too? They are unrelated formats. An .xls file is an OLE2 compound document with binary BIFF records; an .xlsx is a zip of XML parts. openpyxl implements only the latter, so supporting .xls would mean shipping a second, entirely separate parser.

Is xlrd still maintained for .xls? Yes, for .xls only. Version 2.0 deliberately removed .xlsx support to reduce the security surface, which is why old code that passed engine="xlrd" for an .xlsx now fails. For legacy files xlrd remains the standard choice.

What if the file has an .xls extension but is not really .xls? That is common with portal exports. Check the first bytes: OLE2 files start with D0 CF 11 E0, real .xlsx files start with PK, and HTML or CSV exports start with a tag or plain text. Route the read on the bytes, never on the name.

Should I convert to .xlsx or keep reading .xls each time? Convert once if you control the file. Conversion removes the extra dependency, lifts the 65,536-row limit, and makes every downstream tool available. Keep reading .xls only when a system you cannot change keeps producing it.

Does converting lose anything? Formulas and values convert cleanly; macros, some legacy chart types and unusual formatting may not survive a pandas round trip because pandas reads values only. For a fuller conversion, drive LibreOffice in headless mode.