Export a Large Database Table to Excel in Chunks

"Can you send me the whole table in Excel?" is a request that is easy to fulfil for ten thousand rows and surprisingly easy to get wrong for two million. The straightforward approach — pd.read_sql into a DataFrame, then to_excel — holds the entire result in memory twice over (once as the DataFrame, once as the workbook being built), and fails outright when the row count passes Excel's limit of 1,048,576 rows per sheet. The robust approach streams: fetch rows from the database in chunks, write each chunk to the workbook and discard it, and start a new sheet before the limit. With XlsxWriter's constant_memory mode, memory stays flat no matter how many rows go through. This page builds that export, with the database side done properly too. It belongs to Moving Data Between Excel and Databases.

Prerequisites

pip install pandas sqlalchemy xlsxwriter openpyxl

warehouse.db is a SQLite database with a movements table of 250,000 stock movements: id, timestamp, SKU, warehouse, quantity and unit cost. SQLite keeps the example self-contained; the same code works against PostgreSQL, SQL Server or MySQL by changing the connection URL, and the server-side cursor note in Step 1 matters most for those.

Step 1 — Fetch the table in chunks

pd.read_sql with chunksize returns an iterator of DataFrames instead of one large frame. With SQLAlchemy, add stream_results=True so the database driver uses a server-side cursor and does not pull the whole result into the client before the first chunk is returned:

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine("sqlite:///warehouse.db")
query = text("SELECT id, moved_at, sku, warehouse, qty, unit_cost FROM movements ORDER BY id")

with engine.connect().execution_options(stream_results=True) as conn:
    sizes = [len(chunk) for chunk in pd.read_sql(query, conn, chunksize=50_000)]
print(len(sizes), "chunks:", sizes)

Five chunks of 50,000 rows each arrive one at a time. The chunksize controls how many rows pandas builds into each DataFrame; stream_results=True controls whether the driver buffers the full result first. Without it, drivers such as psycopg for PostgreSQL fetch every row into client memory at the moment the query executes, and chunking in pandas only splits up data that is already all in memory. SQLite does not buffer that way, so the option is harmless here and essential there. Ordering by the primary key makes the export deterministic, which matters when someone compares two exports.

Step 2 — Write each chunk straight to disk

XlsxWriter's constant_memory mode writes each row to a temporary file as soon as the next row starts, instead of keeping the whole sheet in memory. The cost is that rows must be written in order, top to bottom — exactly what streaming chunks does:

import xlsxwriter

def export(conn, query, path, chunksize=50_000, max_rows=1_048_575):
    book = xlsxwriter.Workbook(path, {"constant_memory": True})
    head = book.add_format({"bold": True, "bg_color": "#EEF2FF", "bottom": 1})
    when = book.add_format({"num_format": "yyyy-mm-dd hh:mm"})
    money = book.add_format({"num_format": "#,##0.00"})
    sheet, row, sheets, total = None, 0, 0, 0
    for chunk in pd.read_sql(query, conn, chunksize=chunksize, parse_dates=["moved_at"]):
        for rec in chunk.itertuples(index=False):
            if sheet is None or row > max_rows:
                sheets += 1
                sheet = book.add_worksheet(f"Movements {sheets}")
                sheet.write_row(0, 0, list(chunk.columns), head)
                sheet.set_column(1, 1, 17)
                sheet.freeze_panes(1, 0)
                row = 1
            sheet.write_number(row, 0, rec.id)
            sheet.write_datetime(row, 1, rec.moved_at.to_pydatetime(), when)
            sheet.write_string(row, 2, rec.sku)
            sheet.write_string(row, 3, rec.warehouse)
            sheet.write_number(row, 4, rec.qty)
            sheet.write_number(row, 5, rec.unit_cost, money)
            row += 1
            total += 1
    book.close()
    return sheets, total

with engine.connect().execution_options(stream_results=True) as conn:
    print(export(conn, query, "movements.xlsx"))

Each row is written with a typed method — write_datetime with a date format, write_number with a money format — so the timestamps arrive in Excel as real dates and the costs as numbers, not text. parse_dates makes pandas convert the SQLite text timestamps while reading. The header row, column width and frozen pane are set when each sheet is created, because in constant_memory mode nothing can be changed in a row once a later row has been written. Writing cell by cell looks slow, but XlsxWriter is fast at it; the generic to_excel would not work here anyway, because it cannot append to a sheet across chunks.

Step 3 — Roll over before the row limit

An Excel sheet holds at most 1,048,576 rows, header included. The export function counts rows per sheet and starts a new sheet with its own header when the next row would exceed the limit. To see the rollover without generating a million rows, lower the limit:

with engine.connect().execution_options(stream_results=True) as conn:
    sheets, total = export(conn, query, "movements_split.xlsx", max_rows=100_000)
print(sheets, "sheets for", total, "rows")

