Combine dozens of Excel files into one with Python and pandas

Every branch sends its own sales file, every technician fills in their own timesheet, every month the management system exports a new file. At the end of the quarter someone has to open them one by one and copy everything into a single sheet. It is slow, error-prone work: a skipped row, a forgotten file, a column pasted in the wrong place. The pandas library lets you do the consolidation in a few seconds and, above all, the same way every time.

The basic idea

pandas represents a table as a DataFrame. Consolidation takes three steps: read each file into a DataFrame, put them in a list, concatenate them with pd.concat.

pip install pandas openpyxl

from pathlib import Path
import pandas as pd

folder = Path("C:/Data/Branch sales")
tables = []
for file in sorted(folder.glob("*.xlsx")):
    df = pd.read_excel(file)
    df["source_file"] = file.name
    tables.append(df)

combined = pd.concat(tables, ignore_index=True)
combined.to_excel("consolidated_sales.xlsx", index=False)
print(len(tables), "files,", len(combined), "rows")

ignore_index=True renumbers the rows from scratch, avoiding duplicate indexes. The openpyxl package is required because pandas uses it to read and write .xlsx files.

Always knowing where each row comes from

The source_file column added in the example is the most useful detail of the whole script. When a figure in the consolidated table looks wrong, it lets you trace it straight back to the file it came from. If the file name carries information, such as sales_Turin_2025-03.xlsx, you can also extract it into separate columns:

branch, period = file.stem.split("_")[1:3]
df["branch"] = branch
df["period"] = period

This only works if names follow a strict convention, which is a good reason to agree on one with whoever sends the files.

When the columns don't match

In real life the files are never perfectly identical. Someone writes "Amount", someone else "Amount €", a third person adds a notes column. pd.concat aligns columns by name: those with different names become separate columns, full of empty values for the files that lack them. The fix is to normalise the names before concatenating:

rename_map = {"amount €": "amount", "amt.": "amount", "doc date": "date"}

df.columns = df.columns.str.strip().str.lower()
df = df.rename(columns=rename_map)
df = df[["date", "customer", "amount"]]

The last line keeps only the expected columns, in the desired order. If one is missing, pandas raises an error naming it: a clear error is better than an incomplete consolidation.

Headers not in the first row, and multiple sheets

Many files have a few title rows before the actual table. The skiprows parameter skips them: pd.read_excel(file, skiprows=3) starts reading from the fourth row. If the data is in a specific sheet, you name it with sheet_name="Sales". With sheet_name=None you get all the sheets in the file as a dictionary instead, which is useful when each sheet represents a month.

CSV files too

The same pattern works for CSV files, with a little extra care for European formats:

df = pd.read_csv(file, sep=";", decimal=",", thousands=".", encoding="utf-8")

Without decimal="," an amount like 1.234,56 would be read as text. If the file comes from an old management system and accented letters show up as strange symbols, try encoding="cp1252", the historic Windows encoding.

Duplicates and checks

It happens that the same file is sent twice under different names, or that two exports overlap by a few days. Before handing over the consolidated table it is worth looking for duplicates:

dupes = combined.duplicated(subset=["date", "customer", "amount"], keep=False)
print(dupes.sum(), "suspicious rows")
combined[dupes].to_excel("to_check.xlsx", index=False)

It is not wise to delete them blindly: two identical sales on the same day may be genuine. Saving them in a separate file lets a person decide. In my work I also add a summary per file, with the number of rows and the sum of the amounts, to compare with the totals each sender reports.

summary = combined.groupby("source_file")["amount"].agg(["count", "sum"])
print(summary)

In short

• pd.concat merges in one go the DataFrames read from every file in a folder.
• Always add a column with the name of the source file.
• Normalise column names and select only the expected ones.
• For European CSV files specify separator, decimals and encoding.
• Flag duplicates rather than deleting them, and compare totals per file.