Merging Hundreds of Workbooks Into One Table With Python

A folder holds three hundred monthly workbooks from as many branches, and the question is the total across all of them. Opening them one by one and copying into a master sheet takes a day and is wrong by the end. A script does it in a minute and can do it again next month. The code is short. What decides whether the result can be trusted is the handling of the files that are not like the others: renamed columns, an extra sheet, a lock file, a half-saved copy. This post builds the merge with the checks included. It is a spoke of automating Excel work without clicking.

The basic merge

Start with the simplest version, then harden it:

from pathlib import Path
import pandas as pd

frames = []
for path in sorted(Path("branches").glob("*.xlsx")):
    df = pd.read_excel(path, sheet_name="Data", dtype=str)
    df["source_file"] = path.name
    frames.append(df)

combined = pd.concat(frames, ignore_index=True)

Two habits are already in place. dtype=str reads every column as text, which avoids pandas guessing different types for different files (a column that is all numbers in one file and has a note in another). You convert types once, after combining. And the frames are collected in a list and concatenated once. Calling concat inside the loop copies the growing table every time, which is quadratic and slow.

source_file is worth adding every time. When a number looks wrong, you need to know which file it came from.

Which files to read

glob("*.xlsx") also matches files you do not want.

• Lock files. When a workbook is open in Excel, a hidden file named ~$report.xlsx sits beside it. It matches *.xlsx and is not a workbook.

• Temporary and backup copies. report (1).xlsx, report_old.xlsx, Copy of report.xlsx.

• Hidden system files on macOS such as ._report.xlsx.

• Subfolders. glob does not recurse, rglob does. Decide which you want.

• Other formats. .xls and .xlsm need their own handling (batch converting XLS files to XLSX).

A filter like not p.name.startswith(("~$", "._")) removes lock files. For temporary copies, a naming convention agreed with the senders is better than a growing list of exceptions: files must match branch_YYYY-MM.xlsx, and anything else is flagged. A regular expression on the name does the check.

Validate before accepting

The failure that hurts is the quiet one: a file whose columns differ slightly, which concatenation turns into extra columns and nulls without complaint. Check the header of each file against the expected list:

EXPECTED = ["Order", "Date", "Customer", "Amount"]
rejected = []

for path in files:
    try:
        df = pd.read_excel(path, sheet_name="Data", dtype=str)
    except Exception as e:
        rejected.append((path.name, f"unreadable: {e}"))
        continue
    cols = [str(c).strip() for c in df.columns]
    if cols != EXPECTED:
        rejected.append((path.name, f"columns {cols}"))
        continue
    df.columns = cols
    df["source_file"] = path.name
    frames.append(df)

Rejecting is better than guessing. Write the rejected list to a file and move the files to a rejected folder, so a person can fix or reply to the sender. If the same variant appears often, such as Amount versus Amount ($), add a documented rename map and apply it before the check. Keep the map short and visible.

Sheet names vary as well. Read by name when the name is stable. If it is not, find the sheet by content: loop through pd.ExcelFile(path).sheet_names and pick the one whose header matches. Fail clearly if none does. Position-based reading (sheet_name=0) breaks the day someone inserts a cover sheet.

Deal with title rows and layout differences

Real-world files often have a title block above the header. If the position varies, find the header row by looking for a known column name:

raw = pd.read_excel(path, sheet_name="Data", header=None, dtype=str)
header_row = raw.index[raw.apply(lambda r: "Order" in r.values, axis=1)][0]
df = raw.iloc[header_row + 1:].copy()
df.columns = raw.iloc[header_row].astype(str).str.strip()

This reads without assuming a header, locates the row that contains Order, and takes the header and data from there. It tolerates a title block of any height and fails with an IndexError if the marker is missing, which you should catch and turn into a rejection message (skipping header junk when reading Excel).

Count as you go

The check that makes the merge trustworthy is arithmetic:

counts = {f.name: len(f_df) for f, f_df in zip(accepted, frames)}
assert len(combined) == sum(counts.values())
print(len(accepted), "files,", len(rejected), "rejected,", len(combined), "rows")

Keep the per-file counts in a small table and write it next to the output. When a branch says its 1,200 rows are missing from the total, the table shows 1,200 or 980 and settles the question. For a stronger check, ask for or compute a control total in each file, such as a sum of amounts printed in a footer, and compare it with the merged data per file.

Duplicate files are a risk of their own. The same workbook saved twice under different names doubles its rows. After combining, check duplicates on the business key and on the combination of source_file-independent columns (finding duplicate rows in an Excel sheet with code).

Convert types after combining

With everything as text, convert once, in one place, and report failures:

combined["Amount"] = pd.to_numeric(combined["Amount"].str.replace(",", ""), errors="coerce")
combined["Date"] = pd.to_datetime(combined["Date"], format="%Y-%m-%d", errors="coerce")
bad = combined[combined["Amount"].isna() | combined["Date"].isna()]
bad.to_csv("bad_rows.csv", index=False)

The bad file lists the rows that did not convert with their source_file, which turns a vague data quality problem into a list of files to fix. Do not drop these rows silently.

Speed and memory

Three hundred small files merge in seconds. For a thousand large ones, reading is the cost: openpyxl parses XML slowly. Options, in order of effort:

1. Read only the needed columns with usecols.

2. Read in parallel with concurrent.futures.ProcessPoolExecutor, returning DataFrames or writing each cleaned file to Parquet or CSV.

3. Convert once to a faster format. Parse each workbook once to Parquet, then work from the Parquet files in later runs, and re-read only files that changed.

4. Use a faster reader such as python-calamine (reading Excel files faster in Python).

If the result is large, write it to a database or Parquet, not back into one workbook. A merged table of a few million rows is not an Excel file.

Keep it repeatable

Put the folder, the expected columns and the sheet name in a small config, not in the code. Log what was read and rejected. Write the combined table and the counts to a dated output folder. Make the script idempotent: running it twice on the same inputs gives the same output and does not append to the previous one. If files arrive over time, consider processing only new ones and tracking what has been loaded (watching a folder for new Excel files and importing them).

The same job in Power Query

Excel users can do a version of this without Python. Get Data, From File, From Folder, Combine and Transform builds a query that reads every file with a sample-file transformation. It handles new files automatically on refresh. It is weaker on validation and error reporting, and it is slower for hundreds of files (combining many files from a folder with Power Query). Use it when the audience is in Excel and the files are uniform; use the script when you need checks and logs.

When merging files is the wrong fix

The files are the same data saved many times. If the folder holds versions of one workbook, merging them multiplies it. Find the latest.

The structure differs by design. Files with different layouts are different datasets. Merge those that match and treat the rest separately.

The merge repeats every week. A process that needs three hundred workbooks collected by hand every week should have a database or a shared form behind it (when a spreadsheet has become a database).

The senders can send CSV. A plain file avoids most of the above.

See the pandas reference for concat and the Python documentation for pathlib. To convert a small result to JSON, use the CSV to JSON converter.