Combine many Excel files into one: a script tested on 30 files

Thirty monthly exports in a folder look like one table split into pieces, but they rarely line up. On 30 sample sales workbooks we generated ourselves, the common two-line pandas merge first crashed.

With errors skipped, it lost 235 orders, counted 115 twice and spread the data over 19 columns instead of 5. Its amount column added up to 2.08 million against a true 2.95 million.

The files were not broken. They differed in small ways we planted on purpose: a title row here, a renamed header there, a copy someone saved twice.

This guide lists those traps and what Excel’s Power Query does with them, according to Microsoft’s documentation. Then it gives you a tested Python script that merges the files and reports every row it changed or left out. Sources were read on October 29, 2026.

Files that look the same often are not

A merge only works if it can answer three questions for every file: where the header row is, which column means what, and which rows are data. People answer them at a glance. A merge tool has to be told, or it guesses.

Microsoft’s own instructions for combining a folder in Excel set the bar high. Its Import data from a folder page asks you to “ensure that all the files you want to combine are contained in a dedicated folder without extraneous files”, and says “each file should have the same schema with consistent column headers, data types, and number of columns”.

The good news is that most differences are predictable. If you know the traps, you can handle each one with a rule and make the tool report when a rule fired.

Our test: 30 workbooks with 11 kinds of trap

We wrote a Python script that generated 30 workbooks: three branches, ten months each, 40 to 120 orders per file, 2,303 orders in all. Every order, customer and amount in them is made up. Each order has an ID stored as text with leading zeros (000102), a date, a customer and an amount. The script also wrote an answer key: the true row count and total for each file.

Then we planted 11 kinds of problem. One file can carry more than one.

Trap	Files
A Region column added from July onward	12
Blank rows inside the data	8
A Total row at the bottom	7
Two title rows and a blank row above the header	6
Columns in a different order	5
Headers renamed (OrderID, Customer name, Amount (USD))	4
Amounts stored as text ("2,450.00")	4
Data on a second sheet named Data, after a Notes sheet	3
The Customer column missing	2
An exact copy of one file, named sales-south-2026-03 (1).xlsx	1
An Excel lock file, ~$sales-north-2026-05.xlsx	1

The lock file deserves a word. When you open a workbook such as TEST.xlsx, Excel creates a hidden lock file named ~$TEST.xlsx, which Microsoft calls an “owner file”, according to a Microsoft support answer on Microsoft Q&A. Ours was a stand-in with the same kind of name, not a file Excel made, since we had no Excel. A tool that reads every .xlsx in a shared folder can pick one up whenever a colleague has a workbook open.

We ran two methods on the folder on October 29, 2026, on Python 3.11.17 with pandas 3.1.0 and openpyxl 3.1.5, and scored both against the answer key. We could not run Power Query, so its section below comes from Microsoft’s documentation.

Method	Orders in the output (of 2,303)	Counted twice	Rows that are not orders	Columns	Amount total (true: 2,945,338.56)
pandas, first try	Crashed on the lock file
pandas, errors skipped	2,068	115	51	19	2,084,221.81 in the Amount column
Our script	2,303	0	0	8	2,945,338.56

Our script’s 8 columns are the 5 data columns plus three that say where each row came from.

The two-line pandas merge loses and doubles rows

A common answer for this task is some version of this:

import glob, pandas as pd
df = pd.concat([pd.read_excel(f, engine="openpyxl") for f in sorted(glob.glob("in/*.xlsx"))], ignore_index=True)

It reads the first sheet of every file and stacks the results. For the second run we wrapped each read in a try/except that skips a file that fails to open. By default, pandas.concat keeps every column it sees (join="outer") and fills the gaps with empty values. That also means every header spelling becomes a column of its own.

Here is what each trap did to it in our run:

• The lock file stopped the whole merge with BadZipFile: File is not a zip file, because a .xlsx file is a zip archive and the lock file is not.
• The Notes sheets were read instead of the data, so the 235 orders in those three files vanished without an error.
• The duplicate copy added its 115 orders a second time.
• Title rows made pandas take Sales report North 2026-02 as a header, so those files’ data landed in columns named Unnamed: 1 to Unnamed: 4.
• Renamed headers became their own columns, so the 300 orders in those four files sat under OrderID and Amount (USD), apart from the rest.
• Total rows were read as orders. The 7 totals added 739,817.04 to the Amount column on their own.
• Text amounts with a thousands comma stayed text, so they dropped out when we converted the column to numbers to sum it.

