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

Fix BadZipFile When Reading an Excel File in Python

zipfile.BadZipFile: File is not a zip file — why openpyxl and pandas raise it, how to identify the real format, and how to recover the data or repair the workbook.

zipfile.BadZipFile: File is not a zip file is the first error most Python developers hit when automating spreadsheets, and its wording sends people looking in the wrong place. Nothing is wrong with your pandas call or your openpyxl version: a modern .xlsx is a zip archive containing XML parts, so the read fails in the zip layer before a single cell is parsed. This guide identifies what the file really is, fixes each of the causes, and shows how to recover data from an archive that is genuinely damaged. It is one of the failures mapped in Troubleshooting Common Python Excel Errors.

What an .xlsx file really contains A valid xlsx is a zip container holding an XML part per sheet plus shared strings and styles; openpyxl opens the zip first, so a file that is not a zip fails before any sheet is read. report.xlsx, opened as bytes zip container first two bytes: PK central directory at the end of the file truncation breaks it xl/worksheets/sheet1.xml xl/sharedStrings.xml xl/styles.xml openpyxl Workbook object cells, styles, names BadZipFile is raised at the first arrow — the parser never runs

Prerequisites

Nothing beyond a standard install. zipfile is in the standard library, and the checks below need no third-party package:

Bash
pip install pandas openpyxl

The diagnosis works the same whether the error came from pd.read_excel(), openpyxl.load_workbook(), or a library that wraps either.

Step 1: confirm the file is not a zip

Do not guess from the extension. Read the first four bytes and compare them against the signatures Excel-adjacent files actually use:

Python
"""Report what a supposedly-xlsx file really is."""
from pathlib import Path
import zipfile

def diagnose(path: str) -> str:
    p = Path(path)
    if not p.is_file():
        return f"missing: {p.resolve()}"
    size = p.stat().st_size
    if size == 0:
        return "zero bytes — the upstream write or download failed"

    head = p.read_bytes()[:8]
    if head.startswith(b"PK\x03\x04"):
        return "valid zip container" if zipfile.is_zipfile(p) else "zip header but broken archive"
    if head.startswith(b"\xd0\xcf\x11\xe0"):
        return "OLE2 file — a legacy .xls, or an encrypted workbook"
    if head[:5].lower() in (b"<html", b"<!doc"):
        return "HTML — a web export renamed to .xlsx"
    if head.startswith(b"<?xml"):
        return "XML — SpreadsheetML 2003, not a real .xlsx"
    return f"text or unknown ({size} bytes): {head!r}"

print(diagnose("report.xlsx"))

The result names your fix. PK plus a broken archive means truncation or damage. OLE2 means a legacy or encrypted file. HTML, XML or plain text means the producer never wrote a real workbook — the most common outcome by far when the file came from a reporting portal, a CRM export, or an emailed "Excel" attachment.

Step 2: apply the fix for that cause

Four causes of BadZipFile and the fix for each A file that is really HTML or CSV needs a different reader, a legacy xls needs a conversion, a truncated download needs re-fetching, and a file still being written needs a stability check before reading. What the bytes say What to do starts with <html or a comma read_html() or read_csv(), then re-export OLE2 signature D0 CF 11 E0 engine="xlrd", or decrypt if protected PK header, archive still broken re-download; verify length or checksum size changes between two reads wait for a stable size, then open

An HTML table renamed .xlsx. Reporting portals export an HTML <table> with a spreadsheet extension because Excel renders it. pandas can read it directly, and you can write a genuine workbook from the result:

Python
import pandas as pd

tables = pd.read_html("report.xlsx")     # needs lxml or html5lib
df = tables[0]
df.to_excel("report_fixed.xlsx", index=False, engine="openpyxl")

A CSV renamed .xlsx. Same idea with pd.read_csv(); pass sep=None, engine="python" if the delimiter is unknown, and encoding="utf-8-sig" when a byte-order mark is present.

A legacy .xls. The file is an OLE2 compound document, which openpyxl has never supported. Read it with engine="xlrd" and convert once — see Fix "openpyxl does not support the old .xls format" and Convert xls to xlsx with Python.

An encrypted workbook. A password-protected file is also OLE2, so it reports the same way. Decrypt it in memory before reading, as shown in Open a password-protected Excel file with Python.

Step 3: recover data from a damaged archive

If the header says PK but the archive still will not open, the zip itself is damaged — usually a partial download. testzip() names the first bad member, and a member-by-member copy can often rescue everything else:

Python
"""Salvage the readable parts of a damaged .xlsx."""
import shutil
import zipfile

src, dst = "broken.xlsx", "salvaged.xlsx"

with zipfile.ZipFile(src) as zf:          # raises BadZipFile if the directory is gone
    print("first damaged member:", zf.testzip())
    with zipfile.ZipFile(dst, "w", zipfile.ZIP_DEFLATED) as out:
        for info in zf.infolist():
            try:
                out.writestr(info, zf.read(info.filename))
            except zipfile.BadZipFile:
                print("skipping unreadable part:", info.filename)

A workbook that loses xl/styles.xml still opens with its data intact; one that loses a sheet part does not. If zipfile.ZipFile(src) itself raises, the central directory at the end of the file is missing — the download stopped early — and no pure-Python repair exists. Re-fetch the file and compare the byte length against the Content-Length header before trusting it.