With a limit of 100,000 data rows, the 250,000 movements land on three sheets — 100,000, 100,000 and 50,000 rows — each with a header and frozen top row. In real use leave the default; the function then only splits tables that genuinely exceed one sheet. For very large exports ask whether Excel is the right format at all: a 3-million-row workbook is slow to open and hard to use, and a CSV or Parquet file, or a filtered extract, usually serves the requester better. Excel vs CSV vs Parquet for Python Data Pipelines helps with that conversation.

Step 4 — Measure memory against the simple approach

The point of streaming is flat memory. tracemalloc measures the peak memory Python allocates during each approach:

import tracemalloc

def peak_mb(fn):
    tracemalloc.start()
    fn()
    peak = tracemalloc.get_traced_memory()[1]
    tracemalloc.stop()
    return round(peak / 1024 / 1024, 1)

def simple():
    df = pd.read_sql(query, engine, parse_dates=["moved_at"])
    df.to_excel("movements_simple.xlsx", index=False, engine="xlsxwriter")

def streamed():
    with engine.connect().execution_options(stream_results=True) as conn:
        export(conn, query, "movements_streamed.xlsx")

print("simple:   ", peak_mb(simple), "MB peak")
print("streamed: ", peak_mb(streamed), "MB peak")

The simple approach peaked at about 237 MB for this 250,000-row table — the full DataFrame plus XlsxWriter's in-memory sheet — and grows linearly with the table. The streamed export peaked at about 39 MB, set mainly by the 50,000-row chunk, and stays there however long the table is. On a server running several jobs, or in a container with a memory limit, that difference decides whether the export finishes at all. Smaller chunks lower the peak further at a small cost in speed; 10,000 to 100,000 rows per chunk is a sensible range.

Step 5 — Check the export is complete

A large export should prove it contains every row. Count rows per sheet in the written file without loading it fully, and compare with the database:

from openpyxl import load_workbook

with engine.connect() as conn:
    expected = conn.execute(text("SELECT COUNT(*) FROM movements")).scalar()
wb = load_workbook("movements_split.xlsx", read_only=True)
per_sheet = {ws.title: sum(1 for _ in ws.iter_rows(min_row=2, values_only=True)) for ws in wb.worksheets}
wb.close()
print(per_sheet, "| total", sum(per_sheet.values()), "| database", expected)
wb = load_workbook("movements_split.xlsx", read_only=True)
first = next(wb.worksheets[0].iter_rows(min_row=2, max_row=2, values_only=True))
wb.close()
print("first row types:", [type(v).__name__ for v in first])

Reading with read_only=True streams the sheets, so even a very large export can be checked cheaply. The total across sheets matches the database count, and the first data row comes back with an integer, a datetime, strings and numbers — proof the cells were written typed. If rows are being added to the table while the export runs, the count can legitimately differ; export from a snapshot or with a fixed WHERE id <= :max_id to make the comparison exact. Counting rows quickly is covered in Count Rows in an Excel File Without Loading It.

Common pitfalls

• Chunking in pandas but not in the driver. Without stream_results=True, the driver may load everything first.
• to_excel per chunk. It cannot append to a sheet; write rows yourself with XlsxWriter.
• Formatting rows after writing more rows. In constant_memory mode, set headers and widths when the sheet is created.
• Text instead of dates and numbers. Use write_datetime and write_number with formats.
• Ignoring the row limit. Roll over to a new sheet before 1,048,576 rows — or question Excel as the format.

Conclusion

To export a table too big for memory, stream it: read with pd.read_sql(..., chunksize=...) over a connection with stream_results=True so the database uses a server-side cursor, write each row with typed XlsxWriter calls in constant_memory mode, and start a new sheet, with its own header, before Excel's 1,048,576-row limit. Memory then stays flat whatever the table size, and a final row count per sheet against the database proves the export is complete.

Frequently asked questions

How do I export a large SQL table to Excel without running out of memory? Read it in chunks with pandas read_sql and chunksize, using a server-side cursor, and write each row with XlsxWriter in constant_memory mode so rows are flushed to disk as they are written.

What is the maximum number of rows in an Excel sheet? 1,048,576 rows including the header. Larger exports must be split across several sheets or files.

Why does read_sql with chunksize still use a lot of memory? Many database drivers fetch the whole result into memory when the query runs. Use a connection with stream_results=True so the driver streams rows from a server-side cursor.

Can I use to_excel to write chunks to the same sheet? Not reliably; to_excel writes a whole DataFrame. Write rows yourself with XlsxWriter, keeping track of the next row number.

Related

• Up one level: Moving Data Between Excel and Databases — getting data in and out.
• Export SQL Query Results to Excel with Python — the simple case.
• Write a Million Rows to Excel with XlsxWriter constant_memory — the writing side in depth.
• Stream Excel Rows into a Database with openpyxl — the opposite direction.