Python Excel AutomationPractical guides for developers
Start learning
Guide
Automating Reporting WorkflowsDeep dive

Read an Excel File Directly from Amazon S3 with pandas

Load a workbook from S3 into a DataFrame without a temporary file: BytesIO reads, paginated prefix scans, readable error translation, and a Parquet cache for repeat runs.

Reports increasingly source their input from object storage rather than a shared drive, and the natural instinct — download the file, then open it — adds a disk write and a cleanup problem to what can be a single call. This guide, part of Publishing Excel Reports to Cloud Storage, covers reading a workbook from S3 straight into a DataFrame, choosing between in-memory and on-disk, and the failure modes worth handling explicitly.

From object to DataFrame without touching disk A get_object call returns a streaming body, its bytes wrap in a BytesIO buffer, and the parser reads that buffer exactly as it would read a file. in-memory read get_object streaming body BytesIO bytes in memory read_excel a typed frame no temporary file to write, name, or clean up

Prerequisites

Bash
pip install pandas boto3 s3fs openpyxl python-calamine

Credentials from the standard chain — an instance role, a task role, or ~/.aws/credentials. Do not put keys in the script; the reasoning is the same as in Upload an Excel Report to Amazon S3 with boto3.

The one-line version

With s3fs installed, pandas accepts an S3 URI wherever it accepts a path.

Python
import pandas as pd

frame = pd.read_excel(
    "s3://reporting-artifacts/exports/orders-2026-09-01.xlsx",
    sheet_name="Detail",
    engine="calamine",
)
print(frame.shape)

That is genuinely all it takes, and it is the right thing to reach for in a notebook. In a scheduled job it has two drawbacks worth knowing: the credential and permission errors surface as fsspec exceptions that are harder to read than boto3's, and you have no hook to add retries or a timeout.

The explicit version

Python
import io
import boto3
import pandas as pd
from botocore.exceptions import ClientError

s3 = boto3.client("s3")

def read_workbook(bucket: str, key: str, **kwargs) -> pd.DataFrame:
    try:
        response = s3.get_object(Bucket=bucket, Key=key)
    except ClientError as error:
        code = error.response["Error"]["Code"]
        if code == "NoSuchKey":
            raise FileNotFoundError(f"s3://{bucket}/{key} does not exist") from error
        if code in ("AccessDenied", "403"):
            raise PermissionError(f"no permission to read s3://{bucket}/{key}") from error
        raise
    return pd.read_excel(io.BytesIO(response["Body"].read()), engine="calamine", **kwargs)

frame = read_workbook("reporting-artifacts", "exports/orders-2026-09-01.xlsx", sheet_name="Detail")

Translating the two error codes people actually hit into Python's own exception types is the whole point of the extra ten lines: a missing file and a missing permission produce very different remedies, and ClientError: An error occurred (403) does not distinguish them for the person reading the log at seven in the morning.

Reading every sheet, and reading several files

Reading a whole prefix without missing files List with a paginator because a single call returns at most a thousand keys, filter by extension, tag each frame with its source key, and raise when nothing matched. 1 Use a paginator list_objects_v2 truncates at 1,000 keys 2 Filter by extension prefixes collect more than workbooks 3 Tag each frame assign the key so rows keep their origin 4 Raise on empty an empty frame becomes an empty report the truncation is silent, which is what makes it expensive
Python
tabs = read_workbook("reporting-artifacts", "exports/orders.xlsx", sheet_name=None)
print({name: frame.shape for name, frame in tabs.items()})

# Every workbook under a prefix, concatenated with a source column
def read_prefix(bucket: str, prefix: str) -> pd.DataFrame:
    paginator = s3.get_paginator("list_objects_v2")
    frames = []
    for page in paginator.paginate(Bucket=bucket, Prefix=prefix):
        for item in page.get("Contents", []):
            if item["Key"].endswith((".xlsx", ".xlsm")):
                part = read_workbook(bucket, item["Key"])
                frames.append(part.assign(Source=item["Key"].rsplit("/", 1)[-1]))
    if not frames:
        raise FileNotFoundError(f"no workbooks under s3://{bucket}/{prefix}")
    return pd.concat(frames, ignore_index=True)

The paginator is not optional detail: list_objects_v2 returns at most a thousand keys per call, and code that ignores the continuation token silently processes the first thousand. Raising when nothing matched is the other half — an empty concat produces an empty frame, and an empty frame flows downstream as a report with no rows rather than as an error.

Finding the newest file

Python
def newest_key(bucket: str, prefix: str, suffix: str = ".xlsx") -> str:
    paginator = s3.get_paginator("list_objects_v2")
    candidates = [
        item for page in paginator.paginate(Bucket=bucket, Prefix=prefix)
        for item in page.get("Contents", [])
        if item["Key"].endswith(suffix)
    ]
    if not candidates:
        raise FileNotFoundError(f"nothing matching {suffix} under {prefix}")
    return max(candidates, key=lambda item: item["LastModified"])["Key"]

Scanning for the newest is convenient and does not scale: on a prefix with tens of thousands of objects it is several API calls and a growing list. Where the pipeline controls the naming, putting the date in the key and constructing it — exports/orders-{date:%Y-%m-%d}.xlsx — is both faster and more honest, because the job then fails when the expected file is missing rather than silently processing an older one.

Streaming a large workbook to disk

An .xlsx file expands substantially when parsed, so for a large source it can be better to spool it to disk and let the parser stream from there rather than holding the compressed bytes and the parsed frame simultaneously.

Python
import tempfile
from pathlib import Path

