Read Excel Files Inside a ZIP Archive with Python

Monthly exports often arrive as a ZIP: a download from a finance system, an email attachment that bundles twelve branch workbooks, a vendor's nightly drop on an SFTP server. The usual reflex is to unzip into a temporary folder and read the files from disk, then remember to clean up. Python's standard zipfile module makes that unnecessary — each member of the archive can be opened as a file object and handed straight to pd.read_excel, so nothing touches the disk and there is nothing to delete afterwards. This page shows the pattern, the junk entries that break naive loops, archives held in memory, passwords, and the confusing error you get if you pass the .zip to pandas directly. It is part of Reading Excel Files with Pandas.

Prerequisites

pip install pandas openpyxl

zipfile ships with Python. The example archive, monthly_sales.zip, contains three monthly workbooks in a 2026/ folder — plus the debris real archives collect: a __MACOSX resource-fork entry added by the macOS "Compress" command, an Excel owner-lock file (~$sales_feb.xlsx) zipped up because the workbook was open at the time, and a README.

Step 1 — List what is inside

import zipfile

with zipfile.ZipFile("monthly_sales.zip") as z:
    for info in z.infolist():
        print(f"{info.filename:35} {info.file_size:>7} bytes")

infolist() returns one ZipInfo per member with its path inside the archive, its uncompressed size and its modification time. Paths always use forward slashes, whatever system created the archive. Listing first is worth the two lines: it shows the folder structure you have to match and the junk you will have to skip.

Step 2 — Read one workbook without extracting it

import pandas as pd

with zipfile.ZipFile("monthly_sales.zip") as z:
    with z.open("2026/sales_jan.xlsx") as fh:
        jan = pd.read_excel(fh)
print(jan)

z.open() returns a ZipExtFile, a read-only, seekable file object that decompresses on demand. read_excel accepts any binary file object, so it reads the workbook exactly as if it were on disk. Keep the read inside the with blocks: once the archive is closed, the member's file object can no longer be read.

Step 3 — Read every workbook and skip the junk

A loop over namelist() that reads everything ending in .xlsx trips over both junk entries: the __MACOSX/._sales_jan.xlsx file and the ~$ lock file have the right extension but are not workbooks, and read_excel fails on them with ValueError: Excel file format cannot be determined. Filter on the file name, not just the extension:

from pathlib import PurePosixPath

def workbook_members(z):
    for info in z.infolist():
        path = PurePosixPath(info.filename)
        if info.is_dir() or path.parts[0] == "__MACOSX":
            continue
        if path.name.startswith(("~$", "._")) or path.suffix.lower() not in {".xlsx", ".xlsm", ".xls"}:
            continue
        yield info

frames = []
with zipfile.ZipFile("monthly_sales.zip") as z:
    for info in workbook_members(z):
        with z.open(info) as fh:
            df = pd.read_excel(fh)
        df.insert(0, "Source", PurePosixPath(info.filename).name)
        frames.append(df)

sales = pd.concat(frames, ignore_index=True)
print(sales)

PurePosixPath handles the forward-slash paths regardless of the operating system the script runs on. Adding a Source column before concatenating keeps every row traceable to its file, the same habit recommended in Combine Multiple Excel Files into One with Python. The suffix check is case-insensitive because Windows users produce REPORT.XLSX as often as report.xlsx.

Step 4 — Read an archive that only exists in memory

Archives downloaded over HTTP or fetched from object storage do not need to be saved first. ZipFile accepts any seekable binary file object, so wrap the bytes in io.BytesIO:

import io

from pathlib import Path

payload = Path("monthly_sales.zip").read_bytes()   # stands in for requests.get(url).content
with zipfile.ZipFile(io.BytesIO(payload)) as z:
    names = [i.filename for i in workbook_members(z)]
    feb = pd.read_excel(z.open(names[1]))
print(names)
print(feb)

In a real job payload would be requests.get(url, timeout=60).content or the body of an S3 object; the rest is identical. The approach mirrors Read an Excel File from a URL or Bytes in Python with one more layer of wrapping. The whole archive sits in memory, which is fine for the tens of megabytes typical of report bundles.

Step 5 — Password-protected archives

zipfile can decrypt the classic ZIP password scheme (often called ZipCrypto) that zip -P and most "add password" dialogs produce. Pass the password as bytes:

import subprocess

subprocess.run(["zip", "-q", "-P", "s3cret", "locked.zip", "monthly_sales.zip"], check=True)
with zipfile.ZipFile("locked.zip") as z:
    info = z.infolist()[0]
    print("encrypted:", bool(info.flag_bits & 0x1))
    try:
        z.read(info.filename)
    except RuntimeError as exc:
        print("RuntimeError:", exc)
    inner = io.BytesIO(z.read(info.filename, pwd=b"s3cret"))
print(zipfile.ZipFile(inner).namelist()[:3])

