Merging twelve monthly sales files into one clean sheet

Twelve files named Sales_Apr.xlsx through Sales_Mar.xlsx, about 4,000 rows each, and you need one sheet to pivot. The job is straightforward and the usual approach, opening all twelve and copy-pasting, is where it goes wrong. There are two clean routes: do it once in the browser with a merge tool, or write about twenty lines of pandas if this is going to be a monthly chore. Both are below, along with the checks that catch the mistakes neither route prevents.

What goes wrong with copy-paste

The failures are boring and they repeat:

• Header rows buried in the data. You paste eleven blocks under the first file's header and forget to strip the header row from each. Now Invoice No appears as a text value in a numeric column, your row count is eleven too high, and a SUM quietly ignores it.
• Column order drifts. Someone inserted a Discount column in August. From August onward every paste is shifted by one, so Amount values land in the Tax column. Nothing errors. The totals are just wrong.
• You cannot tell which file a row came from. When a figure looks odd in December there is no way back to the source without re-opening files.
• Silent truncation. 48,000 rows pasted in twelve operations, and if one paste landed on the wrong row you have overwritten a few hundred without a warning.

The fix for all four is the same principle: match columns by their heading text, never by position, and keep a column that records the origin of every row.

Headers that are nearly the same

Before merging, list the headings from all twelve files and look at them together. This is what a real set looks like:

A heading that differs by one character is a different column to every merge tool ever written, so you either fix the headings in the source files first, or map them in code. A trailing space is the one you will not see by looking; it is worth checking every heading with repr() or LEN() once.

Duplicates, and which key decides

Duplicate rows in a set of monthly exports usually come from one of three places: a re-export whose date range overlapped the previous month, the same file saved twice under different names, or an invoice genuinely entered twice in the billing software. The first two are your problem to remove; the third belongs to accounts, and you should report it rather than delete it.

Do not dedupe on the whole row. A real duplicate differs in some irrelevant field, an export timestamp or a trailing space, and a full-row comparison will keep both copies. Dedupe on the business key, which for sales rows is usually the invoice number plus the line item, or the invoice number plus the branch if each branch runs its own numbering series.

The browser route, for a one-off

Merge Excel Files does the stacking properly. Drop all twelve files in, drag them into month order, and choose Stack rows into one sheet. Columns are matched by heading text rather than position, so a file that lists them in a different order still lines up, and a heading that exists in only one file is added at the end instead of shifting anything. Keep Add Source file column ticked; that gives you the origin column you need. You can mix XLSX and CSV in the same batch.

Then run the result through Clean & Deduplicate Data. Tick trim spaces, remove empty rows and remove duplicate rows, and in Duplicate check columns select the columns that identify a record rather than leaving it to compare whole rows. Matching ignores letter case, so RAMESH TRADERS and Ramesh Traders count as the same value, and the first occurrence in the current sheet order is the one kept. That last detail matters: sort by date before cleaning if you want the newest version of a repeated invoice to survive.

The merge tool does not rename near-miss headings for you. If August called it Net Amount, you will get two amount columns and you have to fold them together yourself. Fixing the headings in the source files before you merge is genuinely faster than fixing the merged sheet.

Both tools read the files inside your browser, so nothing is uploaded. The practical ceiling is device memory, since every file is opened before merging; twelve files of a few thousand rows is nothing, a dozen files of half a million rows each will not work on a phone.

The same job in pandas

If this happens every month, write it once. Put the monthly files in a folder, name them so the branch and month are in the filename, and run this. It needs pip install pandas openpyxl.

from pathlib import Path
import pandas as pd

RAW = Path("monthly")           # Nagpur_2025-04.xlsx, Nagpur_2025-05.xlsx, ...

HEADER_MAP = {
    "invoice no": "invoice_no", "inv no": "invoice_no",
    "date": "date", "bill date": "date", "invoice date": "date",
    "party name": "customer", "customer name": "customer", "customer": "customer",
    "amount": "amount", "net amount": "amount", "net amt": "amount",
    "qty": "qty", "quantity": "qty",
}

