Read Binary .xlsb Excel Files in Python

The Excel Binary Workbook format, .xlsb, stores the same content as .xlsx in a compact binary encoding instead of XML. Finance teams and anyone with very large workbooks use it because it opens and saves faster in Excel and produces smaller files. For Python it is a stumbling block: openpyxl cannot read it, and the first pd.read_excel("report.xlsb") a person tries may fail for a missing dependency. Two engines read it well, with one important difference in how they return dates. This guide, part of Handling Excel File Formats and Conversions, covers both, plus conversion to .xlsx when the rest of your pipeline needs it.

Why teams use .xlsb

The binary format holds the same things as .xlsx — sheets, formulas, formatting, charts, even VBA macros — with the same limits of 1,048,576 rows and 16,384 columns. What changes is the encoding: records are written in a compact binary form instead of verbose XML. Large workbooks typically shrink noticeably on disk and open and save faster in Excel, which is why teams with heavy models and long histories adopt it. The costs are interoperability and transparency: fewer tools read it, no Python library writes it, and you cannot unzip it and inspect the XML the way you can with .xlsx. When you control the producer, ask for .xlsx or CSV; when the file comes from a system or a team you do not control, the techniques below cover reading it as it is.

Prerequisites

pip install pandas python-calamine pyxlsb

You only need one of the two engines; installing both lets you compare. The examples read a workbook called report.xlsb with a sheet named Test — substitute your own file.

Step 1 — Read with the calamine engine

calamine is the simplest choice: pass the engine name and read as you would any workbook. Date cells come back as datetimes and error cells as missing values:

import pandas as pd

book = pd.ExcelFile("report.xlsb", engine="calamine")
print(book.sheet_names)                     # ['Test']
df = pd.read_excel(book, sheet_name="Test")
print(df.dtypes)

All the usual read_excel options — header, usecols, nrows, dtype — work the same way with calamine as with any other engine. It is also the fastest engine for .xlsx, so the same code serves both formats; see Speed Up pandas Excel Reads with the calamine Engine.

Step 2 — Read with the pyxlsb engine, and fix the dates

The pyxlsb engine returns exactly what the file stores. For dates, that is Excel's serial day number: the count of days since 30 December 1899, with the time as a fraction of a day. Reading a workbook whose first column holds the date 27 December 2017 gives 43096, and a time of 18:06 gives 0.7541…. Convert those columns explicitly:

raw = pd.read_excel("report.xlsb", engine="pyxlsb", sheet_name="Test")

def excel_serial_to_datetime(series: pd.Series) -> pd.Series:
    numbers = pd.to_numeric(series, errors="coerce")
    return pd.to_datetime(numbers, unit="D", origin="1899-12-30")

raw["booked"] = excel_serial_to_datetime(raw["booked"])
print(raw["booked"].head())

The origin 1899-12-30 rather than 1900-01-01 accounts for Excel's historical leap-year quirk, and it is correct for every date from March 1900 onwards. The same conversion applies to serial numbers from any source, as covered in Fix Excel Serial Numbers Showing Instead of Dates.

The last row shows a trap: a cell holding only a time has no date part, so it converts to a time on 30 December 1899. For time-only columns, convert to durations instead with pd.to_timedelta(numbers, unit="D").

Step 3 — Recognise error cells

Cells showing #DIV/0! or #REF! in Excel store error codes. pyxlsb returns them as hexadecimal strings; calamine returns missing values. With pyxlsb you can map the codes to names and count them before deciding what to do:

ERRORS = {"0x0": "#NULL!", "0x7": "#DIV/0!", "0xf": "#VALUE!", "0x17": "#REF!",
          "0x1d": "#NAME?", "0x24": "#NUM!", "0x2a": "#N/A"}

def error_report(frame: pd.DataFrame) -> pd.Series:
    as_text = frame.astype(str)
    hits = as_text.isin(ERRORS.keys())
    return as_text[hits].stack().map(ERRORS).value_counts()

print(error_report(raw))

Counting errors is the right first step whichever engine you use: silently turning #REF! into NaN hides a broken workbook, and a report built on it inherits the damage. The sources of those errors are discussed in Find External Links and Broken References in a Workbook with Python.

Step 4 — Stream rows with pyxlsb directly

For very large .xlsb sheets, or when you want to process rows as they are read, use pyxlsb's own API. It yields one row at a time, so memory stays flat however big the sheet is:

from pyxlsb import convert_date, open_workbook

