Reading a 500 MB Excel File Without Running Out of Memory

A 500 MB workbook arrives and the first attempt, pd.read_excel, ends in a memory error or a machine that stops responding. The file was never meant to be that large. It holds a million rows of an export, or a dozen sheets, or years of data nobody archived. Still, someone has to read it. The task is not to find the single magic setting but to understand why the file is expensive to read, to stream it so memory stays small, and to turn it into a format that does not have the problem. This post explains the cost, shows three ways to read the file in constant memory and gives a method for measuring. It is a spoke of Excel files that are too big or too slow.

Why a file expands

An xlsx is a zip archive. The sheet data sits in XML files inside it, compressed with deflate, which is very effective on repetitive text. A 500 MB file may contain several gigabytes of XML once unzipped. You can see the real size without extracting:

unzip -lv big.xlsx | sort -k1 -n -r | head

The listing shows each part with its compressed and uncompressed size. A sheet part of 4 GB uncompressed is the norm for such files.

Parsing that XML into Python objects costs more again. In a normal pandas or openpyxl load, every cell becomes an object with a value, a style reference and a coordinate. Python objects carry overhead of tens of bytes each, so the memory needed is many times the size of the text. openpyxl's documentation warns about this, and the multiple depends on the cell types, the styles and the strings.

Strings are shared in xlsx: text cells store an index into a shared strings table. When loaded in full, the reader resolves them, and repeated strings may or may not be deduplicated in memory.

The upshot is that memory, not time, is the limit, and the limit is hit while the file is being read, not while you process it. The remedy is not to hold the whole sheet at once.

Option 1: openpyxl in read-only mode

openpyxl can stream a sheet. In read-only mode it parses the XML incrementally and gives rows as they are read:

from openpyxl import load_workbook

wb = load_workbook("big.xlsx", read_only=True, data_only=True)
ws = wb["Data"]

header = None
batch = []
for i, row in enumerate(ws.iter_rows(values_only=True)):
    if i == 0:
        header = row
        continue
    batch.append(row)
    if len(batch) == 50_000:
        process(header, batch)
        batch.clear()
if batch:
    process(header, batch)
wb.close()

read_only=True is what keeps memory flat. data_only=True returns the cached values of formulas, not the formulas. values_only=True yields plain tuples and skips creating cell objects. Always call wb.close(), because read-only mode keeps the file open. Limits: you cannot jump to a random row, styles are mostly unavailable, and merged cells and some formatting are not reported. Reading is slower than a compiled reader. For many files it is fast enough, and it needs no extra packages (reading large Excel files with read only mode covers the details).

Do the processing in batches: convert 50,000 rows to a DataFrame, write them to Parquet or a database, and discard them. Holding all the batches in a list brings the memory problem back.

Option 2: a compiled reader

Readers written in Rust or C parse the XML much faster than Python can. The python-calamine package binds the Rust calamine library, and pandas can use it as an engine (pandas 2.2 and later):

import pandas as pd

df = pd.read_excel("big.xlsx", sheet_name="Data", engine="calamine", usecols="A:H", dtype=str)

It parses the file quickly and returns the sheet. It still builds the whole DataFrame in memory, so it helps with time and with the parse overhead, but a sheet that does not fit in memory as a DataFrame still does not fit. Pass usecols to read only the columns you need, which is often the difference between fitting and not fitting. calamine also reads .xls, .xlsb and .ods.

DuckDB can read xlsx through its Excel extension in recent releases:

INSTALL excel; LOAD excel;
COPY (SELECT * FROM read_xlsx('big.xlsx', sheet = 'Data'))
TO 'big.parquet' (FORMAT parquet);

DuckDB streams in vectors and writes Parquet as it reads, so memory use is bounded by its buffer settings, which you can limit with SET memory_limit = '4GB';. Check the DuckDB documentation for your version's read_xlsx options and type inference (loading Excel files with DuckDB). This is attractive for the one-time conversion.

Option 3: convert once, then read the better format

If the workbook will be read more than once, parse it once and keep the result in a format built for data. Parquet is columnar and compressed; reading selected columns from it takes seconds. CSV is simpler and bigger.

A streaming conversion with openpyxl and pyarrow:

import pyarrow as pa
import pyarrow.parquet as pq
from openpyxl import load_workbook

wb = load_workbook("big.xlsx", read_only=True, data_only=True)
ws = wb["Data"]
rows = ws.iter_rows(values_only=True)
header = [str(h) for h in next(rows)]

writer = None
batch = []
def flush():
    global writer
    table = pa.Table.from_pylist([dict(zip(header, r)) for r in batch])
    if writer is None:
        writer = pq.ParquetWriter("big.parquet", table.schema)
    writer.write_table(table)
    batch.clear()

for r in rows:
    batch.append(r)
    if len(batch) >= 100_000:
        flush()
if batch:
    flush()
writer.close()
wb.close()

Each batch becomes a row group, so memory stays proportional to the batch. The schema is taken from the first batch; if a later batch has a column of a different type (numbers in the first 100,000 rows, a stray text later), the write fails. Either read everything as strings and cast later, or fix the schema up front. See converting a huge Excel file to CSV or Parquet.

Measure instead of guessing

The relation between file size and memory depends on your data, so measure peak memory and time on your own file. Run each approach in its own process and record the peak resident set size:

/usr/bin/time -v python read_stream.py big.xlsx      # Linux: look for "Maximum resident set size"
/usr/bin/time -l python read_stream.py big.xlsx      # macOS: "maximum resident set size"

Repeat three to five times and take the median, and note the hardware and versions. Run on a smaller copy first (take the first 50,000 rows into a new workbook) and scale up, which tells you how memory grows. For a fair comparison between readers, see benchmarking Excel readers in Python. Keep the numbers with the machine description, since a figure without hardware is not a measurement.

Make the file smaller in the first place

If you control the producer, ask for a better format. A million-row export as CSV or Parquet is smaller and faster to read than any xlsx. If xlsx is unavoidable, check why the file is big: unused styles, a used range extending far below the data, embedded images, a pivot cache saved with the file (reducing workbook size by removing unused styles). A file that is large because of formatting bloat can shrink dramatically without losing data.

Row limits

A worksheet holds at most 1,048,576 rows. A file with a half gigabyte of data may be many sheets, or a data set already truncated at the limit by an earlier export (Excel row and column limits). Check the last row of each sheet: a sheet that ends at exactly row 1,048,576 has lost data.

When no reader will make the file manageable

The file is damaged. A reader that fails halfway may be hitting corruption, not size.

The data is not tabular. A workbook full of charts, merged report layouts and formulas has little to stream. Ask for the underlying data.

You need formulas, not values. Reading formulas means parsing more and holding more; process sheet by sheet and avoid whole-workbook loads.

The job is recurring. A weekly 500 MB workbook means the process upstream is wrong. Replace it with a file or a feed that suits machines (analyzing more than a million rows without Excel).

See the openpyxl documentation on optimized modes and the python-calamine project. To convert a small extract to JSON, use the CSV to JSON converter.