def tidy(path: Path) -> pd.DataFrame:
    df = pd.read_excel(path, dtype=str)        # read as text, convert deliberately later
    keys = [c.strip().lower().rstrip(".") for c in df.columns]
    df.columns = [HEADER_MAP.get(k, k.replace(" ", "_")) for k in keys]
    df["branch"] = path.stem.split("_")[0]     # Nagpur_2025-04 -> Nagpur
    df["source_file"] = path.name
    return df

files = [p for p in sorted(RAW.glob("*.xlsx")) if not p.name.startswith("~$")]
sales = pd.concat([tidy(p) for p in files], ignore_index=True)

sales["date"] = pd.to_datetime(sales["date"], dayfirst=True, errors="coerce")
sales["amount"] = pd.to_numeric(sales["amount"].str.replace(",", "", regex=False),
                                errors="coerce")
sales["customer"] = sales["customer"].str.strip().str.title()

before = len(sales)
sales = sales.drop_duplicates(subset=["invoice_no", "branch"], keep="last")
print(f"{len(files)} files, {before} rows in, {before - len(sales)} duplicates dropped")
print(sales.groupby(sales["date"].dt.to_period("M"))["amount"].agg(["count", "sum"]))

sales.to_excel("sales_FY2025-26.xlsx", index=False)

Two lines are doing the real work. dtype=str on the read means pandas does not guess types per file, which is what otherwise gives you an invoice_no column that is integers in nine files and strings in three, and a merge that fails to match them. The errors="coerce" on both conversions turns anything unparseable into NaT or NaN instead of raising, so the script finishes and you can count the failures with sales["date"].isna().sum() rather than debugging one bad cell at a time.

Three smaller details that will bite otherwise. sorted() on the glob gives you a deterministic order, which matters because keep="last" depends on it. The ~$ filter skips the lock files Excel creates while a workbook is open, which are real .xlsx paths that read_excel cannot parse. And pd.concat fills missing columns with NaN, so a heading that exists in only one file gives you a mostly-empty column, which is exactly the signal you want.

If you are new to this, the pandas track covers read_excel, concat and groupby in the order you need them here.

Checks to run before you trust the merged file

1. Row arithmetic. Sum the row counts of the twelve source files and compare with the merged count plus dropped duplicates. The numbers must reconcile exactly.
2. One column total per file. Total amount per source_file and compare with each workbook's own total. This catches column shifts, which row counts do not.
3. Month coverage. Group by month and check you have twelve non-empty groups. A file read but silently empty is a common failure.
4. Unparsed values. Count NaT dates and NaN amounts. A handful is normal, a few hundred means a format you have not handled.
5. Duplicate sample. Look at ten of the rows you dropped before you accept the drop.

Where the limits are

pd.concat is fine to a few million rows on a laptop with 8 GB of RAM, and the whole thing runs in a couple of seconds at the scale described here. Past that, read the files in a loop and append to a Parquet dataset, or use DuckDB to query the folder directly. Neither is worth the complexity for twelve monthly files.

The real limit is not technical. If the twelve files keep arriving with different headings, the fix is upstream: get the export template fixed so the headings stop moving. A script that patches five spellings of Amount is a script that will need a sixth entry in March. If the reporting is more tangled than one folder of files, automation work is the kind of thing worth handing over.

Frequently asked questions

Can the browser tool add a branch column from the filename? Not automatically. It adds a Source file column with the filename in it, and you derive the branch from that with a formula or a Text to Columns split. The pandas version does it directly with path.stem.split("_")[0].

What if one month is a CSV and the rest are XLSX? The merge tool takes both in the same batch. In the script, branch on the suffix: pd.read_csv(path, dtype=str) for .csv and pd.read_excel(path, dtype=str) otherwise, and glob for both patterns.

Should I keep the duplicate rows instead of dropping them? Keep them in a separate file. Write the dropped rows out with sales[sales.duplicated(subset=["invoice_no", "branch"], keep="last")] before the drop, so accounts can check whether any were real double entries in the billing system.

How do I get a quick look at the merged file before analysing it? Drop it into the CSV and Excel analyzer for row and column counts, detected types, missing-cell percentages and duplicate row count in a few seconds.