A Python script that merges many CSV and Excel files into one table, with the columns lined up

Example result

python merge_sales.py --month 2026-10

2026-11-02 09:14:03 INFO  Folder: sales/2026-10, 9 files found
2026-11-02 09:14:03 INFO  shop-01.csv: 1,284 rows
2026-11-02 09:14:04 INFO  shop-02.xlsx: 967 rows
2026-11-02 09:14:04 WARN  shop-07.csv: 3 rows without a price skipped (lines 88, 412, 913)
2026-11-02 09:14:05 INFO  Done: 10,412 rows -> report-2026-10.xlsx (sheets "Sales" and "Totals")

[columns]
date = Date, Sale date, Day
product = Product, Item, Name
quantity = Qty, Quantity, Pcs
price = Price, Unit price

def read_shop_file(path: Path, columns: dict[str, list[str]]) -> pd.DataFrame:
    """Reads one shop's file and gives it the report's column names."""
    table = pd.read_excel(path) if path.suffix == ".xlsx" else pd.read_csv(path, encoding="utf-8-sig")
    renamed = {}
    for name, variants in columns.items():
        found = next((c for c in table.columns if c.strip() in variants), None)
        if found is None:
            raise ReportError(f"{path.name}: no column for '{name}' (looked for {', '.join(variants)})")
        renamed[found] = name
    table = table.rename(columns=renamed)[list(columns)]
    table["shop"] = path.stem
    return table

About this service

Monthly exports, reports from branches, files from several suppliers: when the same data comes in dozens of CSV and Excel files, copying it into one table by hand is slow and easy to get wrong. This Python script reads every file in the folder you set, including several sheets in one workbook, lines the columns up by name, adds a column with the source file and saves one CSV or Excel file. From Standard it handles columns that are named differently in different files and keeps a log of rows it could not read. Send us two or three example files with a few rows each. You run it yourself on your computer. Excel holds up to 1,048,576 rows on a sheet, so a larger result is saved as CSV.

What is included

• What you getA ZIP archive with the Python script, a README with step-by-step instructions and, from Standard, a settings file with your folder and column names; the code is also shown on the order page.
• CSV and Excel togetherFiles of either kind in one folder, several sheets per workbook
• Columns lined upBy name, even when the order differs between files
• Source columnEach row shows which file it came from
• Encodings and separatorsCommas or semicolons, UTF-8 or older Windows encodings
• Readable codeWith comments, easy to add a column later

How it works

1. 1
   ExamplesYou send two or three example files and say what the result should look like
2. 2
   CodeWe write the script and the guide to running it
3. 3
   Try itYou run it on a copy of your folder and check the totals
4. 4
   ChangesYou ask for changes within your package and send any error message

What we need from you

Two or three of the files you merge, cut down to a few rows (change any real names or amounts if you like), roughly how many files there are and how big they get, and what the result should be: CSV or Excel, which columns in what order, and whether repeated rows should stay. If files differ (other column names, extra header lines), an example of each kind. Your system: Windows, macOS or Linux.

We will ask you:

• Which files do you merge?
• What should the result look like?
• Do the files differ?
• Where will it run?

Compare packages

Questions and answers

Did not find your answer? Ask us