One more loss was quieter.

In most files, pandas turned the text ID 000102 into the number 102. Of the 1,619 IDs that landed in an ID column, 1,025 lost their leading zeros. The read_excel documentation gives the fix: pass a dtype, and use object “to preserve data as stored in Excel and not interpret dtype”. We confirmed dtype=str kept 000102 intact.

We covered the same trap for CSV files in our guide to dates and leading zeros.

We think the pandas merge is fine for files you produce yourself from one template. For a folder that people and systems have touched over months, it gives you a table that looks complete and is not.

Power Query matches by name but trusts one sample file

Excel’s built-in route is Power Query. Choose Data > Get Data > From File > From Folder and pick the folder. Then choose Combine > Combine & Load or Combine & Transform Data.

Microsoft’s page lists it for Excel for Microsoft 365, 2024, 2021, 2019 and 2016. We could not test it, so everything in this section comes from the documentation, and where we apply it to our files we say so.

Some traps it handles by design. “The columns do not have to be in the same order as the matching is done by column names,” the same page says. The Combine Files dialog has a Skip files with errors box, which should leave out a file like the lock file. The output carries a Source.Name column with each row’s file name, which Microsoft’s CSV walkthrough uses to check that every file came through.

The weak point is the sample file. When you combine, Power Query “analyzes the example file (by default, the first file in the list)”, according to the combine files overview. It builds a query called Transform Sample file from that one file, turns it into a function, and runs it on every file. Fixes such as Remove top rows therefore apply the same way to all files.

In our folder, six files had three extra rows above the header and 24 had none. One fixed number of rows to remove would be wrong for one group or the other. That is our reading of how the function works, not something we ran.

A column missing from the sample can vanish

Columns that the sample file lacks are the bigger risk. The final step expands each file’s table with Table.ExpandTableColumn, which takes a list of column names to expand. Chris Webb, who writes about Power Query and Power BI, explains why: “Table.ExpandTableColumn needs to know in advance the names of the columns you want to expand.”

In our folder, the first file by name is January’s, which has no Region column. If Power Query used it as the sample, we would expect the region values of the 996 orders in the 12 later files to be left out. We could not confirm that in Excel.

His fix is to replace that expand step with Table.Combine, which, per Microsoft’s reference, builds its result from “a union of the input types” when no column list is given. Microsoft’s example shows tables with different columns appended into one, with nulls filling the gaps. Renamed headers would still be separate columns there, as in pandas, because the matching is by name.

Power Query is the right choice when the people who refresh the report work in Excel and the files come from one system that rarely changes. It is a poor choice when headers drift or files arrive by hand, because its mistakes look like a finished table.

Step 1: put the files in a folder of their own

Copy the exports into a new folder that holds nothing else. Microsoft asks for the same thing for Power Query, and it matters just as much for a script. Close the files in Excel first, so no ~$ lock files are left behind.

Keep the merged output somewhere else. The script below skips its own output file if you put it in the input folder, but a separate folder is cleaner.

Step 2: list every header spelling your files use

Open three or four files from different months and write down every header. Then put them in the ALIASES block at the top of the script. Each key is the column name you want in the output; each list holds the spellings that mean it, in lowercase. Spaces at either end and letter case are ignored.

Decide which columns a file must have to count. In our script, a file without an order ID, a date or an amount is reported and left out, because guessing would be worse. A file without a customer or region keeps its rows, with those cells blank.

Step 3: run the script

You need Python 3 and openpyxl (pip install openpyxl). Save this as combine.py:

"""Combine every .xlsx workbook in a folder into one sheet and report every conflict.

Usage: python3 combine.py INPUT_FOLDER OUTPUT.xlsx
"""
import hashlib, re, sys
from pathlib import Path
from openpyxl import Workbook, load_workbook

