Moving data out of spreadsheets without a season of re-keying

A tool that replaces spreadsheets inherits their content, and the content is where the fear lives: dozens of files, kept by different people over years, with columns that changed meaning in 2019 and a tab called 'old, do not use' that is in fact used. The instinct is to have someone re-key everything into the new tool, cleanly. That is the season of re-keying, and it is how migrations lose a year.

The better reading is that the spreadsheets are the specification. Every column is a field somebody needed; every colour code is a status; every 'see note' is an exception the tool will have to express. Before a line of the migration is written, we read the files with the people who keep them and write down what each column means, what it should mean, and where the two differ.

The second step is reconciliation, done by machine and checked by people. The same property appears in three files with three spellings; a lease has an end date in one file and none in another; an index revision was applied in the rent column but not in the history. A script finds every such case and produces one list: this is what the files say, this is what they disagree on.

That list goes back to the town hall, the fund or the company, not to us. Only the people who know the portfolio can say which of two rents is the real one. Our job is to make the list short, precise and ordered by consequence, so that a morning of decisions settles most of it.

Loading is scripted and repeatable: the same script runs against the same files until the list of inconsistencies is empty, then runs once more on the day of the switch with the files as they were that morning. Nothing is re-keyed. The staff keep working in their spreadsheets until the last run, so there is no frozen month during which nothing can change.

The script also keeps the trail: which row in which file became which record, so that a doubt six months later can be traced back to its source instead of argued about.

For the town of Saint-Maur-des-Fossés, that method moved around sixty properties, their leases and their rent histories from spreadsheets into m2. The reconciliation list had a few dozen lines; the town settled them in two sittings. On the day of the switch the staff opened m2 with their portfolio already in it, correct to the last revision, and the spreadsheets were archived rather than retyped.

It is the least visible part of a deployment and the one that decides whether the tool is trusted. A register that is right on day one is used on day two; a register the staff have to check against the old files is a second spreadsheet.