The Great Migration: Translating [Legacy Spreadsheets](/blog/the-great-migration-translating-legacy-spreadsheets-into-strict-data-structures) into Strict Data Structures

Building a new database from scratch is relatively easy. The real nightmare of enterprise architecture is the Great Migration—taking decades of dirty, unstructured, legacy data and forcing it into a newly built, strict relational environment. When I began migrating Colorado's Drug Testing Agency off their chaotic Google Sheets and into the pristine AWS Fargate Odoo environment, I quickly realized that the old data was actively fighting the new architecture.

The Anatomy of Dirty Data

The core issue with spreadsheets is that they don't enforce data types. In a single column meant for a client's Date of Birth, I found properly formatted dates, raw text strings like "Unknown," and completely blank cells. In the billing columns, numerical values were mixed with random notes like "Paid in Cash on Tuesday."

Odoo's ORM is highly deterministic. If you try to insert a text string into a Date field, the database immediately throws a fatal error and halts the entire migration. I couldn't just export a CSV from Google Sheets and import it into Odoo. I had to build a massive ETL (Extract, Transform, Load) pipeline to sanitize the agency's history before it could enter the secure vault.

Engineering the ETL Pipeline

I utilized a combination of Python scripts and n8n webhooks to act as the filtration system. The script extracted the raw rows from the Google API and immediately began applying strict fuzzy logic and regex parsing. It stripped letters out of phone number columns, reformatted every date into strict ISO 8601 standards, and cross-referenced client names against the Cordant Sentry API to correct spelling errors.

If a row was so corrupted that the script couldn't confidently fix it, the ETL pipeline dropped it into a manual triage queue. This forced the staff to manually review the legacy errors and make a definitive choice before the data was allowed into the new Odoo ecosystem.

The Cost of Migration

Data migration is slow, tedious, and often exposes the darkest operational flaws of a business. But the resulting clarity is worth every hour spent debugging regex scripts. The agency can now generate 5-year longitudinal reports in milliseconds.

If your company is afraid to upgrade its software because the old data is too messy, you are letting your past dictate your future. I am ready to engineer the ETL pipeline that will set your data free.