Handling Excel File Formats and Conversions

The word "Excel file" hides at least six different file formats, and Python treats each one differently. A .xlsx is a zip archive of XML; a .xls is a decades-old binary format from a different era; a .xlsm is an .xlsx carrying a VBA project that most libraries will quietly throw away; a .xlsb is a binary variant that only a couple of Python packages can read at all. Get the format wrong and you meet a confusing error — or worse, a silent data loss. This page maps the landscape, shows which engine handles each format, and gives you the conversion and detection recipes that keep a reporting job from breaking when somebody emails you the wrong kind of spreadsheet. It sits alongside the other foundations in Getting Started with Python Excel Automation.

The five formats you will actually meet

Every spreadsheet that lands on a Python script belongs to one of a handful of families, and the family — not the file name — decides which package can open it.

.xlsx is the modern default: an OOXML workbook, which is really a zip archive full of XML parts. Unzip one and you find xl/worksheets/sheet1.xml, a styles part, a shared-strings table. Because it is an open, documented format, Python support is excellent — openpyxl reads and writes it, xlsxwriter writes it very fast, and pandas sits on top of both.

.xlsm is byte-for-byte the same container with one extra part inside: xl/vbaProject.bin, holding the macros. Everything that reads .xlsx reads .xlsm too. The catch is writing, which is covered in its own section below.

.xls is the pre-2007 binary format. It caps out at 65,536 rows and 256 columns, stores no OOXML at all, and needs a completely separate reader. Modern xlrd reads only this format — support for .xlsx was removed in xlrd 2.0, which is why so much old tutorial code now fails with XLRDError: Excel xlsx file; not supported.

.xlsb is Excel's binary workbook: the same logical structure as .xlsx but with the XML replaced by a compact binary encoding. Excel opens it faster and the files are smaller; Python can only read it, via pyxlsb or python-calamine.

.ods is the OpenDocument spreadsheet used by LibreOffice and Google Sheets exports. pandas reads and writes it through odfpy, but styling support is thin.

Install what you need up front so the failure is a missing package at setup time rather than a traceback at 3 a.m. in a scheduled job:

pip install pandas openpyxl xlsxwriter    # the everyday set
pip install xlrd                          # legacy .xls
pip install pyxlsb                         # binary .xlsb
pip install odfpy                          # OpenDocument .ods
pip install python-calamine                # one fast reader for xlsx/xls/xlsb/ods

python-calamine deserves a note: it is a Rust-backed reader that handles .xlsx, .xls, .xlsb and .ods behind a single interface, and pandas ships an official engine="calamine" for it. It reads only — no writing, no styles — but for the "just get the values into a DataFrame" case it is both the fastest option and the one that spares you from installing four packages.

How pandas chooses an engine

pd.read_excel looks at the file extension and maps it to an engine. That mapping is the source of most confusing errors, because the extension can be wrong and the engine can be missing.

You can always override the guess. Passing engine= explicitly is the fix when a file has the wrong extension, and it is how you opt into the fast calamine reader:

import pandas as pd

# Let the extension decide (the usual case).
df = pd.read_excel("sales.xlsx")

# Force an engine — useful when the extension lies, or for speed.
df = pd.read_excel("sales.xlsx", engine="calamine")
df = pd.read_excel("legacy.xls", engine="xlrd")
df = pd.read_excel("archive.xlsb", engine="pyxlsb")
df = pd.read_excel("export.ods", engine="odf")

Writing follows the same pattern through ExcelWriter, where the engine choice also decides which formatting features you get:

import pandas as pd

df = pd.DataFrame({"region": ["North", "South"], "revenue": [159.92, 247.50]})

with pd.ExcelWriter("out.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Summary", index=False)

