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

Read an Excel File from a URL or Bytes in Python

Load a workbook straight from HTTP, S3, a database blob or an upload: BytesIO, content-type checks, authentication, retries, streaming downloads and caching.

Workbooks increasingly arrive over a network rather than sitting on a disk: a partner's reporting portal, an S3 object, a database blob, a file a user just uploaded. Every Python Excel reader accepts a file-like object, so none of that needs a temporary file — but reading straight from a response has failure modes a local read does not, starting with the login page that arrives instead of the spreadsheet. This guide reads a workbook from each source safely. It belongs to Handling Excel File Formats and Conversions.

Sources that all end up as the same bytes An HTTP response, an S3 object, a database blob and a web upload each produce a byte string, which is wrapped in BytesIO and read by the same reader. Four sources, one reader HTTP response S3 object body database blob web upload bytes check before parsing io.BytesIO read_excel / load_workbook

Prerequisites

Bash
pip install pandas openpyxl requests

Add boto3 for the S3 section.

The one-liner, and why to outgrow it

pandas will fetch a URL itself:

Python
import pandas as pd

df = pd.read_excel("https://example.com/reports/latest.xlsx")

That is fine for a public file in a notebook. In a scheduled job it is a liability: no timeout, no retry, no authentication header, and no way to notice that the server returned a 200 with an HTML login page. Fetch deliberately instead.

Fetch, verify, then read

Python
"""Download a workbook and read it, refusing anything that is not one."""
import io

import pandas as pd
import requests

def read_excel_url(url: str, **kwargs) -> pd.DataFrame:
    resp = requests.get(url, timeout=30, headers={"Accept": "*/*"})
    resp.raise_for_status()

    content_type = resp.headers.get("Content-Type", "")
    if "html" in content_type:
        raise ValueError(f"{url} returned HTML — probably a login or error page")
    if not resp.content[:2] == b"PK":
        raise ValueError(f"{url} is not an .xlsx (starts with {resp.content[:8]!r})")

    return pd.read_excel(io.BytesIO(resp.content), engine="openpyxl", **kwargs)

df = read_excel_url("https://example.com/reports/latest.xlsx", sheet_name="Q3")

The magic-byte check is the important one. Content-Type is often wrong — plenty of servers label an .xlsx as application/octet-stream — but the first two bytes never lie: a modern workbook is a zip, so it starts with PK. The whole family of failures is catalogued in Fix BadZipFile when reading an Excel file in Python.

Authenticate

Most real sources need credentials, and they belong in the environment, not the code:

Python
import os

import requests

session = requests.Session()
session.headers["Authorization"] = f"Bearer {os.environ['REPORT_API_TOKEN']}"

resp = session.get("https://api.example.com/exports/monthly.xlsx", timeout=30)
resp.raise_for_status()

Basic auth is auth=(user, password); a cookie-based portal usually means posting to a login endpoint first and reusing the session. Using one Session matters beyond credentials: it reuses the TCP connection, which is a real saving when fetching many files.

Retry transient failures

Report portals fail intermittently, and a nightly job should not.

Python
from requests.adapters import HTTPAdapter
from urllib3.util.retry import Retry

retry = Retry(
    total=4,
    backoff_factor=1.5,                     # 0s, 1.5s, 3s, 6s
    status_forcelist=(429, 500, 502, 503, 504),
    allowed_methods=frozenset(["GET"]),
)
session = requests.Session()
session.mount("https://", HTTPAdapter(max_retries=retry))

Retrying only idempotent methods and only on genuinely transient statuses is what keeps this from hiding a real problem — a 404 or a 403 fails immediately, as it should. The job-level equivalent is in Retry a failed Excel report job in Python.

Checks between the download and the parse A response passes a status check, a content-type check and a magic-byte check before it is parsed, so an error page never reaches the Excel reader. Three gates, each one line response status 2xx? raise_for_status not HTML? Content-Type starts with PK? then parse A 200 response is not evidence that the body is a workbook

Read from S3 and other object storage

Python
import io

import boto3
import pandas as pd

s3 = boto3.client("s3")
buffer = io.BytesIO()
s3.download_fileobj("reports-bucket", "monthly/sales.xlsx", buffer)
buffer.seek(0)

df = pd.read_excel(buffer, engine="openpyxl")

Do not hand the streaming body from get_object straight to the reader. An .xlsx is a zip, and a zip is read by seeking to its directory at the end of the file — a forward-only stream cannot do that. Download into a buffer first, exactly as above.

Read an upload or a database blob

A web upload is already a file-like object, so it can be read directly:

Python
# Flask
df = pd.read_excel(request.files["report"], engine="openpyxl")

# Django
df = pd.read_excel(request.FILES["report"], engine="openpyxl")

A blob column is bytes, so wrap it:

Python
import io

row = cursor.execute("SELECT content FROM uploads WHERE id = ?", (upload_id,)).fetchone()
df = pd.read_excel(io.BytesIO(row[0]), engine="openpyxl")

In both cases validate before parsing — an upload is untrusted input, and the same magic-byte check applies.

Stream a large download to disk

Holding a large file in memory and then parsing it means paying twice. Stream it to a temporary path instead:

Python
import tempfile
from pathlib import Path

import pandas as pd
import requests

