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

Fix "Excel File Format Cannot Be Determined" in pandas

Why pandas cannot pick an engine for your spreadsheet, how to name the engine explicitly, and how to handle streams, URLs and files with no extension at all.

ValueError: Excel file format cannot be determined, you must specify an engine manually is pandas telling you it has run out of ways to identify your file. It is not a corruption error and not a missing-package error: pandas looks at the extension, then at the leading bytes, and when neither matches a format it knows, it refuses to guess. This guide covers each situation that produces it — a wrong extension, no extension at all, an in-memory buffer, a downloaded response — and the fix for each. It is one branch of Troubleshooting Common Python Excel Errors.

How pandas decides which engine to use read_excel uses an explicit engine argument if given, otherwise the filename extension, otherwise the leading bytes of the stream; when none of those identifies a format it raises the value error. read_excel() engine resolution, in order 1. engine= given? use it, stop asking 2. filename extension .xlsx .xls .xlsb .ods 3. leading bytes PK… or OLE2 header a supported format identified? yes no parse the workbook DataFrame returned ValueError raised "specify an engine"

Prerequisites

pandas plus at least one engine. Which one depends on the formats you actually receive:

Bash
pip install pandas openpyxl        # .xlsx and .xlsm
pip install xlrd                   # legacy .xls
pip install pyxlsb                 # binary .xlsb
pip install odfpy                  # OpenDocument .ods
pip install python-calamine        # all of the above, one engine

The one-line fix, and why it works

Name the engine. It overrides detection entirely, so the extension becomes irrelevant:

Python
import pandas as pd

df = pd.read_excel("export.dat", engine="openpyxl")

That is the whole fix when you already know the format. The interesting cases are the ones where you do not — a file arriving from a system that names things download, or an HTTP response body with no filename anywhere in sight.

Files with no extension, and files with the wrong one

If a file has no extension, pandas falls back to inspecting the bytes, which works for genuine .xlsx and .xls files. It fails when the content is not a spreadsheet at all — an HTML table or a CSV — because no engine can read those. Identify first, then dispatch:

Python
"""Read a spreadsheet whose extension tells you nothing."""
from pathlib import Path

import pandas as pd

def read_unknown(path: str) -> pd.DataFrame:
    head = Path(path).read_bytes()[:8]
    if head[:2] == b"PK":
        return pd.read_excel(path, engine="openpyxl")
    if head[:4] == b"\xd0\xcf\x11\xe0":
        return pd.read_excel(path, engine="xlrd")
    if head[:5].lower() in (b"<html", b"<!doc"):
        return pd.read_html(path)[0]
    return pd.read_csv(path, sep=None, engine="python", encoding="utf-8-sig")

df = read_unknown("downloads/export")
print(df.shape)

Four branches cover essentially every file a business system will hand you. The sep=None, engine="python" combination lets pandas sniff the delimiter, which matters because "CSV" exports are frequently semicolon- or tab-separated in European locales.

Buffers, downloads and streams

The most frequent modern cause is an in-memory buffer. A BytesIO has no name, so the extension route does not exist and pandas will only work from the leading bytes — which fail the moment the response is an error page rather than a workbook:

Python
"""Download a workbook and read it without touching disk."""
import io

import pandas as pd
import requests

resp = requests.get("https://example.com/reports/latest.xlsx", timeout=30)
resp.raise_for_status()

ctype = resp.headers.get("Content-Type", "")
if "spreadsheet" not in ctype and "excel" not in ctype:
    raise ValueError(f"unexpected content type {ctype!r} — probably an error page")

df = pd.read_excel(io.BytesIO(resp.content), engine="openpyxl")
print(df.head())

Two guards make that reliable: raise_for_status() turns a 404 into an exception rather than a DataFrame attempt, and the Content-Type check catches the login page that many portals return to an unauthenticated request. Reading straight from bytes is covered in more depth in Read an Excel file from a URL or bytes in Python.

Why an in-memory buffer loses the format hint Reading from a path gives pandas both a filename and bytes, while reading from BytesIO gives it bytes only, so an explicit engine argument replaces the missing filename hint. from a path from a buffer name: sales.xlsx bytes: PK… two hints — detection succeeds name: none bytes: whatever arrived pass engine= to replace the hint

The message that names a missing package

A different error text — Missing optional dependency 'openpyxl'. Use pip or conda to install openpyxl. — means detection worked fine and the reader is simply not installed. That is a requirements problem, and it is worth failing early rather than at 06:00:

Python
"""Fail at start-up with an actionable message, not mid-report."""
import importlib

REQUIRED_ENGINES = {"openpyxl": ".xlsx", "xlrd": ".xls"}

missing = [pkg for pkg in REQUIRED_ENGINES if importlib.util.find_spec(pkg) is None]
if missing:
    raise SystemExit(f"pip install {' '.join(missing)} — needed for "
                     f"{[REQUIRED_ENGINES[m] for m in missing]}")