# Every header spelling you have seen, mapped to one column name. Edit for your files.
ALIASES = {
    "order_id": ["order id", "orderid", "order no", "order number"],
    "date": ["date", "order date"],
    "customer": ["customer", "customer name", "client"],
    "amount": ["amount", "amount (usd)", "total amount", "value"],
    "region": ["region"],
}
REQUIRED = ["order_id", "date", "amount"]   # a file without these is reported, not guessed
KEY = "order_id"                            # used to spot the same order in two files
TEXT_COLUMNS = {"order_id"}                 # kept as text so leading zeros survive
NUMBER_COLUMNS = {"amount"}
HEADER_SEARCH_ROWS = 20                     # how far down a sheet to look for the header row
EXCEL_MAX_ROWS = 1_048_576

LOOKUP = {a: name for name, alist in ALIASES.items() for a in alist}

def norm(v):
    return re.sub(r"\s+", " ", str(v)).strip().lower() if v is not None else ""

def to_number(v):
    if v is None or isinstance(v, (int, float)):
        return v, False
    s = re.sub(r"[\s$€£]", "", str(v))
    if not re.fullmatch(r"-?(\d+|\d{1,3}(,\d{3})+)(\.\d+)?", s):
        return None, True                                     # e.g. "n/a" or "2.450,00": flagged
    return float(s.replace(",", "")), True

def find_header(rows):
    """Index and mapped names of the first row that names at least two known columns."""
    for i, row in enumerate(rows[:HEADER_SEARCH_ROWS]):
        names = [LOOKUP.get(norm(c), norm(c) or None) for c in row]
        if sum(1 for n in names if n in ALIASES) >= 2:
            return i, names
    return None, None

def main(src, dst):
    src, dst = Path(src), Path(dst)
    files = sorted(p for p in src.iterdir() if p.suffix.lower() in (".xlsx", ".xlsm"))
    skip = {dst.resolve(), dst.with_suffix(".tmp.xlsx").resolve()}
    files = [p for p in files if p.resolve() not in skip]   # never read our own output
    report, out_rows, columns = [], [], list(ALIASES)
    seen_hash, seen_key = {}, {}
    if not files:
        sys.exit(f"No .xlsx or .xlsm files in {src}")
    for p in files:
        if p.name.startswith("~$"):
            report.append(f"SKIP {p.name}: Excel lock file (the workbook is open somewhere)")
            continue
        h = hashlib.sha256(p.read_bytes()).hexdigest()
        if h in seen_hash:
            report.append(f"SKIP {p.name}: identical to {seen_hash[h]}")
            continue
        seen_hash[h] = p.name
        try:
            wb = load_workbook(p, read_only=True, data_only=True)
        except Exception as e:
            report.append(f"ERROR {p.name}: cannot open ({type(e).__name__})")
            continue
        found = False
        for ws in wb.worksheets:
            rows = [list(r) for r in ws.iter_rows(values_only=True)]
            hi, names = find_header(rows)
            if hi is None:
                continue
            found = True
            missing = [c for c in REQUIRED if c not in names]
            if missing:
                report.append(f"ERROR {p.name}/{ws.title}: no column for {', '.join(missing)}; file left out")
                break
            dupes = {n for n in names if n and names.count(n) > 1}
            if dupes:
                report.append(f"ERROR {p.name}/{ws.title}: two columns map to {', '.join(sorted(dupes))}; file left out")
                break
            for opt in ALIASES:
                if opt not in names and opt not in REQUIRED:
                    report.append(f"NOTE {p.name}/{ws.title}: no {opt} column; left blank")
            for n in names:
                if n and n not in columns:
                    columns.append(n)
                    report.append(f"NOTE {p.name}/{ws.title}: unknown column '{n}' kept")
            for r_i, row in enumerate(rows[hi + 1:], start=hi + 2):
                rec = {n: v for n, v in zip(names, row) if n}
                if all(v is None or str(v).strip() == "" for v in rec.values()):
                    continue                                  # blank row
                if any(norm(v) in ("total", "grand total", "subtotal") for v in row):
                    report.append(f"SKIP {p.name}/{ws.title} row {r_i}: total row")
                    continue
                for c in NUMBER_COLUMNS:
                    val, converted = to_number(rec.get(c))
                    if converted and val is None:
                        report.append(f"CHECK {p.name}/{ws.title} row {r_i}: {c} '{rec.get(c)}' is not a number")
                    rec[c] = val
                for c in TEXT_COLUMNS:
                    v = rec.get(c)
                    if isinstance(v, float) and v.is_integer():
                        v = int(v)                            # 102.0 becomes "102", not "102.0"
                    if v is not None:
                        rec[c] = str(v).strip()
                k = rec.get(KEY)
                if k:
                    if k in seen_key:
                        report.append(f"CHECK {p.name}/{ws.title} row {r_i}: {KEY} {k} also in {seen_key[k]}")
                    else:
                        seen_key[k] = f"{p.name} row {r_i}"
                rec["source_file"], rec["source_sheet"], rec["source_row"] = p.name, ws.title, r_i
                out_rows.append(rec)
            break                                             # first sheet with a header wins
        wb.close()
        if not found:
            report.append(f"ERROR {p.name}: no sheet with a recognizable header row")
    columns += ["source_file", "source_sheet", "source_row"]
    if len(out_rows) + 1 > EXCEL_MAX_ROWS:
        sys.exit(f"{len(out_rows)} rows will not fit in one Excel sheet; write CSV or a database instead")
    wb = Workbook(write_only=True)
    ws = wb.create_sheet("Combined")
    ws.append(columns)
    for rec in out_rows:
        ws.append([rec.get(c) for c in columns])
    tmp = dst.with_suffix(".tmp.xlsx")
    dst.parent.mkdir(parents=True, exist_ok=True)
    wb.save(tmp)
    tmp.replace(dst)                                          # no half-written output on a crash
    Path(dst.with_suffix(".report.txt")).write_text("\n".join(report) + "\n")
    total = sum(r["amount"] for r in out_rows if isinstance(r.get("amount"), (int, float)))
    print(f"{len(files)} files, {len(out_rows)} rows, amount {total:,.2f}, {len(report)} report lines")

