🏎️ Large files & performance

📘 Start with the full guides

The long-form treatment of large files & performance, with the reasoning the shorter pages assume.

• GuideWorking with Large Excel Files in PythonRead, transform and write spreadsheets that no longer fit comfortably in memory — read-only and write-only openpyxl modes, chunked pandas reads, column pruning, and when to leave .xlsx behind.
• GuideReading Excel with Polars and ArrowRead and write Excel with Polars and Arrow-backed engines: polars.read_excel, the calamine reader, lazy pipelines, Parquet conversion, and when pandas is still the better tool.

🛠️ Step-by-step how-tos

One task each, with code you can copy, run and adapt.

• How-toSpeed Up pandas Excel Reads with the calamine EngineSwap openpyxl for calamine in pandas.read_excel: install it, measure the difference, understand the trade-offs, and know which reads it will not help.
• How-toAggregate a Huge Excel File Without Loading ItSum, count, average and find the minimum and maximum per group in an Excel sheet with hundreds of thousands of rows while holding only one row at a time: a streaming group-by over openpyxl read-only or python-calamine rows, statistics that can and cannot be computed in one pass, an exact median with bounded memory where values are discrete, and a check against pandas on a sample.
• How-toCount Rows in an Excel File Without Loading ItFind out how many rows a large workbook has before deciding how to process it — in milliseconds, without reading it into pandas. Read the size the file declares with openpyxl, spot when that size is missing or inflated by formatted empty rows, and count real rows with calamine or by streaming the sheet's XML.
• How-toFind Out Why an Excel File Is Slow to OpenDiagnose a sluggish workbook from Python without opening it in Excel: which parts of the xlsx package take the space, sheets whose used range stretches far beyond the data, thousands of cell styles, piles of conditional-formatting rules and data validations, volatile functions such as INDIRECT and OFFSET, external links, and a one-page diagnosis sheet that ranks what to fix first.
• How-toProcess Multiple Excel Files in Parallel with PythonUse every core on a folder of workbooks with ProcessPoolExecutor — why threads do not help, how to return small results, error isolation per file, and when parallelism makes things worse.
• How-toRead a Large Excel File in Chunks with pandaspandas has no chunksize for Excel — build one with an openpyxl row generator, aggregate chunk by chunk, and keep peak memory flat on files with hundreds of thousands of rows.
• How-toReduce Memory When Reading Excel with pandasTwo different numbers decide whether a big Excel file fits in memory — the peak while the engine parses the workbook and the size of the DataFrame you keep. Measure both, then cut them with usecols, categorical and smaller numeric types, the right engine and, when even that is too much, streaming rows with openpyxl.
• How-toReduce the File Size of an Excel Workbook with PythonFind out what makes a workbook big by measuring its parts, then shrink it where it counts: downscale oversized images, delete formatted-but-empty rows that inflate the used range, remove archive sheets, and know which popular fixes barely help. Includes measured before-and-after sizes, an openpyxl bug that breaks saving twice, and habits that keep generated reports small.
• How-toRemove Duplicate Rows Across Many Large Excel FilesDe-duplicate records spread over dozens of overlapping Excel exports without loading them all at once: stream rows file by file, fingerprint each record on normalised key fields, keep only a set of hashes in memory, write unique rows straight to an output workbook, log every duplicate with its source file and row, and choose between keeping the first or the latest copy.
• How-toSpeed Up openpyxl with read_only ModeHow openpyxl's read_only mode streams a worksheet instead of building it, what values_only=True saves, the limits you accept in exchange, and the file handle you must close.
• How-toSplit a Large Excel File into Files of N RowsBreak a big workbook into smaller ones for an upload limit, an import tool or an email attachment: the simple pandas version, a streaming version with openpyxl read-only and write-only modes that keeps memory flat, the header repeated in every part, dates kept as dates, numbered file names, and a check that no row was lost or duplicated.
• How-toStream Excel Rows into a Database with openpyxlLoad a large Excel sheet into a database without ever holding it in memory — read rows with openpyxl's read-only mode, convert and validate each row, insert in batches inside a transaction, divert bad rows to a rejects table and make the load safe to rerun.
• How-toTake a Random Sample of Rows from a Large Excel FilePull a fair random sample from an Excel sheet too big to load comfortably: reservoir sampling in one streaming pass with a fixed seed, a stratified sample that keeps every region represented, the pandas skiprows trick when you know the row count, why "the first 10,000 rows" is not a sample, and checks that the sample's figures match the full file.
• How-toWrite Large DataFrames to Excel with write_only ModeGenerate hundred-thousand-row workbooks without the memory — openpyxl write_only, xlsxwriter constant_memory, streaming a DataFrame row by row, and the formatting you must plan ahead.
• How-toProfile a Slow Excel Report Script in PythonFind out where a slow report actually spends its time before changing anything. Time each stage with a small context manager, drill into the slowest one with cProfile and pstats, read the output correctly, and fix the usual culprits — per-cell styling in openpyxl, slow reads, row-by-row writes — measuring the gain each time.
• How-toWrite a Million Rows to Excel with xlsxwriterConstant memory mode explained: what it costs, why rows must be written in order, where the temporary files go, how to split past Excel's 1,048,576-row limit, and when a CSV is the honest answer.
• How-toBenchmark Python Excel Read and Write SpeedPublished speed ratios are hypotheses about your file. Build a small harness that times openpyxl, calamine, Polars and xlsxwriter on the workbook you actually run.
• How-toExport a Large Database Table to Excel in ChunksExport a database table too big to load at once into Excel with flat memory use: stream rows in chunks from SQLAlchemy with a server-side cursor, write them straight to disk with XlsxWriter constant_memory mode, roll over to a new sheet before the 1,048,576-row limit, keep dates and numbers typed, and check the row counts and memory afterwards.
• How-toQuery Excel Files with SQL Using DuckDBRun SQL directly against .xlsx files with DuckDB's excel extension — read a range below a report header, stop at the first empty row, handle text in numeric columns, combine several workbooks, join Excel with pandas DataFrames and write query results back to a workbook.
• How-toScan and Filter Many Excel Files Lazily with PolarsPolars has scan_csv and scan_parquet but no scan_excel, so a folder of workbooks cannot be queried lazily as-is. This page reads only the columns you need from each file with the calamine engine, tags rows with their source file, builds one LazyFrame for filtering and aggregation, and adds a Parquet cache that turns every later run into a true lazy scan with projection and predicate pushdown.
• How-toConvert Excel to CSV with PythonTurn .xlsx sheets into CSV without mangling dates, leading zeros or European decimals — one sheet, every sheet, or a streaming conversion for files too large to load.
• How-toZip Large Excel Attachments Before Emailing ThemAn .xlsx is already a zip file, so zipping it saves only a few percent — measured here at 4% on an 8.6 MB workbook. Find out what actually shrinks a report enough to email: size budgets that include base64 overhead, repacking at maximum compression, CSV inside a zip for raw data, and splitting attachments across messages.
• How-toCache Generated Excel Files in a Web AppStop a web app rebuilding the same Excel export on every click: key a file cache on the request parameters plus a data version, write cached workbooks atomically, answer repeat downloads with an ETag and 304 Not Modified, keep two users from building the same file at once, and evict old files by age and total size — tested end to end with Flask's test client.
• How-toGenerate Large Excel Exports in a Background JobStop big "Export to Excel" requests from timing out: accept the request and return a job ID at once, build the workbook in a background worker that streams rows from the database into xlsxwriter's constant-memory mode, report progress through a status endpoint, serve the finished file by link, and expire old files.
• How-toSpeed Up xlwings with Bulk Reads and WritesMake xlwings scripts fast by moving whole ranges in single calls instead of looping over cells — expand and options for DataFrames, 2D lists, turning off screen updating, events and automatic calculation, and chunksize for very large ranges.
• How-toList Sheet Names and Column Headers Without Loading the DataFind out what a workbook contains before reading it: sheet names with pandas and openpyxl, the header row of every sheet with nrows=0, hidden sheets and chartsheets, the used range of each sheet, and a catalogue of a whole folder of files — in milliseconds, not seconds.
• How-toSearch Every Sheet and Workbook in a Folder for a ValueFind which workbook, sheet and cell contain an invoice number, a customer name or a pattern across a whole folder of Excel files. Match exactly, by substring or by regular expression, treat 1042 and "1042" as the same, skip lock files and broken files, and search thousands of workbooks quickly in read-only mode.