Common pitfalls and gotchas

  • Catching the wrong exception. BadZipFile is zipfile.BadZipFile; openpyxl re-raises some cases as InvalidFileException. Catch both if you are writing a guard.
  • Reading straight from a download stream. pd.read_excel(response.content) on a failed request happily parses an HTML error page. Check response.status_code and Content-Type first.
  • Assuming a network share is atomic. It is not. Have the producer write name.tmp and rename, or wait until the size stops changing.
  • Version confusion. BadZipFile never indicates an outdated openpyxl. Upgrading the library cannot fix a file that is not a zip.
  • Antivirus and DLP tools occasionally quarantine and replace an attachment with a text stub of the same name. The magic-byte check catches that instantly.

Performance and scale notes

The diagnosis costs nothing: reading eight bytes is a single seek, so you can run the check on every file in a batch without measurable overhead. zipfile.is_zipfile() is heavier — it seeks to the end of the file to find the central directory — but still trivial next to parsing a workbook. In a pipeline that ingests hundreds of files, validate them all first and report every bad file at once, rather than failing on the first one after twenty minutes of work. That pattern pairs well with Process multiple Excel files in parallel with Python.

Validate every input file before parsing any of them A batch job that checks magic bytes for all files first reports every bad input in one pass, instead of failing on the first bad file after parsing the earlier ones. Batch ingest with a cheap pre-flight 120 files from a share read 8 bytes each under a second total 114 valid parse these 6 rejected reported together one run, one report Failing fast on file 1 hides the other five bad inputs until tomorrow's run

Step 4: stop it recurring in an unattended job

A one-off fix on your laptop does not help the 06:00 run. Fold the diagnosis into the ingest itself so a bad input is rejected with a readable message and the rest of the batch still completes. The helper below returns a DataFrame for a genuine workbook, transparently handles the two "renamed export" cases, and raises a message a colleague can act on for anything else:

Python
"""Read a spreadsheet whatever the producer actually sent."""
from pathlib import Path
import zipfile

import pandas as pd

class UnreadableSpreadsheet(RuntimeError):
    pass

def read_any(path: str) -> pd.DataFrame:
    p = Path(path).resolve()
    head = p.read_bytes()[:8] if p.is_file() else b""

    if head.startswith(b"PK"):
        if not zipfile.is_zipfile(p):
            raise UnreadableSpreadsheet(f"{p}: zip header but damaged archive — re-download")
        return pd.read_excel(p, engine="openpyxl")
    if head.startswith(b"\xd0\xcf\x11\xe0"):
        return pd.read_excel(p, engine="xlrd")          # legacy .xls
    if head[:5].lower() in (b"<html", b"<!doc"):
        return pd.read_html(p)[0]                        # HTML table export
    if head and not head.startswith(b"<?xml"):
        return pd.read_csv(p, sep=None, engine="python", encoding="utf-8-sig")

    raise UnreadableSpreadsheet(f"{p}: unrecognised content {head!r}")

Then log the rejection rather than crashing the whole run:

Python
frames, rejected = [], []
for f in sorted(Path("/mnt/share/incoming").glob("*.xlsx")):
    try:
        frames.append(read_any(f))
    except UnreadableSpreadsheet as exc:
        rejected.append(str(exc))

if rejected:
    print(f"{len(rejected)} file(s) skipped:", *rejected, sep="\n  ")

That structure turns a hard stop into a report. The valid files still produce today's numbers, and the operations team gets a list of exactly which uploads to re-send. Pair it with the logging setup in Log Python Excel script output to a file so those messages survive the cron session.

Verify the fix before you trust it

After converting or re-fetching a file, confirm the container is sound rather than assuming it. Three assertions cover everything this error touches — a valid archive, the expected sheet parts present, and a non-trivial row count:

Python
import zipfile
from openpyxl import load_workbook

path = "report_fixed.xlsx"
assert zipfile.is_zipfile(path), "not a zip container"
with zipfile.ZipFile(path) as zf:
    assert any(n.startswith("xl/worksheets/") for n in zf.namelist()), "no sheet parts"

wb = load_workbook(path, read_only=True)
ws = wb[wb.sheetnames[0]]
print(f"{ws.max_row} rows x {ws.max_column} cols in {wb.sheetnames}")
wb.close()

read_only=True keeps the check cheap on a large workbook, and closing the handle matters on Windows, where an open file blocks the next step from replacing it.

Conclusion

BadZipFile is a statement about the container, never about your spreadsheet code. Read the first bytes to learn what the file really is, then apply the matching fix: a different reader for HTML or CSV, engine="xlrd" or a conversion for legacy .xls, decryption for a protected workbook, or a re-download for a truncated one. Wrap the check in a helper so every job in your codebase reports the filename and the true format instead of a stack trace from the zip module.

Frequently asked questions

Why does openpyxl say "File is not a zip file" when Excel opens the file? An .xlsx is a zip archive of XML parts. Excel will happily open a legacy .xls, an HTML table or a CSV that has been renamed .xlsx, silently detecting the real format. openpyxl does not — it opens the container first, and a non-zip file fails immediately.

Can I repair the file from Python? Only if the zip structure is mostly intact. zipfile.ZipFile.testzip() names the first damaged member; if the central directory itself is gone, no pure-Python fix exists and you need the file re-exported from the source system.

Why is the error intermittent on a scheduled job? The job is racing a writer. A file appears on a share or in a sync folder as soon as its first bytes land, so a timer-triggered read can open a half-written archive. Wait for a stable file size or have the producer write to a temporary name and rename on completion.

Does a zero-byte file give the same error? Yes. An empty file is not a valid zip, so it raises BadZipFile rather than a clearer message. Check st_size before reading so the log says "zero bytes" instead.

Is BadZipFile ever caused by encryption? Yes. A password-protected workbook is an OLE2 container wrapping encrypted contents, not a zip, so openpyxl reports it as not a zip file. Decrypt it first with msoffcrypto-tool.