if __name__ == "__main__":
    if len(sys.argv) != 3:
        sys.exit(__doc__)
    main(sys.argv[1], sys.argv[2])

Run it with the input folder and an output file:

python3 combine.py exports/ merged/combined.xlsx

On our folder it printed 32 files, 2303 rows, amount 2,945,338.56, 29 report lines in under a second. Both numbers matched the answer key, and so did the row count of every single file.

What the script does with each trap

First it skips lock files and exact byte-for-byte copies. It opens each workbook read-only and looks at each sheet in turn for a row that names at least two known columns, within the first 20 rows. That row becomes the header, which handles title rows and data on a second sheet.

Columns are matched by name through ALIASES, so order and spelling stop mattering. Blank rows are dropped, and total rows are dropped and logged.

Text amounts become numbers, and IDs stay text. Every output row carries its file, sheet and row number.

Step 4: read the report before you use the data

The script writes combined.report.txt next to the output. Each line starts with one of four words:

• ERROR: a file or sheet was left out. Fix the file or add an alias, then run again.
• CHECK: a row came through with a problem you should look at, such as an amount that is not a number or an ID already seen in another file.
• SKIP: something was left out on purpose: a lock file, a duplicate, a total row.
• NOTE: a column was missing and left blank, or an unknown column was kept.

On our folder, the report held 20 NOTE lines: 18 files without a region and two without a customer. It held 9 SKIP lines: 7 total rows, the lock file and the duplicate. Because the files are read in name order, the copy sales-south-2026-03 (1).xlsx sorted before the original and kept the rows; the original was the one logged as identical. The data is the same either way, but source_file names the copy.

We also ran it on a folder of broken files on purpose. An empty folder stops with No .xlsx or .xlsm files. Four bad files each produced an ERROR line and were left out, while the good files went through: one that is not a real workbook, one without a recognizable header, one with no amount column, and one with two columns that both map to amount.

An amount of n/a produced a CHECK line, and an output folder that did not exist yet was created. Running the script a second time with the output inside the input folder gave the same result, because it skips its own file.