🔧 When it breaks

The errors this topic produces, with the cause explained rather than just the fix.

• FixFix "Filesize would require ZIP64 extensions" (LargeZipFile) in XlsxWriterVery large exports fail at close() with xlsxwriter.exceptions.FileSizeError: Filesize would require ZIP64 extensions, raised from zipfile.LargeZipFile. Learn what crosses the limit (one XML part over 2 GiB, not the .xlsx file size), estimate when your export will hit it, turn on use_zip64 in XlsxWriter or through pandas, and when to split the data or choose another format instead.
• FixFix openpyxl max_row Counting Empty Rowsws.max_row reports 2,000 on a sheet with six rows of data, and ws.append() writes far below the table. Learn the three things that inflate max_row — formatted cells, cells created by reading, and stale dimension tags — and how to find and fix the real last row.
• FixFix ReadOnlyWorksheet Has No Attribute Errors in openpyxlSwitching to load_workbook(read_only=True) for speed breaks code that used iter_cols, merged_cells, column_dimensions, comments, hyperlinks or writes — and EmptyCell objects have no coordinate. See exactly what read-only mode supports, rewrite column access and gap handling for streaming, get the missing metadata from a second normal load, and know when read-only is the wrong mode.
• FixFix "This sheet is too large" When Writing Excel with pandaspandas raises "This sheet is too large! Your sheet size is … Max sheet size is 1048576, 16384" when a DataFrame exceeds Excel's grid. Split rows across sheets, transpose or split wide frames, summarise, or choose another format — and avoid the off-by-one that silently drops a row.

🧭 Follow it through a track

Prefer an ordered route? These tracks cover large files & performance along the way, in the order that builds on itself.

• 🧹Data transformation20 of this topic's pages
• 🌱Getting started8 of this topic's pages
• 🚀Reporting workflows4 of this topic's pages
• 🎨Formatting & charting1 of this topic's pages

🔗 Related topics

Subjects that keep turning up on the same pages as this one, most overlap first.