Without the password, reading raises RuntimeError: File 'monthly_sales.zip' is encrypted, password required for extraction; with it, the bytes come back and can be opened as before (here the protected member is itself a ZIP, which nests just as easily). Archives encrypted with AES — 7-Zip's and WinZip's stronger option — raise NotImplementedError: That compression method is not supported, because the standard library does not implement AES. Use the third-party pyzipper package, which offers the same interface, or ask the sender for ZipCrypto. Keep the password out of the script, as described in Store Credentials for Excel Report Scripts Safely.

Step 6 — Do not hand the ZIP to pandas

An .xlsx workbook is itself a ZIP file of XML parts — zipfile.is_zipfile("sales.xlsx") returns True. pandas therefore looks inside any ZIP it is given to decide what kind of workbook it is. A report bundle has none of the parts it looks for, and the error is baffling:

try:
    pd.read_excel("monthly_sales.zip")
except Exception as exc:
    print(type(exc).__name__, exc)

pandas 3 raises OptionError: No such keys(s): 'io.excel.zip.reader' — it fell back to treating the extension zip as a format name and found no reader registered for it. read_excel also has no compression= argument, unlike read_csv, which can read a single CSV out of a ZIP on its own. For workbooks, always open the archive with zipfile first.

Step 7 — Write the results back into an archive

The reverse trip is just as short. When the output is several workbooks — one per region, say — build each one in memory and add it to a new archive with writestr, again without temporary files:

with zipfile.ZipFile("by_region.zip", "w", zipfile.ZIP_DEFLATED) as out:
    for region, part in sales.groupby("Region"):
        buf = io.BytesIO()
        part.to_excel(buf, index=False)
        out.writestr(f"sales_{region.lower()}.xlsx", buf.getvalue())
print(zipfile.ZipFile("by_region.zip").namelist())

Each BytesIO buffer holds a complete workbook; writestr stores its bytes under the given name. The result is ready to attach to an email, as in Zip Large Excel Attachments Before Emailing, or to upload in one request.

Common pitfalls

• Junk members. __MACOSX/._* and ~$* entries end in .xlsx but are not workbooks; filter them by name.
• Reading after the archive closes. A member opened with z.open() is unreadable once the ZipFile is closed; read inside the with block or copy the bytes out with z.read().
• Backslashes in member names. Some Windows tools write folder\file.xlsx. zipfile keeps such names as given, so a PurePosixPath check sees one long name — normalise with info.filename.replace("\\", "/") when matching.
• Non-ASCII file names. Archives created without the UTF-8 flag store names in the old DOS code page and come back garbled. Python 3.11+ accepts ZipFile(path, metadata_encoding="cp437") or the sender's code page to decode them.
• Expecting compression savings. Zipping workbooks barely shrinks them, because each .xlsx is already compressed; a 2.4 MB workbook became 2.39 MB in the archive during testing.

Performance notes

Reading from a ZipExtFile costs about the same as reading from disk: on a 60,000-row workbook, streaming it from the archive took 1.7 seconds with openpyxl and copying it into io.BytesIO first took 1.5 seconds — both dominated by XML parsing. Switching the engine matters far more: engine="calamine" read the same member in 0.23 seconds. For archives with dozens of large workbooks, read members in parallel processes, each opening the archive itself; a ZipFile object should not be shared between processes.

Conclusion

Open the archive with zipfile.ZipFile, filter infolist() down to real workbooks by name, and pass each member's file object from z.open() to pd.read_excel — no extraction, no temporary folder. Wrap downloaded bytes in io.BytesIO, pass pwd= for classic password ZIPs and switch to pyzipper for AES, and never hand the archive itself to pandas, which mistakes it for a workbook and fails with an io.excel.zip.reader error.

Frequently asked questions

Can pandas read an Excel file inside a ZIP without extracting it? Yes. Open the archive with zipfile.ZipFile, open the member with z.open("folder/file.xlsx") and pass that file object to pd.read_excel. Nothing is written to disk.

Why does pd.read_excel on a .zip file fail with "No such keys(s): io.excel.zip.reader"? pandas treats any ZIP as a possible workbook package, finds none of the parts it expects, and falls back to using the extension zip as a reader name, for which no reader exists. Open the archive with zipfile and read the members instead.

How do I read every Excel file in a ZIP into one DataFrame? Loop over z.infolist(), skip directories, __MACOSX entries and names starting with ~$ or ._, read each remaining .xlsx with pd.read_excel(z.open(info)), add a column with the member name, and pd.concat the results.

Can Python open a password-protected ZIP of Excel files? The standard zipfile module decrypts classic ZipCrypto archives when you pass pwd=b"password". AES-encrypted archives raise NotImplementedError; use the pyzipper package for those.

Related

• Up one level: Reading Excel Files with Pandas — the read_excel options in depth.
• Ignore Excel Lock Files When Processing a Folder — the same ~$ files on disk.
• Download Excel Attachments from an Email Inbox — where many of these archives come from.
• Fix BadZipFile When Reading an Excel File in Python — when the workbook itself is not a valid package.