Merge Files with Python: Combine Dozens of Excel/CSV Files in One Click

Running retail, accounts or operations, you probably face the same thing every month: each branch, each department, each month hands in its own Excel or CSV file, and you open them one by one, copy-paste, and stitch them into one master table. Ten files is bearable — dozens will have you pasting until your eyes glaze over.

This tutorial uses Python's pandas and glob to read every CSV/Excel in a folder and merge them into a single file in about a dozen lines. Install the two packages once, then use them every day.

Step 1: Install pandas

pandas is Python's most-used data toolkit — great for reading and writing CSV and Excel. If you haven't installed it yet, run this in Command Prompt:

pip install pandas

If you're working with .xlsx files, you'll also need openpyxl:

pip install openpyxl

Step 2: Write the script

Create a file called merge_files.py and paste in the code below. Change FOLDER at the top to the folder holding your files.

import glob import pandas as pd FOLDER = "reports/*.csv" # All CSV files live in this folder files = sorted(glob.glob(FOLDER)) print("Found", len(files), "files") frames = [] for f in files: df = pd.read_csv(f) frames.append(df) combined = pd.concat(frames, ignore_index=True) combined.to_csv("reports/combined.csv", index=False, encoding="utf-8-sig") print("Merged", len(combined), "rows")

Line by line

  • glob.glob(FOLDER) — uses the *.csv wildcard to find every CSV in the folder and returns a list of filenames.
  • sorted(...) — sorts the filenames so the merged table follows filename order and stays tidy.
  • pd.read_csv(f) — reads each CSV into a DataFrame (a table).
  • pd.concat(frames, ignore_index=True) — stacks all the tables top to bottom into one big table. ignore_index=True renumbers rows from 0 so they don't clash.
  • to_csv(..., encoding="utf-8-sig") — writes a new CSV. utf-8-sig keeps Chinese readable when reopened in Excel.

Step 3: Switch to Excel files

If you receive .xlsx files, just change two lines: swap *.csv for *.xlsx, and read_csv for read_excel:

FOLDER = "reports/*.xlsx" ... df = pd.read_excel(f)

Everything else — the concat and the export — stays exactly the same. You can even mix CSV and Excel by adding another glob pass for each type.

Common problem: why does the merged table misalign?

The most common merge failure is inconsistent columns: one file calls a column "Name", another "Customer name", another has an extra column. Before merging, make sure every file uses the same column names in the same order. If names differ, pandas creates separate columns and the data won't line up.

Summary

Python Tutorial: with pandas and glob, a dozen lines of code have the computer read every CSV/Excel in a folder and merge them into one master table — turning hours of pasting into a single command. Using the same idea, combine it with our earlier scheduling tutorial to auto-merge each branch's report on the first of the month, so you open your desk to a ready-made summary.