Data Engineering · Python & PostgreSQL

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.

Production-readyv0.5.0

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.

  1. Input

    Excel workbooks

    Many files, many sheets

    Operator

    migratex CLI, dry-run first

  2. CLI layer (Click)

    Commands

    extract, migrate, db:status, db:schema, db:validate

    Options

    Phone region, output dir, mode, dry run

  3. Core pipeline

    Each component returns status, statistics and errors

    Extractor

    Workbook to clean CSV

    Cleaner

    Config-driven rules per table

    Importer

    COPY or INSERT, batched, transactional

    Validators & reporter

    Schema, FK and row-count checks

  4. 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 fast

Building something similar?

Tell us what you need. We'll come back with a plan and a realistic estimate.