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

Read .xls Files in Python with xlrd and pandas

Open legacy .xls workbooks from Python: install xlrd 2.x, read sheets into DataFrames, decode the date serials xlrd returns, and handle the 65,536-row ceiling.

Legacy .xls workbooks keep arriving — from ERP exports, from banking portals, from a colleague's Excel 2003 habit. They are the one spreadsheet format that openpyxl flatly refuses, so the usual load_workbook reflex fails with a blunt error. This guide covers the working path: install the right reader, get the data into a DataFrame, deal with the date serials the format stores instead of real dates, and know the row ceiling that silently truncates data upstream. It is the hands-on companion to Handling Excel File Formats and Conversions.

Two routes at a legacy .xls file, one of which is a dead end A legacy.xls file branches two ways. The upper branch to openpyxl ends in an InvalidFileException because openpyxl only reads the OOXML zip formats. The lower branch goes through xlrd or python-calamine, which parse the BIFF binary records into cell values, and pandas turns those into a DataFrame. input legacy.xls BIFF8 binary openpyxl InvalidFileException xlrd / calamine parses BIFF records cell values + date serials DataFrame pandas

Prerequisites

  • Python 3 with pandas and a legacy reader:
Bash
pip install pandas xlrd            # xlrd 2.x reads .xls only
pip install python-calamine        # optional, faster alternative
  • An .xls file to read. If you do not have one to hand, any spreadsheet program will "Save As → Excel 97-2003 Workbook (*.xls)". You cannot create one with openpyxl or xlsxwriter — neither can write the format.

One thing to internalise before the first line of code: xlrd 2.0 removed .xlsx support. A large amount of tutorial code on the internet still says pd.read_excel(path, engine="xlrd") for modern files, and that now fails. xlrd is a legacy-format reader and nothing else.

Step 1 — Read the whole file into DataFrames

The simplest correct call lets pandas route on the extension. Because the file ends in .xls, pandas reaches for xlrd automatically:

Python
import pandas as pd

df = pd.read_excel("legacy.xls")
print(df.head())
print(df.dtypes)

If the extension is wrong or you want to be explicit, name the engine:

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

Reading every sheet at once is the same call with sheet_name=None, which returns a dict keyed by sheet name — the same shape as reading all sheets from a modern workbook:

Python
sheets = pd.read_excel("legacy.xls", sheet_name=None)

for name, frame in sheets.items():
    print(f"{name:<20} {frame.shape[0]:>6} rows x {frame.shape[1]} cols")

Step 2 — Use xlrd directly when you need cell types

pandas gives you values. When you need to know what Excel thought a cell was — text, number, date, boolean, error — you drop to xlrd itself. Every cell carries a ctype code alongside its value:

Python
import xlrd

book = xlrd.open_workbook("legacy.xls")
print("sheets:", book.sheet_names())
print("datemode:", book.datemode)      # 0 = 1900 system, 1 = 1904 (old Mac)

sheet = book.sheet_by_index(0)
print(f"{sheet.nrows} rows x {sheet.ncols} cols")

TYPES = {
    xlrd.XL_CELL_EMPTY: "empty",
    xlrd.XL_CELL_TEXT: "text",
    xlrd.XL_CELL_NUMBER: "number",
    xlrd.XL_CELL_DATE: "date",
    xlrd.XL_CELL_BOOLEAN: "bool",
    xlrd.XL_CELL_ERROR: "error",
}

for col in range(sheet.ncols):
    cell = sheet.cell(1, col)          # first data row
    print(f"col {col}: {TYPES[cell.ctype]:<7} {cell.value!r}")

That datemode value matters for the next step, and it is why blindly converting serials with a hard-coded 1899-12-30 epoch is a bug waiting to happen on files produced by older Macs.

Step 3 — Turn date serials into real datetimes

The .xls format has no date type. A date is a floating-point day count, and whether a given cell is a date is a property of its number format, not its value. xlrd reports XL_CELL_DATE when the format looks like a date, but plenty of real files store dates in cells formatted as plain numbers — and then you get 45292.0 where you expected 2024-01-01.

Converting an .xls date serial to a datetime The workbook stores 45292.0, a day count. Combined with the workbook's datemode, which selects the 1900 or 1904 epoch, xlrd's xldate_as_datetime function converts it to the first of January 2024. The datemode is required because the two epochs differ by more than four years. a stored day count is not a date until you supply the epoch cell value 45292.0 book.datemode 0 = 1900 · 1 = 1904 xldate_as_datetime value, datemode datetime 2024-01-01

Convert with the workbook's own datemode so both epochs are handled:

Python
import xlrd
from xlrd import xldate_as_datetime

book = xlrd.open_workbook("legacy.xls")
sheet = book.sheet_by_index(0)

def cell_to_python(cell, datemode):
    """Normalise one xlrd cell into a plain Python value."""
    if cell.ctype == xlrd.XL_CELL_DATE:
        return xldate_as_datetime(cell.value, datemode)
    if cell.ctype == xlrd.XL_CELL_BOOLEAN:
        return bool(cell.value)
    if cell.ctype == xlrd.XL_CELL_EMPTY:
        return None
    return cell.value

rows = [
    [cell_to_python(sheet.cell(r, c), book.datemode) for c in range(sheet.ncols)]
    for r in range(sheet.nrows)
]
print(rows[1])

When the dates came through pandas as bare floats because the cells were not date-formatted, convert the column afterwards. pandas has the Excel epoch built in:

Python
import pandas as pd

df = pd.read_excel("legacy.xls")

