Legacy Data Cleanup

The project was replacing a DOS-era system with a web application, and the old data did not map cleanly into the new structure. Free text had been entered into narrow fixed-width fields and continued across neighboring rows or columns. When some of those records were imported, line boundaries disappeared, words were joined together, comments were split into fragments, and values ended up in places where they did not belong.

The challenge was not only finding unusual values. Some rare values were real, and some strange-looking fragments only made sense when read beside the surrounding cells. A cleanup system needed enough context to reconstruct the intended information without confidently rewriting valid historical records.

I started with scripts that scanned columns, counted distinct values, sampled suspicious patterns, and identified likely corruption. This made the rules reflect the actual data instead of assumptions about what bad data should look like. Mostly unique fields were treated differently from categorical fields, where one rare value among thousands of repeated values was much more likely to be a typo or misplaced note.

The profiling output became structured JSON containing the original values, surrounding rows, likely issue types, and enough context for the next stage. The source database remained read-only during discovery.

There was too much material to review in one prompt, so I divided the findings into independent JSON batches. Each batch explained the table layout, reading order, relevant rules, original values, and the exact response format. The AI could then propose reconstructed comments, spelling corrections, values to move, or cases that still needed a person.

The output contract mattered as much as the prompt. Import scripts rejected incomplete batches, malformed JSON, unknown categories, missing record identifiers, and findings that referred to data outside the supplied batch. That turned AI output into a checked intermediate artifact rather than something that could flow directly into the database.

I built a local review application that grouped every related table for one asset onto the same screen. Suggested comments appeared beside the source table, with the exact contributing cells highlighted. Reviewers could approve a suggestion, edit its wording, mark it for more review, dismiss it, or inspect the raw fragments before deciding.

Each approved comment kept its category, final text, source table, column, record identifier, and captured original value. Decisions and edits were saved as work progressed, so a long review could be paused and resumed without losing its place.

Some corruption had a consistent mechanical cause: a missing space where one fixed-width line had wrapped into the next. I created a separate deterministic fixer for those cases. It only split tokens from an explicitly reviewed list and only inserted or removed spaces or collapsed doubled punctuation. It was not allowed to add, delete, or reorder any letter or number.

The tool rechecked that invariant for every proposed change and aborted the entire run if even one row failed. It was dry-run by default, produced a complete before-and-after audit file, made a backup before applying changes, used a single transaction, and was designed to be idempotent. A full-dataset dry run identified 21,616 corrections with zero invariant violations.

I produced this tooling through an AI-generated development process. I gave the coding model representative records, explained where the data came from and what a valid transformation looked like, then used it to generate the profilers, algorithms, batch converters, validation scripts, and review interfaces.

My role was to define the problem, design the data passed between each stage, supply examples and constraints, inspect the proposed behavior, and keep iterating when the output missed an edge case. Using AI let me test several approaches quickly, but it also made independent verification especially important. The strict schemas, invariants, audit files, and human approval steps were how I made the generated implementation accountable.

Instead of asking someone to manually read every table and reconstruct every fragment from scratch, the system brought likely problems and suggested repairs to them with the evidence already attached. Routine issues could be handled consistently, while uncertain cases stayed visible and required a decision.

The project taught me that automation for messy historical data should not be measured only by how many changes it can produce. The better question is how clearly it can explain each change, how safely it can refuse uncertain work, and how easy it is for a person to recover the original value.