ValueError: Excel file format cannot be determined, you must specify an engine manually

Did a spreadsheet that opens perfectly well in Excel refuse to load?

pd.read_excel('report.xlsx')

ValueError: Excel file format cannot be determined, you must specify an engine manually.

Pandas identifies a spreadsheet by looking at the first few bytes, not at the file extension. This message means those bytes matched none of the formats it knows, so the extension is telling you one thing and the contents another.

Before specifying an engine, find out what the file actually is:

open('report.xlsx', 'rb').read(200)

The answer is usually immediate and slightly annoying.

The three things it usually turns out to be

An HTML table with a spreadsheet extension. If the first bytes are b'<html', b'<!DOCTYPE' or b'<table', some reporting system exported HTML and named it .xls because Excel opens it anyway. pd.read_html('report.xlsx') reads it properly and returns a list of frames.

A CSV with a spreadsheet extension. If you can read the column headers in plain text, it is a CSV. pd.read_csv handles it, sometimes with sep='\t' if it is really tab-separated.

An error page. If the file was downloaded, the first bytes may be a login page or a 404. This is the one that catches automated pipelines: the download "succeeded" with a 200 status and saved HTML to a file named .xlsx.

A file that starts with PK is a genuine modern .xlsx — those are zip archives — and one starting with \xd0\xcf\x11\xe0 is a genuine old-style .xls.

When the format is real and the engine is missing

If the bytes look right, the problem is the reader rather than the file, and the fix depends on the format:

pd.read_excel('report.xls', engine='xlrd')

Worth knowing: xlrd dropped .xlsx support some years ago and now reads only the old .xls format. Advice that says "install xlrd" for an .xlsx file predates that change and will not help.

Reading a file you have in memory

If the spreadsheet came from a download or a database rather than disk, wrap the bytes so Pandas can seek through them:

pd.read_excel(io.BytesIO(response.content))

Passing response.content directly, or a stream that has already been read to the end, produces this same message — because there are no bytes left to sniff.

The mistakes I see most often

Trusting the extension. It is a naming convention, not a guarantee, and export tools are careless with it.

Renaming the file to fix it. Changing .xls to .xlsx changes nothing about the contents.

Installing every engine. If the file is HTML, no engine will read it. Look at the bytes first; it takes ten seconds and answers the question.

Practice

See read_excel in Pandas for loading spreadsheets, read_html in Pandas for tables that are really HTML, and read_csv in Pandas for the ones that are really text.