Samlify

Combine many Excel files into one

By the Samlify team · · 5 min read · Excel

Put the files in a folder of their own and use Power Query: Data, Get Data, From File, From Folder, then Combine. It works when every file has the same headers. If the files are messier, use the script below. It matches headers by name, skips copies and total rows, and writes a report of everything it did not take at face value. We ran it on sample data.

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

Microsoft asks you to "ensure that all the files you want to combine are contained in a dedicated folder without extraneous files". It also says each file "should have the same schema with consistent column headers, data types, and number of columns". The columns do not have to be in the same order, because "the matching is done by column names".

Step 2: try Power Query first

If your files meet Microsoft's requirements, Power Query is the simplest route. In Excel, choose Data, Get Data, From File, From Folder. Pick the folder, then choose a command at the bottom of the dialog: Combine and Load, or Combine and Transform Data. We did not run Power Query for this guide. The steps come from Microsoft's page, read on 18 November 2026.

Step 3: list the header spellings your files use

Open a few files and write down every spelling of the same column. In our sample files the order number is Order ID or OrderID, and the amount is Amount or Amount (USD). The script has a table, ALIASES, where you enter your own spellings.

Step 4: run the script

You need Python 3 and the openpyxl package (pip install openpyxl). Save the script as merge_workbooks.py. It reads only .xlsx and .xlsm files, and only values, not formulas.

"""Combine every Excel file in a folder into one sheet and report every oddity.

Usage: python3 merge_workbooks.py INPUT_FOLDER OUTPUT.xlsx
"""
import hashlib, re, sys
from datetime import date, datetime
from pathlib import Path
from openpyxl import Workbook, load_workbook
from openpyxl.cell import WriteOnlyCell

# Every header spelling you have seen, mapped to one column name. Edit to fit your files.
ALIASES = {
    "order_id": ["order id", "orderid", "order no", "order number", "order_id"],
    "date": ["date", "order date"],
    "customer": ["customer", "customer name"],
    "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"}
SEARCH_ROWS = 20                            # how far down to look for the header row
TOTAL_WORDS = {"total", "grand total", "subtotal", "sum"}
MAX_ROWS = 1_048_576

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

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

def to_number(v):
    """Accepts '2,450.00', '2450.00', '$1,200' or 1200. Anything else is flagged."""
    if v is None or isinstance(v, (int, float)):
        return v, False
    s = re.sub(r"\s+", "", str(v)).lstrip("$")
    if not re.fullmatch(r"-?(\d{1,3}(,\d{3})+|\d+)(\.\d+)?", s):
        return None, True                    # for example "n/a" or "2.450,00"
    return float(s.replace(",", "")), True