Container images are the usual culprit: a slim base image plus pip install pandas gives you pandas with no Excel engine at all, because the engines are optional extras. Installing pandas[excel] or listing the engines explicitly in requirements.txt avoids it.

Validate user uploads before pandas ever sees them

When the file comes from a person rather than a system, the format is genuinely unknown and this error becomes a routine event rather than a bug. Decide what you accept, check it explicitly, and return a message the uploader can act on:

Python
"""Classify an upload before choosing a reader."""
ACCEPTED = {
    b"PK\x03\x04": ("xlsx", "openpyxl"),
    b"\xd0\xcf\x11\xe0": ("xls", "xlrd"),
}

def classify(blob: bytes) -> tuple[str, str | None]:
    for magic, (kind, engine) in ACCEPTED.items():
        if blob.startswith(magic):
            return kind, engine
    if blob[:5].lower() in (b"<html", b"<!doc"):
        return "html", None
    if b"," in blob[:200] or b";" in blob[:200]:
        return "csv", None
    return "unknown", None

kind, engine = classify(open("upload.bin", "rb").read(512))
print(kind, engine)
An upload gate that answers with a message, not a stack trace Uploads are classified by their leading bytes into workbook, tabular text, or rejected, so the person uploading gets a specific message instead of a pandas value error. Classify first, read second uploaded file any name, any type first 512 bytes matched to a signature known: read with the right engine unknown: "this is not a workbook" The uploader learns what went wrong; the job keeps running

Returning "That file looks like an HTML export — please save it as .xlsx" is worth far more to the person uploading than a ValueError from deep inside pandas, and it removes an entire category of support requests.

Keep the engine choice in one place

Scattering engine= across a codebase works until the day a format changes. Centralise the decision in a single module-level mapping, so switching every read from openpyxl to calamine — for speed, or because a format was added — is a one-line change rather than a search across the repository:

Python
"""One place that knows which engine reads what."""
ENGINE_BY_SUFFIX = {
    ".xlsx": "openpyxl", ".xlsm": "openpyxl",
    ".xls": "xlrd", ".xlsb": "pyxlsb", ".ods": "odf",
}

def engine_for(path) -> str:
    from pathlib import Path
    suffix = Path(path).suffix.lower()
    try:
        return ENGINE_BY_SUFFIX[suffix]
    except KeyError:
        raise ValueError(f"no engine configured for {suffix!r} ({path})") from None

That also gives you a place to hang a feature flag: reading the mapping from configuration lets an operator switch engines in a running deployment without a release, which is useful when a new file format shows up unannounced. The configuration pattern itself is covered in Keep Excel report settings in a config file.

Common pitfalls and gotchas

  • Reading a .csv with read_excel. No engine can help; a CSV is not a workbook. Route on content, as above.
  • Passing a file object opened in text mode. open(path) yields text; read_excel needs binary. Use open(path, "rb") or pass the path itself.
  • A consumed buffer. Reading a BytesIO twice returns nothing the second time unless you seek(0) first — a common cause of a confusing empty-DataFrame result right after this error is fixed.
  • engine="xlrd" on a modern file. xlrd 2.0 handles .xls only, and this is one of the errors it produces — see Fix "openpyxl does not support the old .xls format".
  • Assuming the extension is authoritative in a folder of user uploads. It never is; validate every upload before parsing.

Performance and scale notes

Explicit engines are marginally faster than detection because pandas skips the sniffing step, but the real gain is predictability: a job that names its engine cannot silently switch readers when a file arrives with a different extension. For large files the engine choice dominates everything else — calamine typically reads several times faster than openpyxl and uses less memory, at the cost of ignoring formatting. Benchmarks and the trade-off are in Speed up pandas Excel reads with the calamine engine, and chunked strategies for very large workbooks are in Read a large Excel file in chunks with pandas.

Conclusion

pandas raises this error when it cannot identify the format from a filename or from the leading bytes — most often because the file has no extension, has the wrong one, or arrived as an unnamed buffer. Pass engine= when you know the format, and dispatch on magic bytes when you do not. Add a start-up check that the engines you depend on are installed, and the error stops appearing in unattended runs altogether.

Frequently asked questions

What exactly does pandas look at to choose an engine? The filename extension when it has one, and otherwise the first bytes of the stream. If neither identifies a supported format — because the object is a bare BytesIO, or the extension is wrong — it gives up with this ValueError rather than guessing.

Why does it happen only for downloaded files? A response body handed to read_excel as BytesIO carries no filename, so the extension route is unavailable. Pass engine= explicitly, or give the buffer a name by writing it to a temporary file first.

Does installing openpyxl fix it? Only when the message names a missing dependency. "Format cannot be determined" is about identification, not installation — a missing engine produces a different ImportError naming the package to install.

Can I make pandas ignore the extension? Yes. The engine= argument overrides detection completely, which is why naming it is the durable fix for files whose extension is wrong or absent.

What if the file turns out to be a CSV? Read it with read_csv instead. A CSV has no sheets, so no Excel engine can open it — the extension is simply a lie told by whatever exported it.