The 4 Excel jobs I refuse to do by hand anymore (so I automated them in Python)

Wei Li — Fri, 25 Sep 2026 03:59:45 +0000

Excel work has a way of eating entire afternoons: twelve monthly workbooks to merge, a sheet per month to split back out, formulas that break the moment the file touches pandas or a BI tool. These four command-line tools (openpyxl is the only dependency) do the boring versions of those jobs.

1. Summarize a workbook before opening it

python xlsx_summary.py report.xlsx

Prints rows, columns and a header preview per sheet. For a folder of workbooks, it's the fastest "what am I even looking at" pass.

2. Merge monthly workbooks — with a header contract

xlsx_merge.py refuses files whose columns don't match (silently stacked mis-aligned exports are how data goes wrong), tolerates trailing empty header cells, and can tag every row with its source file:

python xlsx_merge.py year.xlsx jan.xlsx feb.xlsx mar.xlsx --add-source

3. One file per sheet

python xlsx_split_sheets.py big.xlsx

Each sheet becomes its own workbook — handy when downstream tools want single sheets.

4. Flatten formulas to values

The classic "why is this cell empty in pandas" problem: formulas have no cached value until Excel recalculates. xlsx_values.py writes a formula-free copy:

python xlsx_values.py report.xlsx

The take

The tools are tiny on purpose: each maps to one flag, one transformation, and always prints what it changed. If a script's output can't be audited in five seconds, it's not automation — it's a liability.

What's next: I'm bundling these (plus the CSV cleanup tools from my previous post) into downloadable toolkits with READMEs and a money-back guarantee. I'll link them here as soon as they're live — drop a comment if you want a ping.

Questions about gnarly .xlsx edge cases? Ask in the comments.

I got tired of cleaning messy CSVs by hand, so I wrote 5 tiny Python tools

Wei Li — Fri, 25 Sep 2026 03:25:09 +0000

Every data job I've ever touched starts the same way: someone hands me an export that's almost usable. Duplicated rows. Columns named "Order Date ". Numbers stored as text. I kept writing the same cleanup snippets over and over, so I turned them into five small command-line tools. They have zero dependencies (just Python 3.8+), each one does exactly one job, and every one of them prints a summary of what it changed so you can trust the output.

Here's what each tool does and how to use it.

1. csv_cleaner.py — the one you'll use the most

Deduplicates rows, trims whitespace in every cell, normalizes headers (Order Date → order_date), and prints a report:

python csv_cleaner.py messy.csv --dedupe --trim --headers --summary

Output:

cleaned file written -> messy_clean.csv
--- summary ---
           rows_in: 4
duplicates_removed: 1
empty_rows_dropped: 1
          rows_out: 2
          columns: 3 -> ['name', 'age', 'city']

2. csv_splitter.py — when the file is too big to open

Split by rows per chunk or by number of parts:

python csv_splitter.py big.csv --rows 100000
python csv_splitter.py big.csv --parts 4

3. csv_merger.py — merge monthly exports without surprises

Refuses to merge files with different headers (instead of silently mangling your data), skips stray repeated header lines, and can tag each row with its source file:

python csv_merger.py year.csv jan.csv feb.csv mar.csv --add-source

4. csv_to_json.py — feed the cleaned data to an API

Converts to a JSON array or JSON Lines, with smart type conversion ("30" → 30, "true" → true, "" → null):

python csv_to_json.py clean.csv clean.json
python csv_to_json.py clean.csv clean.jsonl --jsonl

5. file_organizer.py — not just CSVs

Sorts any folder (hello, Downloads) into subfolders by type, extension, or year-month — with a dry-run mode so you can see the plan before anything moves:

python file_organizer.py ~/Downloads --by type --dry-run

The patterns behind all five

If you're writing your own version, the whole toolkit rests on three ideas:

Read with utf-8-sig (kills the BOM Excel adds) and write with newline="".
Every flag maps to one obvious transformation — no magic.
Always print what changed. Silent success is how data bugs survive.

What's next: I'm packaging these five tools (plus a README with more examples) into a small downloadable toolkit. Drop a comment if you'd find that useful — I'll link it here as soon as it's live.

Questions or messy-data edge cases? Ask in the comments — I read everything.

DEV Community: Wei Li