Migratex
A production ETL command-line tool that moves messy Excel workbooks into PostgreSQL, cleaned, validated and in the right order.
Migratex extracts every sheet of an Excel workbook to clean CSV, applies configurable cleaning rules, and loads the result into PostgreSQL or Supabase inside transactions, respecting foreign keys.
247,709
Records migrated
99
Excel sheets
99.96%
Import success rate
98
Automated tests
The challenge
A client's business ran on six Excel files and 99 sheets: inconsistent headers, phone numbers in several country formats, broken formulas and duplicate rows. All of it had to land in a relational database without losing records or breaking referential integrity.
What we built
We wrote the migration as a pipeline, then refactored it into a reusable Python CLI with its own tests and docs: extract, transform and load as separate components, every rule in one configuration file, and imports ordered by foreign-key dependency.
Features
Extract
- Multi-sheet extraction to CSV with automatic naming
- Header normalization to snake_case
- Duplicate, empty row and empty column cleanup
- Excel error handling (#REF!, #N/A) and high-null column removal
Transform
- Phone normalization for six countries: US, CO, MX, ES, AR, BR
- Email validation and normalization, full-name splitting
- Encoding fixes for Spanish characters
- Foreign key validation against the target database
Load
- Fast mode with PostgreSQL COPY for bulk imports
- Standard INSERT with ON CONFLICT for upserts
- Batches with progress tracking and rollback
- Pre-import and post-import validation
Architecture
Supporting a new data source means editing configuration, not code: cleaning rules, column mappings, foreign keys and import order all live in one file.
Input
Excel workbooks
Many files, many sheets
Operator
migratex CLI, dry-run first
CLI layer (Click)
Commands
extract, migrate, db:status, db:schema, db:validate
Options
Phone region, output dir, mode, dry run
Core pipeline
Each component returns status, statistics and errorsExtractor
Workbook to clean CSV
Cleaner
Config-driven rules per table
Importer
COPY or INSERT, batched, transactional
Validators & reporter
Schema, FK and row-count checks
Target
PostgreSQL
Self-hosted or managed
Supabase
Hosted PostgreSQL
Engineering highlights
Safe dynamic SQL
Table and column names are validated against a strict pattern and escaped with psycopg2.sql. Values are always parameterized; no query is built by string interpolation.
Foreign-key ordering
Master data loads first, then dependent tables, then transactions, so imports never trip integrity constraints.
All or nothing
Every import runs in a transaction. If a batch fails, the operation rolls back and the database is never left half-loaded.
Two speeds
COPY for clean bulk loads, INSERT with ON CONFLICT for incremental updates. The mode is chosen per run.
Tech stack
- Language
- Python 3.8+
- Data processing
- Pandas, openpyxl
- Database
- PostgreSQL (psycopg2), Supabase
- CLI
- Click
- Configuration
- python-dotenv, central cleaning config
- Testing
- pytest
A typical run
# Workbook to clean CSVs, Colombian phone format
migratex --region co extract ./data/inventory.xlsx -o ./output/
# Validate against the database without writing
migratex migrate ./output/ --auto --dry-run
# Bulk load with PostgreSQL COPY
migratex migrate ./output/ --auto --mode fastBuilding something similar?
Tell us what you need. We'll come back with a plan and a realistic estimate.