def find_header(rows):
    for i, row in enumerate(rows[: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, out):
    src, out = Path(src), Path(out)
    if not src.is_dir():
        sys.exit(f"Folder not found: {src}")
    tmp = out.with_suffix(".tmp.xlsx")
    files = sorted(p for p in src.iterdir() if p.suffix.lower() in (".xlsx", ".xlsm"))
    files = [p for p in files if p.resolve() not in {out.resolve(), tmp.resolve()}]
    if not files:
        sys.exit(f"No .xlsx or .xlsm files in {src}")
    report, out_rows, columns = [], [], list(ALIASES)
    seen_hash, seen_key = {}, {}
    for p in files:
        if p.name.startswith("~$"):
            report.append(f"SKIP {p.name}: 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 be opened ({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
            if "source_file" in names:
                report.append(f"SKIP {p.name}: an earlier output of this script")
                break
            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 k in ALIASES:
                if k not in names and k not in REQUIRED:
                    report.append(f"NOTE {p.name}/{ws.title}: no {k} column; left empty")
            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 row_no, 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
                if any(norm(v) in TOTAL_WORDS for v in row):
                    report.append(f"SKIP {p.name}/{ws.title} row {row_no}: 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 {row_no}: {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)
                    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 {row_no}: {KEY} {k} also in {seen_key[k]}")
                    else:
                        seen_key[k] = f"{p.name} row {row_no}"
                rec["source_file"], rec["source_sheet"], rec["source_row"] = p.name, ws.title, row_no
                out_rows.append(rec)
            break                                             # first sheet with a header row wins
        wb.close()
        if not found:
            report.append(f"ERROR {p.name}: no sheet with a header row we recognise")
    columns += ["source_file", "source_sheet", "source_row"]
    if len(out_rows) + 1 > MAX_ROWS:
        sys.exit(f"{len(out_rows)} rows do not fit on 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:
        cells = []
        for c in columns:
            cell = WriteOnlyCell(ws, value=rec.get(c))
            if isinstance(rec.get(c), (datetime, date)):
                cell.number_format = "yyyy-mm-dd"
            cells.append(cell)
        ws.append(cells)
    out.parent.mkdir(parents=True, exist_ok=True)
    try:
        wb.save(tmp)
        tmp.replace(out)                                      # no half-written output if it crashes
    except OSError as e:
        tmp.unlink(missing_ok=True)
        sys.exit(f"Cannot write {out} ({type(e).__name__}). Is it open in Excel? Close it and run again.")
    out.with_suffix(".report.txt").write_text("\n".join(report) + "\n", encoding="utf-8")
    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 folder and the name of the output file. Keep the output file outside the input folder. The script also skips its own earlier output if it ends up there.

python3 merge_workbooks.py sample/ out/combined.xlsx

Step 5: read the report

The sample files are made up. We generated 16 files with invented orders and customers: renamed and reordered headers, title rows, blank rows, total rows, amounts stored as text, data on a second sheet, one copy, one broken file, one stand-in lock file, and one amount written as 2.287,07. On Python 3.15.2 and openpyxl 3.1.5, the script printed this line:

16 files, 131 rows, amount 331,188.17, 18 report lines

The report file, combined.report.txt, held these 18 lines:

ERROR broken.xlsx: cannot be opened (BadZipFile)
NOTE sales-east-2026-05.xlsx/Sheet: no region column; left empty
CHECK sales-east-2026-05.xlsx/Sheet row 2: amount '2.287,07' is not a number
NOTE sales-north-2026-01.xlsx/Orders: no region column; left empty
NOTE sales-north-2026-02.xlsx/Orders: no region column; left empty
NOTE sales-north-2026-03.xlsx/Orders: no region column; left empty
NOTE sales-north-2026-04.xlsx/Orders: no region column; left empty
NOTE sales-south-2026-01 (1).xlsx/Orders: no region column; left empty
SKIP sales-south-2026-01.xlsx: identical to sales-south-2026-01 (1).xlsx
NOTE sales-south-2026-02.xlsx/Orders: no region column; left empty
NOTE sales-south-2026-03.xlsx/Orders: no region column; left empty
SKIP sales-south-2026-03.xlsx/Orders row 13: total row
NOTE sales-south-2026-04.xlsx/Orders: no region column; left empty
NOTE sales-west-2026-01.xlsx/Orders: no region column; left empty
NOTE sales-west-2026-02.xlsx/Data: no region column; left empty
NOTE sales-west-2026-03.xlsx/Orders: no region column; left empty
NOTE sales-west-2026-04.xlsx/Orders: no region column; left empty
SKIP ~$sales-north-2026-01.xlsx: lock file (the workbook is open somewhere)

Lines that start with CHECK and ERROR need you to look at the file. The amount 2.287,07 is flagged on purpose. With a dot used as a thousands separator, nobody can tell whether the digits are read correctly, so the script does not guess. That cell is empty in the result.

Step 6: check the total

The generator wrote the true row count and total when it made the files. They matched: 131 rows, and 331,188.17 for every amount the script could read. Do the same with your own files: add up one or two source files by hand and compare. In the result sheet, =COUNTA(A2:A1000) counts the filled order cells and =SUM(D2:D1000) adds the amounts. We did not run those two formulas in Excel. Rows with a flagged amount have no amount in the sum.

How the script handles tricky values

  • Leading zeros: order IDs such as 000131 are saved as text. In our output all 131 order IDs were text with six characters.
  • Accents: names such as Café Lumière were read and written without problems in our test.
  • Number formats: 2,450.00, 2450.00 and $1,200 are read as numbers. Anything else, including 2.450,00, is flagged. If your files use a decimal comma, change the pattern in to_number.
  • Total rows: a row with Total, Grand total, Subtotal or Sum in any cell is skipped and reported. A customer with exactly one of those names would be skipped too.
  • Output file open in Excel: the script stops and asks you to close it. We could not test that with a file really open in Excel.

When this is not enough

  • The script does not guess headers. A header missing from ALIASES becomes its own column and is reported as NOTE.
  • Two files that differ by one character count as different files. The same order ID in two files gives a CHECK line, but both rows are kept.
  • Excel allows "1,048,576 rows by 16,384 columns" on a worksheet. If you have more rows, the script stops and tells you.
  • Excel makes a hidden lock file such as ~$TEST.xlsx when a workbook is open, which Microsoft calls an "owner file". The script skips names that start with ~$. Ours was a stand-in we created, not a file Excel made.
  • If the export is daily or automatic, for example from a business system, a Power Query that refreshes, or an export straight to a database, usually fits better than a script run by hand.

How we made this guide

On 18 November 2026 we read the Microsoft pages listed below and created 16 sample workbooks with invented data and a known row count and total. We ran the script above (Python 3.15.2, openpyxl 3.1.5) and compared its output with those known figures. We did not run Power Query or the Excel formulas, so those statements come from Microsoft's pages only.

Sources