Migrating spreadsheet data when names, dates and fields don't match
The school sends us eleven years of student records. Three spreadsheets, each from a different admin era. The first has "Admission Date" in DD-MM-YYYY. The second uses "Joining Date" in MM/DD/YY. The third splits the date across two columns — one for day, one for a month name abbreviated in Hindi.
Nobody who built these sheets is still around. The current office staff knows what the columns mean but not why they were structured that way. Our job is to get this into a PostgreSQL table that the ERP can query without someone having to open Excel every time they need a student's history.
The first decision is whether to clean in SQL or in Python. We use Python when the data is messy enough that we need to see intermediate states — a CSV reader, a column-mapping dictionary, and print statements showing how many rows failed each transform. SQL is cleaner once the data is already rectangular and you are doing set operations. For the initial pass, Python wins because the input is not rectangular at all.
The names are the hardest part. "Aarav Sharma" in one sheet is "Aarav S." in another and "Aarav" with a father's name in the third. We build a mapping file keyed by admission number, which is the one field that is consistent across all three sheets — mostly. Eight admission numbers appear twice in the 2015 sheet because a temp staffer duplicated a batch by mistake. We catch that with a uniqueness check before doing anything else.
For dates, we try every column against three formats and log the rows that match none of them. Out of 4,200 rows, 87 fail all three formats. We write those to a separate CSV and send it back to the school. They fix 71 of them manually. The remaining 16 are students who joined before the school digitised anything, and the only record is a handwritten register that nobody wants to touch.
We load those 16 with a NULL admission date and a flag column set to `needs_review`. That is better than guessing. A wrong date in an ERP is worse than a missing date, because a wrong date looks correct and someone will make a decision based on it.
Missing fields we handle differently depending on the field. A missing parent phone number becomes NULL — the ERP can prompt for it on next contact. A missing caste category, which determines a scholarship in the Uttar Pradesh state system, gets flagged but not defaulted. We do not want to guess wrong on something that affects fees.
The transformation script ends up at about 180 lines of Python. It reads three CSVs, normalises dates against a format list, maps columns by a dictionary we wrote after reading the first 50 rows of each sheet, cross-references admission numbers to catch duplicates, and writes a single clean CSV plus a rejection report.
We load that clean CSV into Postgres with `COPY` and run foreign key checks. About 40 rows fail because they reference a class ID that does not exist in the classes table. That is a data issue, not a migration issue — the school created classes in the old system that they never set up in the ERP. We add those classes, re-run the load, and it passes.
The whole migration takes two days of back-and-forth. The Python script is 180 lines. The Excel files that started this are now archived. The ERP is the source of truth going forward, and nobody has to wonder whether the admission date is the third column or the fourth.