Step 5: check the merged total against the sources

The report tells you what the script did. It cannot tell you whether a file was missing from the folder to begin with. So before you send the merged sheet anywhere, add up one number two ways. Take the merged amount column, and compare it with the totals in the source system or in each file’s own total row.

If they differ, source_file tells you which file to open.

If you compare this month’s merged sheet with last month’s, match the rows on the ID column, not by position. Our guide to comparing two sheets shows how and why.

Where the script stops being the right tool

More than about a million rows. An Excel sheet holds “1,048,576 rows by 16,384 columns”, according to Microsoft’s specifications and limits page. The script counts the rows first and stops with a message rather than writing more rows than one sheet can hold. We tested that stop by lowering the limit to 2,000 rows; it exited without writing anything. Past that size, write CSV or load the rows into a database instead.

Speed and memory. On 100 generated files of 5,000 rows each, the script took 101 seconds and peaked at 341 MB of memory on a 4-core cloud machine. openpyxl’s read-only mode is built for reading large files with “(near) constant memory consumption”, but this script still holds each sheet and every output row in memory before writing. For a monthly folder that is plenty; for a daily job over years of files, a database is the better home.

Values it flags instead of guessing

Numbers in other formats. The amount rule accepts 2,450.00 and $1,200 and flags anything else. A European 2.450,00 is flagged as CHECK, not misread as 2.45. If your files use a comma for decimals, change the pattern in to_number.

Dates stored as text. The script keeps real Excel dates as dates and leaves text dates as text. It does not guess whether 03/04/2026 means March or April.

IDs that are already numbers. If a system wrote 102 as a number, the leading zeros were lost before the file reached you. The script stores it as the text 102 and cannot bring the zeros back.

Total rows with other words. The script drops any row with a cell that reads exactly total, grand total or subtotal, in any letter case, and logs each one. A sum row labeled Sum would get through as data. Add your label to that line, and read the SKIP lines to make sure no real customer named “Total” was dropped.

When Power Query is the better choice

Non-programmers who refresh the report. If the merge has to run every month in someone else’s Excel, Power Query fits better, as long as the files come from one system. Keep the script for the messy folders, or run it once to find the traps and then fix the sources. For more on picking small scripts over bigger tools, see our guide to tiny automations in code and the rest of our automation guides.

Thirty files, one script and one report: the fastest check on any merged sheet is still the total. If the merged amount does not match what the source files say, do not send it until the report tells you why.

How we made this

On October 29, 2026, we read the Microsoft, pandas and openpyxl pages and Chris Webb’s post listed below, and generated 30 sample workbooks with 2,303 made-up orders, 11 kinds of planted trap, a stand-in lock file and an answer key. We ran a two-line pandas merge (pandas 3.1.0) and the script in this guide (openpyxl 3.1.5, Python 3.11.17) on them and scored both against the key. We also ran the script on deliberately broken files and timed it on 100 generated files of 5,000 rows. We had no Excel, so every Power Query statement comes from the documentation, untested, and what we expect it to do with our files is inference.

Sources

• Microsoft Support, Import data from a folder with multiple files (Power Query), read October 29, 2026
• Microsoft Learn, Combine files overview, read October 29, 2026
• Microsoft Learn, Combine CSV files in Power Query, read October 29, 2026
• Microsoft Learn, Table.ExpandTableColumn, read October 29, 2026
• Microsoft Learn, Table.Combine, read October 29, 2026
• Microsoft Support, Excel specifications and limits, read October 29, 2026
• pandas 3.1.0 documentation, pandas.concat and pandas.read_excel, read October 29, 2026
• Microsoft Q&A, Excel open on another device, but isn’t, answer by Microsoft support staff, August 8, 2023, read October 29, 2026
• openpyxl documentation, Optimised modes, read October 29, 2026
• Chris Webb, Making sure all columns appear when you combine data from multiple files in Power BI/Power Query M, December 3, 2017, read October 29, 2026
• 28creation test, October 29, 2026: 30 generated workbooks with 2,303 made-up orders and 11 planted traps, scored against an answer key; 100 generated workbooks of 5,000 rows for timing