Guide

Advanced Data Transformation And CleaningDeep dive

Process Multiple Excel Files in Parallel with Python

Q: Why processes rather than threads?

Parsing xlsx is CPU-bound Python work, so it holds the global interpreter lock. Threads take turns on one core; processes genuinely run at the same time.

Use 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.

A folder of thirty monthly workbooks is the ideal shape for parallelism: the files are independent, each one is CPU-bound XML parsing, and the results are small. Done right this is close to a linear speed-up; done wrong it exhausts memory or hides a corrupt file. This guide, part of Working with Large Excel Files in Python, covers the pattern and its limits.

Prerequisites

Bash

pip install pandas openpyxl

Why processes, not threads

Parsing an .xlsx is Python bytecode doing XML work, so it holds the global interpreter lock almost continuously. Threads therefore interleave rather than overlap:

Python

import time
from concurrent.futures import ProcessPoolExecutor, ThreadPoolExecutor
from pathlib import Path

import pandas as pd

def row_count(path):
    return len(pd.read_excel(path, sheet_name=0, usecols=[0]))

files = sorted(str(p) for p in Path("monthly").glob("*.xlsx"))

for Pool, label in ((ThreadPoolExecutor, "threads"), (ProcessPoolExecutor, "processes")):
    start = time.perf_counter()
    with Pool(max_workers=4) as pool:
        totals = list(pool.map(row_count, files))
    print(f"{label:10} {time.perf_counter() - start:5.1f}s  {sum(totals):,} rows")

On a machine with four or more cores the process version is typically two to three times faster; the thread version is often no faster than a plain loop. Threads still earn their place when the bottleneck is the network — downloading thirty workbooks from object storage is I/O-bound and threads handle it well — so the rule is threads for fetching, processes for parsing.

A worker that returns something small

The unit of work is one file, and what it returns crosses a process boundary — so return an aggregate, not the data:

Python

from pathlib import Path

import pandas as pd

def summarise(path):
    """Runs in a worker process. Returns a small, picklable dict."""
    try:
        df = pd.read_excel(path, sheet_name="Orders",
                           usecols=["Region", "Quantity", "Unit_Price"])
        df["Revenue"] = df["Quantity"] * df["Unit_Price"]
        by_region = df.groupby("Region", observed=True)["Revenue"].sum().round(2)
        return {
            "file": Path(path).name,
            "rows": int(len(df)),
            "revenue": float(df["Revenue"].sum()),
            "by_region": by_region.to_dict(),
            "error": None,
        }
    except Exception as exc:                      # noqa: BLE001 - report, do not crash the batch
        return {"file": Path(path).name, "rows": 0, "revenue": 0.0,
                "by_region": {}, "error": f"{type(exc).__name__}: {exc}"}

Catching the exception inside the worker is the part that makes a batch job survivable. One corrupt workbook then becomes a row in the report rather than a traceback that takes the other twenty-nine results with it.

The return value must be picklable, which rules out open file handles, workbook objects and generators — another reason to reduce to numbers and dicts before returning.

Run the pool and combine

Python

from concurrent.futures import ProcessPoolExecutor, as_completed
from pathlib import Path

import pandas as pd

def run(folder="monthly", workers=4):
    files = sorted(str(p) for p in Path(folder).glob("*.xlsx") if not p.name.startswith("~$"))
    results, failures = [], []

    with ProcessPoolExecutor(max_workers=workers) as pool:
        futures = {pool.submit(summarise, f): f for f in files}
        for future in as_completed(futures):
            outcome = future.result()
            (failures if outcome["error"] else results).append(outcome)
            print(f"{outcome['file']:24} {'FAILED' if outcome['error'] else outcome['rows']}")

    combined = {}
    for outcome in results:
        for region, revenue in outcome["by_region"].items():
            combined[region] = combined.get(region, 0.0) + revenue

    summary = pd.DataFrame(sorted(combined.items()), columns=["Region", "Revenue"])
    return summary, failures

if __name__ == "__main__":               # required on Windows and macOS spawn
    summary, failures = run()
    print(summary)
    for failure in failures:
        print("failed:", failure["file"], failure["error"])

Two details are easy to skip and painful to debug. The if __name__ == "__main__" guard is mandatory wherever processes are spawned rather than forked — without it each worker re-imports the module and starts its own pool. And filtering ~$ files matters on any shared drive: those are Excel's lock files, they are not valid workbooks, and they appear whenever a colleague has the folder open.

Choosing the worker count

The illustrated shape is what you should expect rather than a benchmark of your machine: gains flatten at the physical core count, and once total memory exceeds what is available the wall time gets worse, not just flat. Because each worker holds an entire workbook, memory is nearly always the binding constraint with Excel work — four workers on 500 MB files needs two gigabytes before any of your own data structures exist.

When not to parallelise

