Fixing the Python xlrd Error Excel xlsx Not Supported

The traceback ends with xlrd.biffh.XLRDError: Excel xlsx file; not supported. The code worked last year, the file is a perfectly good workbook, and nothing was changed except an upgrade. It is one of the most searched Python errors for spreadsheets, and the fix takes one line once you know what happened: the library that reads .xls was told to read .xlsx, and since version 2.0 it refuses. This post explains the history, the fix, and the checks for the cases where the fix does not work. It is a spoke of understanding Excel errors when data comes from code.

What happened

Excel has two main file formats. The old binary .xls was used until Excel 2003. The .xlsx format, a zip of XML files, arrived in Excel 2007. For years, one library, xlrd, read both. Its maintainers removed .xlsx support in version 2.0, released in December 2020, because they intended the package to cover the old format only and had security concerns about the xlsx code. openpyxl became the reader for xlsx.

pandas used to call xlrd for every Excel file. After xlrd 2.0 came out, pd.read_excel("data.xlsx") on a machine with the new xlrd raised the error. pandas 1.2 changed the default: for .xlsx it uses openpyxl when installed. So the same line of code fails or works depending on the versions installed.

The quickest fix

Install openpyxl and ask for it:

pip install openpyxl

import pandas as pd

df = pd.read_excel("data.xlsx", engine="openpyxl")

If your pandas is 1.2 or newer and openpyxl is installed, engine is not needed, and plain pd.read_excel("data.xlsx") works. The explicit argument is a good habit in scripts that might run in different environments, because it makes the dependency visible and the failure clear.

If you see a different error, ImportError: Missing optional dependency 'openpyxl', pandas is telling you the same thing the other way round: it wants openpyxl and cannot find it (fixing missing optional dependency openpyxl).

Why not pin xlrd to 1.2

The old advice was pip install xlrd==1.2.0, which brings back xlsx reading. It works, and it is a poor trade. Version 1.2 is unmaintained, its xlsx reader had known problems with untrusted files, and a pin in a requirements file stays there for years. It also stops pandas from moving forward, since new pandas versions expect the openpyxl path. Use the pin only as a short-term bridge while you fix the code, and put a date on removing it.

When the file really is .xls

A genuine old .xls file still needs xlrd. Install the current one and read with the matching engine:

pip install xlrd

df = pd.read_excel("legacy.xls", engine="xlrd")

openpyxl cannot read .xls. If you use it on one, you get InvalidFileException for the extension or BadZipFile for the content, since an .xls is not a zip. For a pipeline that sees both, choose the engine from the extension, or better from the content:

def engine_for(path):
    with open(path, "rb") as f:
        head = f.read(4)
    if head == b"PK\x03\x04":
        return "openpyxl"
    if head == b"\xd0\xcf\x11\xe0":
        return "xlrd"
    raise ValueError(f"{path}: not an Excel workbook (starts with {head!r})")

df = pd.read_excel(path, engine=engine_for(path))

Reading the first bytes beats trusting the extension, because files are renamed more often than anyone admits (why Excel says the file format or extension is not valid).

Converting old files once

If you have many .xls files and want to stop depending on xlrd, convert them to .xlsx once and read those from then on. Excel does it by hand with Save As. LibreOffice does it in batch, headless:

soffice --headless --convert-to xlsx --outdir converted/ *.xls

Check a sample after converting, since formatting and some formulas may differ. For a large archive, the batch route is worth planning (batch converting XLS files to XLSX).

Other versions of the same mistake

The error message changes with the pair of mismatched things:

Each is the same problem with a different tool: the engine and the content disagree. The first bytes settle it.

Environment mismatches

Sometimes the code is right and the environment is wrong. Two Python installations on one machine, a notebook kernel with different packages than the shell, or a container image built before the dependency was added. Print what you have inside the failing process:

import pandas, sys
print(sys.executable, pandas.__version__)
import importlib.metadata as md
for pkg in ("xlrd", "openpyxl"):
    try:
        print(pkg, md.version(pkg))
    except md.PackageNotFoundError:
        print(pkg, "not installed")

Install into the interpreter that printed, with python -m pip install openpyxl, which uses the same Python rather than whichever pip comes first on the path. Record both packages in the project's requirements file so the next environment is built correctly.

Checks after the fix

Compare the row count and a control total of the loaded frame with the workbook. Look at the dtypes: the openpyxl reader returns numbers, dates and strings according to the cell types, and a column that xlrd once returned as floats may now be integers or datetimes. A script that relied on float dates or on NaN handling may behave differently. Read a sample and compare it with what you had before (specifying column types when reading Excel).

Formulas are another difference. Both readers return the cached value of a formula, not the formula itself, unless you load the workbook with openpyxl directly in formula mode (handling Excel formulas and cached values).

A note on versions and lock files

Pin versions in the project, not in your head. A requirements file that lists pandas, openpyxl and, if you read legacy files, xlrd with a compatible range means a new machine builds the same environment as the old one. Without it, the next person who installs only pandas finds that Excel reading fails with an import error, and the fix is a guess.

For a pipeline that must keep running for years, run the reading code in a test with a small xlsx and a small xls fixture. When a library upgrade changes behavior, as xlrd 2.0 did, the test fails on your machine in the upgrade branch, not in production on a Monday.

When switching the engine does not help

The file is not an Excel file. An HTML or CSV with an Excel name fails in every reader. Look at the first bytes and read it as what it is.

The file is encrypted. A password-protected workbook is neither a plain zip nor a plain xls. It needs a decryption step first.

The file is damaged. A truncated xlsx fails in openpyxl with a zip error (Excel cannot open the file because the file is corrupt).

You need speed. If openpyxl is too slow for large files, try the read-only mode or another reader such as python-calamine (reading Excel files faster in Python).

The xlrd project explains the change in its release notes and documentation, and pandas lists the supported engines on its page for read_excel. To convert a small result to JSON, use the CSV to JSON converter.