[Python] How can I speed up merging very large multi-sheet Excel files (6–7 sheets) into...

I have a Django background task that needs to process very large Excel workbooks (hundreds of MB, sometimes over 1 GB). Each workbook contains 6–7 sheets:

• Main sheet – acts as the parent table and has a unique _id column.
• Child sheets – each has a _submission__id column that references the parent _id in the main sheet.

The workflow requirements:

1. Iterate through the main sheet row-by-row (or in reasonably sized chunks to avoid high memory usage).
2. For each chunk, filter every child sheet to match rows whose _submission__id values exist in that chunk’s set of parent _ids.
3. Combine the results into a nested JSON structure (parent + all matching child rows).
4. Insert the nested JSON into MongoDB and create/update related Django GIS models.

What I’ve tried

• 
  openpyxl + Pandas
  
  • Used pd.read_excel with chunking (skiprows + nrows) on the main sheet.
  • Filtered the child sheets with isin() joins on each chunk’s _ids.
  • Works functionally, but is extremely slow and memory-hungry on large files.
• 
  Research/alternatives considered
  
  • Polars – Much faster for joins and filtering, but pl.read_excel doesn’t seem to support true chunked/lazy streaming the way pl.scan_csv does.
  • DuckDB – Could load all sheets as tables and perform SQL joins in memory or on-disk; promising but I’m unsure about the best pattern for incremental processing when a workbook is >1 GB.
  • Dask / Modin – Parallelize Pandas operations, but may still require the full sheet in memory.
  • Pre-converting Excel to Parquet/CSV to allow streaming/lazy reads.

What I expected


A solution that allows me to:

• Handle hundreds of thousands or millions of rows per sheet without exhausting memory.
• Join child sheets to their parent _ids efficiently.
• Build the nested JSON structure and write to MongoDB significantly faster than the current Pandas + openpyxl approach.

Continue reading...