with pd.ExcelWriter("out_fast.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer, sheet_name="Summary", index=False)

with pd.ExcelWriter("out.ods", engine="odf") as writer:
    df.to_excel(writer, sheet_name="Summary", index=False)

The trade-off between the two .xlsx writers is covered in detail in openpyxl vs xlsxwriter vs pandas.ExcelWriter. The short version: xlsxwriter is faster and has richer formatting, openpyxl is the only one that can modify an existing file.

Reading the legacy binary format

A .xls file cannot be opened by openpyxl at all — the error is blunt and confuses people who expect one library to do everything:

from openpyxl import load_workbook

load_workbook("legacy.xls")
# InvalidFileException: openpyxl does not support the old .xls file format,
# please use xlrd to read this file, or convert it to the more recent
# .xlsx file format.

The pragmatic answer is to read it once with a reader that understands BIFF, then work in .xlsx from there:

import pandas as pd

# One call, one engine, straight into a DataFrame.
df = pd.read_excel("legacy.xls", engine="xlrd")

# Or read every sheet at once.
sheets = pd.read_excel("legacy.xls", sheet_name=None, engine="xlrd")
print(list(sheets))          # ['Q1', 'Q2', 'Notes']

Remember the row cap. A .xls sheet holds at most 65,536 rows, so if an upstream system exports to .xls and your data has outgrown that, rows are being lost before Python ever sees the file. The full walkthrough — including the pitfalls of dates and the xlrd 2.0 breaking change — is in reading .xls files in Python.

Keeping macros alive in .xlsm

This is the trap that costs people the most time. Load an .xlsm, change one cell, save it — and the macros are gone, with no warning at all.

The rule has two halves, and both are required:

from openpyxl import load_workbook

# 1. Tell openpyxl to carry the VBA project through.
wb = load_workbook("report.xlsm", keep_vba=True)

ws = wb["Data"]
ws["B2"] = 4821

# 2. Save with an .xlsm name. Saving as .xlsx discards the macros
#    even when keep_vba was set.
wb.save("report_updated.xlsm")

There is a second, subtler half to the rule: openpyxl preserves the VBA binary but does not understand it. If a macro refers to a sheet you renamed or a named range you deleted, the file still opens and the macro still exists — it just fails at run time inside Excel. Treat macro-enabled workbooks as templates whose structure you fill, not restructure; the template-filling pattern in populate an Excel template without losing formatting applies directly.

Converting between formats in bulk

Conversion is the pressure valve for every format problem: read once with whatever engine understands the input, write out .xlsx, and let the rest of your pipeline assume one format. Because the read side is uniform through pandas, a converter is short.

from pathlib import Path
import pandas as pd

READERS = {
    ".xls": "xlrd",
    ".xlsb": "pyxlsb",
    ".ods": "odf",
    ".xlsx": "openpyxl",
    ".xlsm": "openpyxl",
}

def convert_to_xlsx(src, out_dir="converted"):
    """Read any supported spreadsheet and write every sheet to one .xlsx."""
    src = Path(src)
    engine = READERS.get(src.suffix.lower())
    if engine is None:
        raise ValueError(f"Unsupported input format: {src.suffix}")

    sheets = pd.read_excel(src, sheet_name=None, engine=engine)

    out_dir = Path(out_dir)
    out_dir.mkdir(parents=True, exist_ok=True)
    dest = out_dir / (src.stem + ".xlsx")

    with pd.ExcelWriter(dest, engine="xlsxwriter") as writer:
        for name, frame in sheets.items():
            # Excel sheet names cap at 31 characters.
            frame.to_excel(writer, sheet_name=name[:31], index=False)
    return dest

if __name__ == "__main__":
    for path in Path("inbox").glob("*.*"):
        if path.suffix.lower() in READERS:
            print("converted", convert_to_xlsx(path))

Two things to be clear-eyed about. First, this converts values, not appearance — pandas reads cells, so colours, merged cells, charts and formulas do not survive. If the visual fidelity matters, convert with LibreOffice instead (soffice --headless --convert-to xlsx), the same tool used in converting Excel to PDF. Second, formulas come back as their last cached result under most engines, which is usually what you want for a data pipeline and never what you want if you were trying to preserve a live model.

For the reverse direction — going down to CSV for a system that will not read spreadsheets — see converting Excel to CSV with Python, which also covers the encoding and delimiter details that trip up exports.

Detecting the real format of a file

Extensions lie. A system exports "Excel" that is actually tab-separated text; a user renames .xls to .xlsx because a form insisted on it; a download arrives as report.xlsx and is an HTML table. Sniffing the first bytes tells you the truth in microseconds.

from pathlib import Path
import zipfile

OLE2 = b"\xd0\xcf\x11\xe0"

def detect_format(path):
    """Identify a spreadsheet by content, not by file name."""
    path = Path(path)
    head = path.open("rb").read(8)

    if head.startswith(b"PK\x03\x04"):
        # A zip — look inside to tell xlsx/xlsm from ods.
        with zipfile.ZipFile(path) as zf:
            names = set(zf.namelist())
        if "xl/vbaProject.bin" in names:
            return "xlsm"
        if any(n.startswith("xl/") for n in names):
            return "xlsx"
        if "content.xml" in names:
            return "ods"
        return "zip"

    if head.startswith(OLE2):
        return "xls-or-xlsb"        # OLE2 container; both live here
    if head.lstrip()[:1] == b"<":
        return "html"
    return "text"                   # csv / tsv / something delimited

for name in ["sales.xlsx", "legacy.xls", "export.csv"]:
    print(name, "->", detect_format(name))

Wiring that into an ingest step turns a mystifying traceback into a clear message. It pairs well with the column and type checks in validating Excel columns before import — sniff the container first, then validate the contents.

import pandas as pd

READ = {"xlsx": "openpyxl", "xlsm": "openpyxl", "ods": "odf"}

def read_any(path):
    kind = detect_format(path)
    if kind in READ:
        return pd.read_excel(path, engine=READ[kind])
    if kind == "xls-or-xlsb":
        # calamine reads both binary families behind one engine.
        return pd.read_excel(path, engine="calamine")
    if kind == "html":
        return pd.read_html(path)[0]
    if kind == "text":
        return pd.read_csv(path, sep=None, engine="python")
    raise ValueError(f"Cannot read {path}: unrecognised format {kind}")

What actually lives inside an .xlsx

Because .xlsx is a zip archive, you can inspect it with nothing but the standard library — which is genuinely useful when a file misbehaves and you want to know whether the problem is your code or the file.

import zipfile

with zipfile.ZipFile("sales.xlsx") as zf:
    for info in sorted(zf.infolist(), key=lambda i: -i.file_size)[:8]:
        print(f"{info.file_size:>10,}  {info.filename}")

A typical report prints something like this:

   482,104  xl/worksheets/sheet1.xml
   118,940  xl/sharedStrings.xml
     9,220  xl/styles.xml
     2,118  xl/workbook.xml
     1,004  xl/theme/theme1.xml
       872  [Content_Types].xml
       610  xl/_rels/workbook.xml.rels
       412  docProps/app.xml

Three of those parts explain most of the surprises people hit.

sharedStrings.xml is a deduplication table. Every distinct piece of text in the workbook is stored once here, and cells reference it by index. That is why a sheet with a million repeated category names is far smaller than you would expect — and why writing that same sheet with a tool that does not use shared strings produces a much larger file.

styles.xml holds every distinct combination of font, fill, border and number format in the workbook. Excel caps this at roughly 64,000 unique cell formats, which sounds generous until a script applies a style object inside a loop and creates a fresh entry every iteration. The result is the "too many different cell formats" error, and the fix — define each style once outside the loop and reuse it — is covered in styling Excel cells with openpyxl.

[Content_Types].xml declares what each part is. This is the file that makes an .xlsm an .xlsm: it carries the macro-enabled content type. Rename an .xlsm to .xlsx on disk and Excel still treats it as macro-enabled, because the extension was never what decided it.

Knowing the layout also gives you a fast structural check that needs no spreadsheet library at all — handy in CI, where installing openpyxl just to assert a file is well-formed is overkill:

import zipfile

REQUIRED = {"[Content_Types].xml", "xl/workbook.xml"}

def looks_like_a_workbook(path):
    """Cheap structural validation of an OOXML file."""
    try:
        with zipfile.ZipFile(path) as zf:
            names = set(zf.namelist())
            if bad := zf.testzip():          # first corrupt member, or None
                return False, f"corrupt archive member: {bad}"
    except zipfile.BadZipFile:
        return False, "not a zip archive — wrong format or truncated download"

    missing = REQUIRED - names
    if missing:
        return False, f"missing parts: {', '.join(sorted(missing))}"
    sheets = [n for n in names if n.startswith("xl/worksheets/sheet")]
    return True, f"{len(sheets)} sheet(s)"

print(looks_like_a_workbook("sales.xlsx"))

Truncated downloads are the single most common cause of BadZipFile in production. A file that stopped arriving halfway through is still a plausible-looking .xlsx on disk, and this check catches it before a confusing parser error does.

Reading from bytes, streams and URLs

Not every workbook arrives as a path on disk. It comes back from an HTTP request, out of an object store, or as an upload in a web handler. Every reader in this ecosystem accepts a file-like object, so none of those cases needs a temporary file.

import io
import pandas as pd
import requests
from openpyxl import load_workbook

response = requests.get(
    "https://example.com/exports/august.xlsx", timeout=30
)
response.raise_for_status()

buffer = io.BytesIO(response.content)

# pandas takes the buffer directly — no temp file.
df = pd.read_excel(buffer)

# Rewind before handing the same bytes to another reader.
buffer.seek(0)
wb = load_workbook(buffer)
print(wb.sheetnames)

That seek(0) is the detail people miss. Reading a buffer leaves the cursor at the end, and the second reader sees zero bytes and reports a corrupt file. Rewind between consumers, or build a fresh BytesIO for each.

Writing to a buffer works the same way in reverse, which is how you return a workbook from a web endpoint or hand one to an upload call without ever touching the filesystem:

import io
import pandas as pd

df = pd.DataFrame({"region": ["North", "South"], "revenue": [159.92, 247.50]})

buffer = io.BytesIO()
with pd.ExcelWriter(buffer, engine="xlsxwriter") as writer:
    df.to_excel(writer, sheet_name="Summary", index=False)

buffer.seek(0)
payload = buffer.getvalue()      # bytes, ready to upload or return
print(f"{len(payload):,} bytes in memory")

One caveat worth knowing: pd.read_excel accepts a URL string directly, and it works — but it downloads with no timeout, no retry and no status check. In a scheduled job that is a hang waiting to happen, so fetch with requests yourself and pass the bytes, as above. The same reasoning applies to API sources generally; see fetching API data into Excel with Python requests.

Choosing the output format for your readers

Input format is decided for you. Output format is a choice, and it is worth making deliberately rather than defaulting to whatever the first tutorial used.

The last row deserves more weight than it usually gets. A surprising share of "Excel reports" are never edited — they are read, and then filed. Sending a PDF removes the whole class of problems where two people hold different edited copies of the same figures, and the conversion is one step, described in exporting Excel reports to PDF.

When the answer is .xlsx, one more decision remains: which writer. The trade-off is not subtle:

import time
import pandas as pd

df = pd.DataFrame({"id": range(200_000), "amount": range(200_000)})

for engine in ("openpyxl", "xlsxwriter"):
    start = time.perf_counter()
    df.to_excel(f"bench_{engine}.xlsx", index=False, engine=engine)
    print(f"{engine:<12} {time.perf_counter() - start:6.2f}s")

xlsxwriter wins on write speed and has the richer formatting API, but it can only create new files. openpyxl is the only one of the two that can open an existing workbook and change part of it — which is exactly what template filling requires. The full comparison is in openpyxl vs xlsxwriter vs pandas.ExcelWriter.

A practical rule that covers nearly every case: create with xlsxwriter, modify with openpyxl. If a job does both — builds a fresh workbook and then edits a template — use each library for the half it is good at rather than forcing one to do everything.

Key takeaways

• The extension picks the engine, and you can override it. openpyxl for .xlsx/.xlsm, xlrd for .xls, pyxlsb for .xlsb, odf for .ods — or calamine for all of them at once when you only need values.
• .xls and .xlsb are read-only from Python. Convert to .xlsx at the edge of your pipeline and never think about them again.
• keep_vba=True plus an .xlsm filename is the only way macros survive an openpyxl round-trip. Both halves are required.
• Converting through pandas keeps values, not appearance. For visual fidelity, shell out to headless LibreOffice instead.
• Sniff the leading bytes before trusting a file name; PK means a zip-based workbook and D0 CF 11 E0 means a legacy binary one.

Frequently asked questions

Which engine does pandas use for an .xlsx file? openpyxl. pandas picks the engine from the file extension unless you pass engine= explicitly — openpyxl for .xlsx/.xlsm, calamine or xlrd for .xls, pyxlsb for .xlsb, and odfpy for .ods. Install the matching package or the read raises ImportError.

Why does openpyxl refuse to open my .xls file? openpyxl only handles the OOXML zip formats (.xlsx, .xlsm). A .xls file is the older binary BIFF format and needs xlrd or python-calamine instead, or a conversion step to .xlsx first.

Will saving an .xlsm with openpyxl keep the macros? Only if you load it with keep_vba=True and save it back with an .xlsm extension. Without that flag openpyxl drops the VBA project silently and you get a macro-free workbook with no error.

Is .xlsb worth using for large files? It reads and writes faster in Excel itself and the files are smaller, but Python support is read-only through pyxlsb or python-calamine. If a job needs to write output, produce .xlsx and let Excel users save as .xlsb if they want.

How do I know what a file really is when the extension is wrong? Read the first few bytes. An OOXML workbook starts with PK (a zip), the legacy binary formats start with the OLE2 signature D0 CF 11 E0, and a CSV is plain text. Sniffing the header is far more reliable than trusting the name.

Can Python write .ods files? Yes — pandas writes OpenDocument spreadsheets through the odf engine when the output path ends in .ods, and reads them through odfpy. Formatting support is much thinner than for .xlsx, so treat .ods as a data interchange format rather than a report target.

Related

• Up to the parent: Getting Started with Python Excel Automation — the foundations this topic sits inside.
• Read .xls Files in Python with xlrd and pandas — the legacy binary format end to end.
• Convert .xls to .xlsx with Python — a batch converter with the pitfalls handled.
• Work with Macro-Enabled .xlsm Files in openpyxl — keeping the VBA project intact.
• Read and Write .ods Files with Python — the OpenDocument side.
• Using openpyxl for Excel File Manipulation — the library behind the OOXML formats.
• Reading Excel Files with pandas — the reading layer that sits on top of every engine here.