Py LearnPythonForExcelThe Excel-user's path to Python
Home › Course › Module 12 › Merging many files into one

Merging many files into one

Module 12 · Automating Boring Excel Tasks9 min readBeginner

What you'll learn

  • Combine every file in a folder
  • Tag each row with its source
  • Handle a folder of mixed-format files gracefully

The script

from pathlib import Path
import pandas as pd

SRC = Path("monthly")
DST = Path("combined.xlsx")

frames = []
for path in sorted(SRC.glob("*.xlsx")):
    df = pd.read_excel(path)
    df["source_file"] = path.name
    frames.append(df)
    print(f"  loaded {path.name}: {len(df):,} rows")

combined = pd.concat(frames, ignore_index=True)
combined.to_excel(DST, index=False, sheet_name="All")
print(f"Wrote {len(combined):,} rows to {DST}")

Mixed CSV + XLSX

def read_any(p):
    if p.suffix == ".csv":
        return pd.read_csv(p)
    return pd.read_excel(p)

frames = [read_any(p) for p in SRC.iterdir() if p.suffix in (".csv", ".xlsx")]

Skip broken files

frames = []
errors = []
for path in SRC.glob("*"):
    try:
        df = read_any(path)
        df["source_file"] = path.name
        frames.append(df)
    except Exception as e:
        errors.append((path.name, str(e)))

print(f"Processed: {len(frames)},  Errors: {len(errors)}")
for name, msg in errors:
    print(f"  {name}: {msg}")

Key takeaways

  • folder.glob("*.xlsx") + pd.concat() is the merge skeleton.
  • Tag every row with source_file — you'll always want it later.
  • Wrap in try/except so one broken file doesn't stop the whole job.

Build a merger

Put 3-5 small CSVs in a folder. Write a script that merges them into one Excel file with a "source" column.

📹 Video walkthrough
A video walkthrough of this lesson will be embedded here. Until then, the written walkthrough above mirrors what the video will cover step-for-step.