Demo: messy CSV files to a clean Excel report with Python by Prabhudutt MishraDemo: messy CSV files to a clean Excel report with Python by Prabhudutt Mishra

Demo: messy CSV files to a clean Excel report with Python

Prabhudutt Mishra

Business Workflow Automation

Microsoft Excel

pandas

Python

Data

A demo I built with made-up sample data to show what my Excel and CSV scripts do. It is my own sample, not client work.

The input is two CSV files with the usual problems: extra spaces, names in mixed case, dates in three different formats, a number stored as text, an empty cell and duplicate rows.

The script reads every CSV file in a folder, cleans the names, dates and amounts, drops rows with missing values and duplicate rows, and saves report.xlsx with a clean Data sheet and a Summary sheet with totals per customer. Running it again on new files takes seconds.

The whole script is about 20 lines of Python with pandas, shown below.

The same approach works for merging a folder of Excel files, pulling tables out of text-based PDFs, or sending the results by email or to Google Sheets.

I used AI tools to speed up the routine parts, then ran and checked the script on the sample files myself.

from pathlib import Path
import pandas as pd

# 1. Read every CSV file in the folder
cols = ["customer", "date", "amount"]
df = pd.concat(pd.read_csv(f, names=cols, header=0)
               for f in Path("input").glob("*.csv"))

# 2. Clean names, dates and amounts
df["customer"] = df["customer"].str.strip().str.title()
df["date"] = pd.to_datetime(df["date"], format="mixed", dayfirst=True, errors="coerce")
df["amount"] = pd.to_numeric(df["amount"].astype(str).str.replace(",", ""), errors="coerce")

# 3. Drop empty rows and duplicates
df = df.dropna().drop_duplicates(["customer", "date"])

# 4. Save a clean Excel report with a summary sheet
with pd.ExcelWriter("report.xlsx") as xl:
    df.sort_values("customer").to_excel(xl, sheet_name="Data", index=False)
    df.groupby("customer")["amount"].sum().to_excel(xl, sheet_name="Summary")

Like this project

Posted Oct 23, 2026

A demo script I built with sample data: two messy CSV files cleaned and merged into one Excel report.

Likes

Views

Timeline

Oct 16, 2026 - Oct 16, 2026