with requests.get(url, stream=True, timeout=60) as resp:
    resp.raise_for_status()
    with tempfile.NamedTemporaryFile(suffix=".xlsx", delete=False) as tmp:
        for chunk in resp.iter_content(chunk_size=1 << 20):
            tmp.write(chunk)
        path = Path(tmp.name)

try:
    df = pd.read_excel(path, engine="calamine")
finally:
    path.unlink(missing_ok=True)

The finally matters: a temporary file that outlives a crashed job fills a disk over weeks.

Cache what you fetch

If the same file is fetched repeatedly, use HTTP's own caching rather than re-downloading:

Python
from pathlib import Path

import requests

def fetch_cached(url: str, cache: Path) -> bytes:
    headers = {}
    etag_file = cache.with_suffix(".etag")
    if cache.exists() and etag_file.exists():
        headers["If-None-Match"] = etag_file.read_text()

    resp = requests.get(url, headers=headers, timeout=30)
    if resp.status_code == 304:
        return cache.read_bytes()

    resp.raise_for_status()
    cache.write_bytes(resp.content)
    if "ETag" in resp.headers:
        etag_file.write_text(resp.headers["ETag"])
    return resp.content

A 304 Not Modified costs a round trip instead of a download, which turns a slow hourly job into a fast one whenever the source has not changed.

Wrap it all in one loader

The pieces above belong together, because every caller wants the same behaviour: authenticate, retry, verify, parse, and fail with a message naming the source. One function does it, and the rest of the codebase never touches requests again:

Python
"""loader.py — the only place the network meets the Excel reader."""
import io
import os

import pandas as pd
import requests
from requests.adapters import HTTPAdapter
from urllib3.util.retry import Retry

class RemoteWorkbookError(RuntimeError):
    pass

def _session() -> requests.Session:
    session = requests.Session()
    retry = Retry(total=4, backoff_factor=1.5,
                  status_forcelist=(429, 500, 502, 503, 504),
                  allowed_methods=frozenset(["GET"]))
    session.mount("https://", HTTPAdapter(max_retries=retry))
    if token := os.getenv("REPORT_API_TOKEN"):
        session.headers["Authorization"] = f"Bearer {token}"
    return session

def load_remote_excel(url: str, **read_kwargs) -> pd.DataFrame:
    try:
        resp = _session().get(url, timeout=30)
        resp.raise_for_status()
    except requests.RequestException as exc:
        raise RemoteWorkbookError(f"{url}: {exc}") from exc

    if resp.content[:2] != b"PK":
        preview = resp.content[:80].decode("utf-8", "replace")
        raise RemoteWorkbookError(f"{url}: not a workbook — starts {preview!r}")

    read_kwargs.setdefault("engine", "openpyxl")
    return pd.read_excel(io.BytesIO(resp.content), **read_kwargs)

Decoding the first bytes into the error message is a small touch that pays for itself: the log line then reads not a workbook — starts '<!DOCTYPE html><title>Sign in', which diagnoses an expired token without anybody reproducing the run.

What the error message should contain A useful failure names the source, the status and the first bytes of the body, which together identify an expired token, a moved file or a wrong format without a rerun. unhelpful actionable BadZipFile: File is not a zip file no source, no status, no clue url + status + first bytes "starts '<!DOCTYPE html'" — token expired The cost is one decode; the saving is a whole debugging session

Common pitfalls and gotchas

  • Trusting a 200. Portals return login pages with a 200 status. Check the bytes.
  • Streaming a zip. get_object()["Body"] and resp.raw are forward-only; buffer first.
  • Forgetting seek(0). A buffer you just wrote is positioned at the end.
  • No timeout. A requests.get without one can hang a scheduled job indefinitely.
  • Credentials in code. Read them from the environment; a token in a repository outlives the job that needed it.

Performance and scale notes

Network time usually dominates the parse, so the biggest wins are conditional requests and connection reuse rather than a faster reader — though engine="calamine" still helps on large files. When fetching many workbooks, run the downloads concurrently with a thread pool (they are I/O-bound) and the parsing in processes (it is CPU-bound); mixing the two in one pool wastes both. That split is the same one described in Process multiple Excel files in parallel with Python.

Conclusion

Reading a workbook from a URL or a byte string is a one-liner surrounded by four guards worth having: a status check, a content-type check, a magic-byte check and a timeout. Buffer object-storage bodies rather than streaming them into the reader, stream genuinely large downloads to a temporary file, and use ETag caching when the same file is fetched on a schedule. Then the network becomes just another source, with no temporary files and no surprises.

Frequently asked questions

Can pandas read a URL directly? Yes, pd.read_excel("https://…") works for a simple public file. Fetching with requests first is better in a job, because you can set headers and timeouts, check the status and content type, and retry — none of which the one-liner allows.

Why does the read fail with "format cannot be determined"? A BytesIO has no filename, so pandas cannot use the extension and falls back to sniffing the bytes. If the response was an HTML login page rather than a workbook, that sniff fails. Check the status code and content type first, and pass engine= explicitly.

Do I need to save the file to disk at all? No. Every reader accepts a file-like object, so io.BytesIO(response.content) is enough. Save to disk only when you want a cache or an audit copy.

How do I read a workbook from S3? Download the object body into BytesIO with download_fileobj and read that. Do not stream the body directly into the reader — an .xlsx needs random access to its zip directory.

What about a very large download? Stream it to a temporary file with iter_content, then read from the path. Holding a hundred megabytes in memory and then parsing it doubles the peak.