def read_large(bucket: str, key: str, **kwargs) -> pd.DataFrame:
    with tempfile.TemporaryDirectory() as directory:
        target = Path(directory) / Path(key).name
        s3.download_file(bucket, key, str(target))
        return pd.read_excel(target, engine="calamine", **kwargs)

download_file uses a managed multipart transfer with retries built in, which for a large object is more robust than a single get_object. The temporary directory removes itself, which is what makes this safe inside a container with limited disk.

Confirming you read the whole file

A truncated download produces a BadZipFile if you are lucky and a partially-read workbook if you are not. Comparing the bytes you received against the object's declared length turns that into a clear failure, and it costs one comparison.

Python
def read_verified(bucket: str, key: str, **kwargs) -> pd.DataFrame:
    response = s3.get_object(Bucket=bucket, Key=key)
    declared = response["ContentLength"]
    payload = response["Body"].read()
    if len(payload) != declared:
        raise IOError(f"s3://{bucket}/{key}: read {len(payload)} of {declared} bytes")
    return pd.read_excel(io.BytesIO(payload), engine="calamine", **kwargs)

The same response carries an ETag, which for a single-part upload is the object's MD5. Where the producer records a checksum alongside the file, comparing it here catches corruption that a length check cannot — a rare failure, but a cheap one to rule out on data that will drive a report.

Reading ContentLength before deciding how to read is also how the in-memory and spool-to-disk paths choose between themselves:

Python
head = s3.head_object(Bucket=bucket, Key=key)
frame = (read_large if head["ContentLength"] > 50_000_000 else read_workbook)(bucket, key)

head_object is a cheap metadata call, so branching on it costs almost nothing and removes the guess about which path a given file needs.

Reading from other providers

The pattern generalises, and the only part that changes is the client. Azure Blob Storage and Google Cloud Storage both expose a bytes download that feeds the same BytesIO.

Python
# Azure
from azure.storage.blob import BlobServiceClient
blob = BlobServiceClient.from_connection_string(conn).get_blob_client("reports", key)
frame = pd.read_excel(io.BytesIO(blob.download_blob().readall()), engine="calamine")

# Google Cloud Storage
from google.cloud import storage
bucket = storage.Client().bucket("reporting-artifacts")
frame = pd.read_excel(io.BytesIO(bucket.blob(key).download_as_bytes()), engine="calamine")

Keeping the parse behind one function that takes bytes — rather than one per provider — means a pipeline that moves between clouds changes its fetch and nothing else. That is also what makes the read testable, since a test can hand the function bytes from a fixture without touching a network. The write-side equivalents are in Save Excel Reports to Google Drive with Python.

Common pitfalls

SymptomCauseFix
NoSuchKey on a key that existsWrong bucket, or a leading slash in the keyS3 keys never start with /
403 with valid credentialsThe role lacks s3:GetObject for that prefixCheck the policy, not the credentials
Only 1,000 files processedlist_objects_v2 truncation ignoredUse a paginator
BadZipFile on readThe object is not a workbook, or the download was truncatedCheck ContentLength against the bytes read
Reads are slow and repeatedThe same file parsed on every runConvert to Parquet once and read that
Memory spike on a large fileBytes and parsed frame held togetherSpool to a temporary file first

Performance and scale

Where the time goes on an S3 workbook read Transferring a compressed workbook is quick, parsing it with openpyxl is the dominant cost, calamine cuts that substantially, and reading a cached Parquet copy makes it negligible. transfer compressed bytes parse with openpyxl dominates parse with calamine same bytes read Parquet copy converted once relative cost the network is rarely the bottleneck; the parser is

Two costs make up an S3 read: the transfer and the parse. The transfer is usually the smaller of the two — .xlsx is compressed — and the parse dominates, which means the engine choice matters as much here as it does locally. calamine is several times faster than openpyxl on the same bytes.

The larger win is not reading the workbook at all on repeat runs:

Python
def cached_parquet(bucket: str, key: str) -> pd.DataFrame:
    parquet_key = key.rsplit(".", 1)[0] + ".parquet"
    try:
        return pd.read_parquet(f"s3://{bucket}/{parquet_key}")
    except FileNotFoundError:
        frame = read_workbook(bucket, key)
        frame.to_parquet(f"s3://{bucket}/{parquet_key}", index=False)
        return frame

For a file read by several jobs a day, converting once turns a multi-second parse into a fraction of a second for every consumer — the argument made in Convert Excel Files to Parquet with Python.

Conclusion

pd.read_excel("s3://…") is the right call in a notebook; in a job, use get_object with BytesIO so you can translate NoSuchKey and AccessDenied into errors that name the problem. Paginate when listing, construct keys from dates rather than scanning for the newest, spool large workbooks to a temporary file, and cache a Parquet copy when the same source is read more than once.

Frequently asked questions

Does pandas read from s3:// directly? Yes, when s3fs is installed — handing read_excel an s3:// URI works, and it uses your usual AWS credential chain. It is convenient for exploration; for production, an explicit boto3 get_object gives clearer errors and lets you control retries.

Should I download to a temporary file or read into memory? Read into memory with BytesIO for anything that comfortably fits — it avoids a disk write and cleans itself up. Download to a file when the workbook is large enough that holding both the bytes and the parsed frame is a problem, or when a library insists on a path.

How do I read only the newest file in a prefix? List the prefix with a paginator and take the maximum LastModified. For anything more than a few hundred keys, prefer a naming convention with the date in the key so you can construct the name rather than scanning.

Why is my read slow from a Lambda? Usually cold-start plus a large workbook. Convert the source to Parquet once and have the function read that instead — it is smaller to transfer and much faster to parse, which matters most where the compute is billed by the millisecond.