The goal
Migrate a messy legacy referrals workbook (fused company/contact cells, FREE JOB rows, negative-amount refunds, multi-job cells) into a new template, reproducibly — no silent errors from manual cleanup.
The solution
A single Python script (migrate_cleaner.py) that reads every sheet from the uncleaned workbook, applies four deterministic rules (FREE JOB exclusion, negative-amount exclusion, company/contact parentheses split, multi-job-number row explosion), casts currency-like strings to real numbers, and writes output against the exact column order of the provided template.
Highlights
- Template-driven output makes the migration target ingestable without further edits
- 4 deterministic rules replace days of manual cleanup
- Row-explosion for multi-job cells preserves downstream linkage
The challenge
Thousands of rows had 'Company Name (Contact Name)' fused into a single Referral From cell.
Regex ^(.*?)\s*\((.*?)\)\s*$ captures company and parenthesized contact; no-parens rows are treated as company-only with an empty contact column.
'FREE JOB' strings had inconsistent casing and whitespace.
Case-insensitive regex \bFREE\s*JOB\b with flexible whitespace catches all observed variants.
Job numbers arrived fused with mixed separators: ',', '/', 'and', '&'.
Compiled regex [,/]| and |\s*&\s* and row explosion — each original row with N job numbers becomes N rows so downstream ingestion can link per-job.
Amount column mixed floats, '$1,234.00' strings, blanks and hyphens.
to_number() strips $ and commas, returns None for blanks/invalid, real float otherwise; negatives route into has_negative_amount() for exclusion.
What was delivered
- migrate_cleaner.py single-entry cleaner script
- requirements.txt (pandas + openpyxl)
- Output workbook in the template's exact column order
Results
4
Cleaning rules
All sheets
Sheets handled
Template-matched
Output shape
What it taught me
- Template-driven output order is the simplest way to keep migrations ingestable — no follow-up mapping step
- Every 'it's usually X' pattern in real data has exceptions; generous whitespace and case regexes save hours