How to move data from spreadsheets into a system without stopping the company

What is in a spreadsheet after a few years

A spreadsheet a company has kept for years has a fixed set of problems. The same customer entered three times, differently each time. A phone number in the address column. Dates in three formats. A “notes” column holding everything: the discount, the deadline, the fact that this customer pays late. Rows someone once copied and never deleted.

None of that bothers a spreadsheet, because a person reads it and understands. A system needs one customer to be one customer, a date to be a date and a discount to be a discount. So moving the data starts with tidying, not copying.

The order that works

1. A list of what you have

We gather every spreadsheet that takes part in the process. Including the “private” ones on a salesperson’s laptop and the ones everyone forgot about. For each we establish what it holds, who fills it in and whether it should go into the system at all.

2. Deciding what to move

Not everything has to go in. Customers and products move in full. Jobs and orders usually for the last year or two. Older ones stay in the spreadsheet as an archive. The less we move, the faster and cheaper, so it is worth thinking about what you really go back to.

3. Tidying

A program I write for your spreadsheets finds duplicates, empty fields, values outside the allowed list and formats that cannot be read. You get a list of doubtful cases to settle: whether “Smith Ltd” and “SMITH LTD.” are the same customer, what to do with an order that has no date. The person who knows the data decides. The rest runs automatically.

4. Trial import

The data goes into a copy of the system that can be thrown away. You open it, click through customers and jobs, and check that everything is where it should be. Usually a few things come up to fix in the tidying. We repeat the trial import until there are no more comments.

5. Running in parallel

For a week or two the team enters new jobs into both the spreadsheet and the system. It costs some double work, but it proves the system copes with real cases, not only the ones we predicted. At the end we compare the numbers on both sides.

6. The switch-over

A final import of the current data, usually at a weekend or after hours. On Monday the team works only in the system. The spreadsheet stays on the drive as a read-only archive.

What to do and what not to do

Do not tidy the spreadsheets “in advance” before we talk. Cleaning thousands of rows by hand is weeks of work that a program does in a fraction of that time. I would rather see the data as it is.

Do not move everything. Every column that goes into the system needs a screen where it is entered and viewed. A column nobody has looked at for two years does not need a screen.

Leave the spreadsheets untouched. Until the parallel run ends, nobody deletes or edits the old files. When something does not add up, the spreadsheet shows where the number came from.

Appoint one person to decide. Someone has to settle the doubtful cases from the tidying. When several people do it in turn, the decisions contradict each other and the tidying drags on.

How long it takes

Moving the data is part of launching the first module, meaning the first self-contained part of the system, not a separate project. With a few spreadsheets and a year or two of data it usually fits in the same month in which the module is built. Prices and timelines come in writing together with the scope.

When it is worth moving from spreadsheets to a system at all is on the main page of the guide. What a system looks like for orders and jobs and for production and warehouse is on separate pages.

Describe in the form below which spreadsheets you keep and for how many years. I will get back to you with questions or a migration plan.