Situation	Better approach
A handful of small files	A plain loop — pool startup costs more than it saves
One very large file	Stream it; parallelism cannot split a single sheet easily
Downloading the files	Threads, then process the local copies
Workers returning big frames	Aggregate in the worker, or write per-file output to disk
A memory-capped container	Fewer workers, or sequential streaming
Debugging	Run sequentially first; tracebacks cross process boundaries badly

The last row is a practical habit rather than a rule. Keep the worker function callable on its own and test it on one file directly — summarise("monthly/jan.xlsx") — before it goes anywhere near a pool.

Writing results per file instead of returning them

When each file produces substantial output rather than a summary, have the worker write its own file and return only the path. Nothing large crosses the process boundary:

Python

from pathlib import Path

import pandas as pd

def clean_to_parquet(path, outdir="clean"):
    Path(outdir).mkdir(exist_ok=True)
    target = Path(outdir) / (Path(path).stem + ".parquet")
    df = pd.read_excel(path, sheet_name="Orders")
    df["Revenue"] = df["Quantity"] * df["Unit_Price"]
    df.to_parquet(target, index=False)
    return {"file": Path(path).name, "rows": len(df), "output": str(target)}

The parent then reads the Parquet files afterwards — cheaply, and possibly in parallel too. This "fan out, write, fan in" shape is what most batch Excel jobs should look like once they outgrow a single loop.

Threads for fetching, processes for parsing

Batch jobs over a shared drive or object storage have two phases with opposite bottlenecks, and the fastest arrangement uses both pools:

Writing the downloads to a local folder between the phases is worth the disk it uses. A batch that fails halfway can be restarted against the files already fetched, and the parse phase becomes reproducible without touching the network at all.

Common pitfalls and gotchas

Symptom	Cause	Fix
No speed-up at all	Used threads for CPU-bound parsing	Use `ProcessPoolExecutor`
Workers spawn recursively	Missing `if __name__ == "__main__"`	Add the guard around the pool
`MemoryError` with several workers	Each worker holds a full workbook	Fewer workers, or stream inside the worker
Whole batch dies on one file	Exception raised out of a worker	Catch inside the worker and return the error
`PicklingError`	Returned an unpicklable object	Return dicts, lists and numbers
Weird files in the batch	Excel lock files (`~$name.xlsx`)	Filter names starting with `~$`
Results in the wrong order	`as_completed` yields by completion	Sort afterwards, or use `pool.map`

Keep a progress signal

A batch that prints nothing for four minutes is indistinguishable from a batch that has hung. as_completed gives you a natural place to report progress, and the count is free:

Python

from concurrent.futures import ProcessPoolExecutor, as_completed

def run_with_progress(files, workers=4):
    done = 0
    results = []
    with ProcessPoolExecutor(max_workers=workers) as pool:
        futures = [pool.submit(summarise, f) for f in files]
        for future in as_completed(futures):
            outcome = future.result()
            results.append(outcome)
            done += 1
            status = "error" if outcome["error"] else f"{outcome['rows']:,} rows"
            print(f"[{done}/{len(files)}] {outcome['file']:24} {status}", flush=True)
    return results

flush=True matters when the job runs under a scheduler that buffers output — without it the whole log arrives at the end, which defeats the point. The same counter is what you would emit to a metrics system or write into the job's log file for the error-handling layer to alert on.

Performance and scale notes

Expect a speed-up close to the physical core count for parsing-heavy work, and nothing beyond it — hyper-threads add little because the work is memory-bandwidth bound as much as it is arithmetic. Pool startup is tens of milliseconds per worker, so batches under a second of total work are better off sequential. If the same folder is processed repeatedly, the biggest win is not more cores but caching: convert each workbook to Parquet once, then let subsequent runs read that, which is usually faster than any parallel Excel parse.

Conclusion

A folder of workbooks parallelises almost perfectly, provided each worker parses one file, catches its own exceptions, and returns something small. Use processes rather than threads for parsing, guard the entry point, filter Excel's lock files, and set the worker count from your memory budget rather than your core count. When per-file output is large, write it from the worker and return the path.

Frequently asked questions

Why processes rather than threads? Parsing .xlsx is CPU-bound Python work, so it holds the global interpreter lock. Threads take turns on one core; processes genuinely run at the same time.

How many workers should I use? Start at the number of physical cores, then reduce until peak memory fits — each worker holds its own workbook, so memory is the usual constraint rather than CPU.

Can workers return DataFrames? They can, but every result is pickled and copied back to the parent. Return aggregates where possible and only return frames when they are small.

What happens if one file is corrupt? Catch the exception inside the worker and return it as data. An uncaught exception surfaces when you read that future's result, and it is easy to lose the rest of the batch to it.

Up to the parent guide:

Working with Large Excel Files in Python — the other levers to try before adding cores.

Related guides:

Combine Multiple Excel Files into One with Python — the sequential version of the same job.
Convert Excel to CSV with Python — the per-file output format that makes fan-in cheap.
Error Handling and Logging in Excel Automation — reporting the failures a batch collects.
Schedule Recurring Excel Reports with APScheduler — running the batch unattended.