DEV Community

Python Tools Lab
Python Tools Lab

Posted on

How to Merge 100s of Excel & CSV Files in 5 Lines of Python

Copying and pasting rows across dozens of daily or monthly CSV and Excel exports is one of the fastest ways to waste hours of your week.

With Python and Pandas, you can combine every CSV and Excel file in a folder into one clean master workbook in seconds:

import glob, os, pandas as pd

all_files = glob.glob("./raw_files/.csv") + glob.glob("./raw_files/.xlsx")
frames = []

for f in all_files:
df = pd.read_csv(f) if f.endswith(".csv") else pd.read_excel(f)
df["Source_File"] = os.path.basename(f)
frames.append(df)

pd.concat(frames, ignore_index=True).to_excel("merged_master_data.xlsx", index=False)

Enter fullscreen mode Exit fullscreen mode




Want 4 Complete Excel & CSV Automation Scripts?

If you need ready-to-run templates for merging spreadsheets, cleaning messy currencies/duplicates, generating styled executive Excel reports, and catching fuzzy name typos, grab The No-BS Python Excel & CSV Automation Kit (50% launch discount with code EXCEL50):

Download the Playbook & 4 Templates (50% OFF): https://tugrulian8.gumroad.

Top comments (0)