Fixing BadZipFile When Reading an XLSX With Python

zipfile.BadZipFile: File is not a zip file is what openpyxl, pandas and many other libraries show when they cannot make sense of an .xlsx. The error is accurate and unhelpful. The library wanted a zip, because an xlsx is a zip of XML parts, and it was given something else. Working out what that something is takes a minute, and it points straight at the fix. This post lists the causes in the order they occur and shows the code for each check. It is a spoke of understanding Excel errors when data comes from code.

The two-minute diagnosis

Run these checks in order. They separate almost all the causes.

import os, zipfile

path = "report.xlsx"
print("size:", os.path.getsize(path))

with open(path, "rb") as f:
    print("head:", f.read(8))

print("is zip:", zipfile.is_zipfile(path))

Read the three lines:

• size 0: the file is empty. The writer failed, or the download did.

• **head starts with b'PK\x03\x04'**: it looks like a zip. If is_zipfile is also true, the archive is probably fine and the error is elsewhere. If it is false, the end of the file is missing.

• **head is b'\xd0\xcf\x11\xe0\xa1\xb1\x1a\xe1'**: a compound document, either an old .xls or an encrypted workbook.

• **head is b'<html', b'<!DOCT' or b'<?xml'**: markup, not a workbook.

• head is readable text with commas: a CSV.

The file command says the same from a shell: file report.xlsx. If the answer is "ASCII text" or "HTML document", you have your cause.

Cause: an HTML page or an error response

Download scripts are the usual source. A request returns an HTML login page, a 403 message or a "file not found" body, and the script saves it as report.xlsx without checking the status. Open the file in a text editor and read it. Fix the request: check the HTTP status, send the credentials, and verify the content type before saving.

import requests

r = requests.get(url, timeout=60)
r.raise_for_status()
if not r.content.startswith(b"PK\x03\x04"):
    raise ValueError(f"not an xlsx: {r.headers.get('content-type')}, {r.content[:60]!r}")
open("report.xlsx", "wb").write(r.content)

The startswith check catches a 200 response that carries an error page, which some servers send.

Cause: a truncated or empty file

A download interrupted halfway, a copy that ran out of space or a writer that crashed leaves a file that begins with PK and ends nowhere. The zip index lives at the end of the archive, so a missing tail gives BadZipFile (sometimes worded "File is not a zip file" and sometimes "Bad magic number for central directory"). Compare the size with the source or the Content-Length header, and download again. For a file you must recover, see Excel cannot open the file because the file is corrupt.

Cause: an old .xls or another format

openpyxl cannot read the old binary format. An .xls file renamed to .xlsx, or a legacy export given the wrong name, starts with D0 CF 11 E0 and fails. Read it with xlrd instead (fixing the Python xlrd error), or convert it to xlsx first. A CSV renamed to .xlsx should be read with read_csv.

Cause: an encrypted workbook

A workbook protected with an open password is encrypted as a whole and stored as a compound document, not a zip. It has the same D0 CF 11 E0 start as an old xls, which is why the error looks identical. The file needs decrypting with the password before a zip reader can see it. The msoffcrypto-tool package does this in Python:

import io
import msoffcrypto
import pandas as pd

buf = io.BytesIO()
with open("secret.xlsx", "rb") as f:
    office = msoffcrypto.OfficeFile(f)
    office.load_key(password="from-a-secrets-store")
    office.decrypt(buf)

df = pd.read_excel(buf)

Take the password from a secrets store or an environment variable, not from the source file. Sheet protection and workbook structure protection are different: they do not encrypt the file, and a protected sheet still opens as a normal zip (password protected Excel files and scripts).

Cause: reading uploaded bytes wrongly

A web application receives the workbook as an upload, and the code reads it incorrectly. The usual mistakes:

• Opening in text mode. open(path) without "rb" decodes the bytes as text and corrupts them. Always open binary.

• Reading the stream twice. A framework's file object is at the end after the first read. Call seek(0) or read the content once into memory.

• Passing the wrong thing. Some libraries want a path or a file-like object, not the framework's upload wrapper. Wrap the bytes: io.BytesIO(upload.read()).

• Truncating at a limit. A proxy or the framework cuts uploads at a size limit and passes the first part along.

• Base64 or multipart not decoded. The code saved the encoded form.

import io
import pandas as pd

data = upload.read()                      # bytes, read once
if not zipfile.is_zipfile(io.BytesIO(data)):
    raise ValueError("The file is not a valid xlsx")
df = pd.read_excel(io.BytesIO(data), engine="openpyxl")

Validate before parsing, and return a clear message to the user. A stack trace from deep in a library helps nobody who uploaded the file (checking an Excel upload before reading it).

Cause: temporary file and encoding problems

A script that writes an xlsx and then reads it back while the writer still holds it open can read a partial file. Close the writer first (wb.save completes, with pd.ExcelWriter(...) exits), then read. Another case is a file transformed in transit, for instance a Windows line-ending conversion from a text-mode transfer such as ASCII-mode FTP. That alters bytes inside the zip and breaks CRC checks. Use binary mode for transfers.

A file that is a valid zip but still fails

If zipfile.is_zipfile is true and the error remains, the archive opens but is not a workbook, or a part is damaged. Test it:

unzip -t report.xlsx
unzip -l report.xlsx | head

A real workbook contains [Content_Types].xml, xl/workbook.xml and a folder xl/worksheets/. If those are missing, it is a different kind of zip, perhaps a docx, a zip of CSV files, or a zip containing the xlsx. If a part has a bad CRC, the file is damaged (fixing Excel found unreadable content after a script export covers invalid XML inside a good zip).

A guard for pipelines

Put the checks in one function and call it before every read:

def check_xlsx(path):
    size = os.path.getsize(path)
    if size == 0:
        raise ValueError("empty file")
    with open(path, "rb") as f:
        head = f.read(8)
    if head.startswith(b"\xd0\xcf\x11\xe0"):
        raise ValueError("compound file: old .xls or an encrypted workbook")
    if not head.startswith(b"PK\x03\x04"):
        raise ValueError(f"not a zip: starts with {head!r}")
    with zipfile.ZipFile(path) as z:
        bad = z.testzip()
        if bad:
            raise ValueError(f"damaged part: {bad}")
        if "xl/workbook.xml" not in z.namelist():
            raise ValueError("zip without xl/workbook.xml")

testzip reads every part and returns the name of the first bad one. The check costs a read of the file, which is small next to parsing it, and it turns an obscure traceback into a message that names the cause.

When the zip is fine and the error is elsewhere

The error is raised inside a different library. A BadZipFile from a path you did not open may come from a docx, a Python wheel or a cached download.

A zip bomb. A small file that expands to gigabytes is valid but dangerous. Check the uncompressed sizes before reading untrusted workbooks (zip bombs and oversized XLSX uploads).

The path points at a directory or a lock file. Excel creates ~$report.xlsx lock files while a workbook is open. They are small and not zips. Skip names that start with ~$ when scanning a folder.

The environment is wrong. A container may not mount the file you think it does, and you are reading a zero-byte placeholder.

Python documents the exception in its zipfile module reference, and openpyxl describes the file handling in its documentation. To convert a recovered extract, use the CSV to JSON converter.