total = 0.0
with open_workbook("report.xlsb") as wb:
    print(wb.sheets)                          # ['Test']
    with wb.get_sheet("Test") as sheet:
        for i, row in enumerate(sheet.rows()):
            values = [cell.v for cell in row]
            if i == 0:
                header = values
                continue
            amount = values[header.index("amount")]
            if isinstance(amount, (int, float)):
                total += amount
print(convert_date(43096))                    # 2017-12-27 00:00:00

convert_date turns a serial number into a Python datetime, the same conversion as Step 2 for single values. Sheets can be opened by name or by one-based index.

Step 5 — Convert .xlsb to .xlsx

Python libraries cannot write .xlsb, and some tools in a pipeline cannot read it. When you need an .xlsx copy, LibreOffice converts headlessly and keeps values, dates and formatting:

import subprocess
from pathlib import Path

Path("converted").mkdir(exist_ok=True)
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "converted", "report.xlsb"],
               check=True, capture_output=True, timeout=300)
converted = pd.read_excel("converted/report.xlsx")

On Windows with Excel installed, Excel itself can do the conversion through COM (SaveAs with the .xlsx file format), which is the most faithful option for files with macros or unusual features; see Save a Workbook in Another Format with win32com.

Step 6 — Read a folder of .xlsb files

Monthly .xlsb exports usually arrive as a series. Read them all with the same settings and stack them, keeping the file name so every row can be traced back to its source:

from pathlib import Path

frames = []
for path in sorted(Path("exports").glob("*.xlsb")):
    frame = pd.read_excel(path, engine="calamine", sheet_name=0)
    frame["source_file"] = path.name
    frames.append(frame)
combined = pd.concat(frames, ignore_index=True) if frames else pd.DataFrame()
print(combined.groupby("source_file").size())

The per-file row counts are a quick sanity check: a month with an unusually small count usually means the export was cut short or saved from the wrong sheet. For large folders, the parallel pattern in Process Multiple Excel Files in Parallel with Python applies unchanged — each file is read independently.

Common pitfalls

• Formulas are not available. Both engines return the cached results of formulas, not the formulas themselves. If a workbook was saved without recalculation, the values may be stale.
• Mixed columns. A column with numbers and error codes arrives as object dtype with pyxlsb. Convert with pd.to_numeric(..., errors="coerce") after counting the errors.
• Engine not installed. ImportError: Missing optional dependency means the engine package is missing. Install python-calamine or pyxlsb in the same environment — the general fix is in Fix "Missing optional dependency 'openpyxl'" in pandas.
• Treating times as dates. Time-only cells become times on 30 December 1899 when converted as dates. Convert them to timedeltas.

Performance and scale notes

calamine reads .xlsb roughly as fast as it reads .xlsx — both are far faster than pure-Python parsing — so for analysis it is the right default. pyxlsb is slower per row but constant in memory when streamed, which matters for sheets with millions of cells on a small machine. If the same large .xlsb is read repeatedly, convert it once to Parquet and read that instead, as described in Convert Excel Files to Parquet with Python.

Conclusion

Read .xlsb with pandas and the calamine engine for a DataFrame with dates already converted and errors as missing values. Use the pyxlsb engine or its streaming API when you need raw values or flat memory, converting serial numbers with origin 1899-12-30 and mapping error codes before discarding them. Python cannot write .xlsb; convert to .xlsx with LibreOffice or Excel when the rest of the pipeline needs it.

Frequently asked questions

Can openpyxl read .xlsb files? No. openpyxl reads and writes the XML-based formats (.xlsx, .xlsm). For the binary .xlsb format use pandas with the calamine or pyxlsb engine, or the pyxlsb package directly.

Why are my dates numbers when I read an .xlsb file? The pyxlsb engine returns the raw stored value, and Excel stores dates as serial day numbers. Convert them with pd.to_datetime(col, unit="D", origin="1899-12-30"), or use the calamine engine, which converts date cells for you.

Can Python write .xlsb files? Not with the common libraries — pandas, openpyxl and xlsxwriter write .xlsx only. Write .xlsx, or have Excel itself save as .xlsb through COM or xlwings if the binary format is required.

Why does an .xlsb read show 0x7 or 0x17 in some cells? Those are Excel error codes returned by pyxlsb — 0x7 is #DIV/0! and 0x17 is #REF!. The calamine engine returns missing values for error cells instead.

Related

• Up one level: Handling Excel File Formats and Conversions — every format in and out.
• Pick an Excel Engine for .xlsx, .xlsm, .xls, .xlsb and .ods — engines at a glance.
• Read .xls Files in Python with xlrd and pandas — the older binary format.
• Reading Excel with Polars and Arrow — calamine-based reading in Polars.