# origin="1899-12-30" is the 1900 date system Excel actually implements.
df["invoice_date"] = pd.to_datetime(
    df["invoice_date"], unit="D", origin="1899-12-30"
)
print(df["invoice_date"].head())

Use origin="1904-01-01" instead if book.datemode reported 1. Date handling across all Excel formats is covered more fully in working with dates and times in Excel data.

Step 4 — The faster alternative: python-calamine

xlrd is pure Python and shows it on big files. python-calamine wraps a Rust parser and reads .xls, .xlsx, .xlsb and .ods behind one engine name. pandas supports it directly:

Python
import pandas as pd

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

The trade-off: calamine returns values only, with no per-cell type introspection and no styling. When you need ctype, stay on xlrd; when you just want the numbers in a DataFrame quickly, calamine wins, often by a factor of three to five on multi-megabyte legacy files.

Common pitfalls and fixes

SymptomCauseFix
XLRDError: Excel xlsx file; not supportedPassing an .xlsx to xlrd 2.xDrop the engine="xlrd" argument, or use engine="openpyxl".
ImportError: Missing optional dependency 'xlrd'pandas routed to xlrd but it is not installedpip install xlrd
Dates read as 45292.0Cells not formatted as dates in the sourceConvert with pd.to_datetime(col, unit="D", origin="1899-12-30").
Dates off by ~4 yearsWorkbook uses the 1904 date systemCheck book.datemode; use origin="1904-01-01".
File has exactly 65,536 rowsBIFF8 row ceiling hit during exportFix the export to emit .xlsx or CSV — the data is already lost.
CompDocError / corrupt fileNot really an .xls; often HTML or CSV renamedSniff the leading bytes before reading; see the detection recipe in the parent topic.
Blank leading rowsA title block above the headerPass skiprows= and header=, as in skipping rows when reading Excel.

The 65,536-row case deserves emphasis because it looks like a Python problem and is not. If a sheet has exactly 65,536 rows, assume truncation until proven otherwise:

Python
import pandas as pd

df = pd.read_excel("legacy.xls")
if len(df) >= 65_535:
    raise ValueError(
        f"{len(df)} rows — at the .xls ceiling. "
        "The export almost certainly truncated; request .xlsx or CSV."
    )

Performance and scale notes

Memory profile: xlrd's whole-file load versus streaming an .xlsx Two memory-over-time curves. The xlrd curve rises steeply to a high plateau as the entire workbook is materialised as Python objects before any cell can be read, and stays there for the whole job. The converted xlsx curve read in openpyxl read-only mode stays low and flat, rising only slightly, because rows are yielded one at a time instead of being held. peak memory while reading the same data high low time → xlrd — whole workbook held converted .xlsx, streamed parse completes before cell 1 one row at a time — flat

xlrd loads the whole workbook into memory before you touch a cell, and there is no streaming mode — the format's structure does not allow one. A 20 MB .xls can occupy several hundred megabytes as Python objects, which is a real constraint in a container with a memory limit.

Two mitigations. First, read only the sheet you need rather than letting sheet_name=None materialise all of them:

Python
# Only sheet "Detail" is parsed into a DataFrame.
df = pd.read_excel("legacy.xls", sheet_name="Detail", usecols="A:F")

Second — and this is the durable fix — convert once, then work in .xlsx. Every downstream read gets faster, gains the streaming options described in reading large Excel files in chunks, and stops depending on a legacy parser. The conversion recipe is in convert .xls to .xlsx with Python.

For a directory of legacy files, convert them all in one pass and keep the originals for audit:

Python
from pathlib import Path
import pandas as pd

for src in Path("legacy").glob("*.xls"):
    frames = pd.read_excel(src, sheet_name=None)
    dest = Path("converted") / (src.stem + ".xlsx")
    dest.parent.mkdir(exist_ok=True)
    with pd.ExcelWriter(dest, engine="xlsxwriter") as writer:
        for name, frame in frames.items():
            frame.to_excel(writer, sheet_name=name[:31], index=False)
    print(src.name, "->", dest.name)

Conclusion

Reading .xls is a solved problem once you accept that it needs its own reader: xlrd for full cell-type detail, python-calamine when you want speed and only need values, and pandas over the top of either. Watch two things specifically — date serials, which need the workbook's datemode to decode correctly, and the 65,536-row ceiling, which quietly truncates upstream exports. Then convert to .xlsx at the boundary of your pipeline so nothing further downstream has to care.

Frequently asked questions

Why does xlrd raise XLRDError on my .xlsx file?xlrd 2.0 removed .xlsx support on purpose. It now reads only the legacy .xls binary format. For .xlsx use openpyxl, which is what pandas already picks by default for that extension.

Why are my dates coming back as numbers like 45292.0? The .xls format stores dates as a serial day count, and xlrd hands that number back unless the cell is typed as a date. Convert with xldate_as_datetime and the workbook's datemode, or let pandas parse the column after the read.

My .xls file only has 65,536 rows but the source had more — where did they go? They were dropped by whatever wrote the file. The BIFF8 format used by .xls has a hard ceiling of 65,536 rows and 256 columns; the truncation happened before Python saw the file, so the fix is to have the export produce .xlsx or CSV instead.

Is there a faster alternative to xlrd? Yes — python-calamine is a Rust-backed reader that handles .xls as well as .xlsx, .xlsb and .ods. pandas supports it directly with engine="calamine", and it is usually several times faster on large legacy files.

Can I write .xls files from Python? Not with xlrd, which is read-only. The old xlwt package could write .xls but is unmaintained and does not support modern Excel features. Write .xlsx instead — every current version of Excel opens it.