Branch reports, monthly extracts, one file per salesperson — sooner or later someone asks you to “put them all together”. Copy-pasting 100 files takes an afternoon and invites mistakes. This script does it in seconds.

The script

from pathlib import Path
import pandas as pd

folder = Path(r"C:\Reports\Monthly")
frames = []
for f in sorted(folder.glob("*.xlsx")):
    if f.name.startswith("~$"):          # skip Excel lock files
        continue
    df = pd.read_excel(f)
    df["source_file"] = f.name           # keep track of where each row came from
    frames.append(df)

combined = pd.concat(frames, ignore_index=True)
combined.to_excel(folder / "_combined.xlsx", index=False)
print(f"{len(frames)} files, {len(combined):,} rows")

What each part does

• glob("*.xlsx") finds every workbook in the folder. Use rglob to include sub-folders.
• Files starting with ~$ are temporary lock files Excel creates while a workbook is open — reading them crashes the script.
• source_file is the column you will thank yourself for when a number looks wrong.
• concat stacks all the tables. Columns are matched by name, not position.

Check the headers before trusting the result

If one file says “Amount” and another “Amt”, pandas creates two columns. Add this after the loop to spot it:

for f, df in zip(sorted(folder.glob("*.xlsx")), frames):
    missing = set(frames[0].columns) - set(df.columns)
    if missing:
        print(f"{f.name} is missing: {missing}")

Add a quick summary sheet

summary = combined.groupby("source_file").size().rename("rows")
with pd.ExcelWriter(folder / "_combined.xlsx") as xl:
    combined.to_excel(xl, sheet_name="All data", index=False)
    summary.to_excel(xl, sheet_name="Rows per file")

The “Rows per file” sheet makes it obvious if one branch sent an empty or duplicated file.

New to pandas? Start with Python for Excel users.

Try it yourself: step by step

1. Download branch-files.zip and unzip it to C:\Reports so you have C:\Reports\Monthly with four files.
2. Download merge_files.py; if you used another folder, edit the folder = Path(...) line.
3. Run python merge_files.py. It prints “4 files, 60 rows” and creates _combined.xlsx.
4. Open it: All data has a source_file column; Rows per file shows how many rows came from each branch.
5. Rename one header in a branch file (e.g. Qty → Quantity), run again and see the extra column appear — then add the header check from the article.