Merge many Excel files into one

import pandas as pd
from pathlib import Path

frames = []
for f in Path("reports").glob("*.xlsx"):
    df = pd.read_excel(f)
    df["source_file"] = f.name
    frames.append(df)

pd.concat(frames, ignore_index=True).to_excel("merged.xlsx", index=False)
print("Merged", len(frames), "files")

When you need this

Monthly reports. Branch-wise sheets. Twelve files called sales_jan.xlsx to sales_dec.xlsx that someone now wants as one sheet. Doing it by hand with copy-paste takes an hour and it's easy to miss a file or paste a block twice. This script does it in a second, the same way every time.

How it works

Path("reports").glob("*.xlsx") finds every Excel file in the reports folder. Each one is read into a DataFrame, and a source_file column is added so you can always trace a row back to the file it came from. That column has saved me more than once when a number looked wrong. pd.concat stacks all of them, and the result is written to merged.xlsx.

Before you run it

• Install the packages: pip install pandas openpyxl.
• Put only the files you want merged in the folder. If merged.xlsx is saved in the same folder, it'll get merged into itself on the next run.
• Close the files in Excel first. Excel creates hidden ~$filename.xlsx lock files that the script will try (and fail) to read. You can skip them by adding if f.name.startswith("~$"): continue at the top of the loop.

When the files don't line up

If one file calls a column "Amount" and another calls it "Amt", you'll get two half-empty columns. Clean the headers as you read:

df.columns = df.columns.str.strip().str.lower()
df = df.rename(columns={"amt": "amount"})

For files with several sheets, pd.read_excel(f, sheet_name=None) returns all of them as a dictionary, and you can loop over that too.