Migrate from spreadsheets to a database without losing the history

The file started as a list. Then it got a second sheet, then a colour code that only one person understands, then a copy called final-v3 that somebody is definitely still using. At some point it stopped being a list and became the system your business runs on, and nobody signed off on that.

This note is about the move to a database: when it is actually justified, how to do it without throwing away the history sitting in the old file, and which parts of the spreadsheet should honestly stay where they are.

The limits are much further away than people assume

The usual argument for migrating is that the file is too big. It rarely is. Microsoft documents the hard ceiling of a worksheet as 1,048,576 rows by 16,384 columns, with 32,767 characters in a single cell. Most businesses that feel crushed by their spreadsheet are using well under one per cent of that.

So size is almost never the real reason, and if you migrate believing it was, you will build the wrong thing. The number in that documentation worth paying attention to is a different one: with the shared workbook feature enabled, 256 users can have the file open at once. Concurrency is where spreadsheets get uncomfortable long before row counts do.

What actually breaks first

In every migration we have done, the trigger was one of five things, and none of them was file size:

• Two people edited at once and one set of changes quietly won.
• A column that should only ever hold a positive number holds the text "TBC" in forty rows.
• A customer exists three times with three spellings, so no total is trustworthy.
• Somebody needs last March's version and the only copy was overwritten.
• The person who understands the formulas is on leave.

Those are integrity and history problems, not capacity problems. That distinction decides what you build, so it is worth being honest about which one you actually have before anybody writes code.

What a database gives you that a spreadsheet cannot

The short answer is rules the data cannot break. PostgreSQL's documentation makes the case plainly: data types alone are too coarse, because "a column containing a product price should probably only accept positive values. But there is no standard data type that accepts only positive numbers." That gap is exactly where a spreadsheet lets a typo through.

Constraints close it. The constraint types in PostgreSQL are check, not-null, unique, primary key, foreign key and exclusion, and the documentation is blunt about what they do: if a user attempts to store data that would violate one, an error is raised. A foreign key goes further and maintains referential integrity between two tables, so it becomes impossible to record an order against a product that does not exist.

In a spreadsheet that order just sits there looking normal. That is the whole difference, and it is worth more than any reporting feature you will be sold alongside it.

Migrating without losing the history

The failure mode here is treating the old file as a dirty input to be cleaned on the way in. Clean it too eagerly and you delete the record of what your business actually did, which is the one thing you cannot recreate later.

• Freeze a copy of the spreadsheet, untouched, and keep it somewhere permanent. It is now an archive, not a working file. Our note on a data backup strategy covers where that copy should live.
• Import the rows exactly as they are into a staging table, with no constraints applied and no corrections made. Every row lands, including the broken ones.
• Add a column recording the source row number and the import date, so any figure in the new system can be traced back to the line it came from.
• Apply your constraints to a second, clean table, and move rows across in batches. Rows that fail land in a rejects list with the reason attached.
• Work the rejects list by hand with somebody who knows the business. This is the only step that cannot be automated and the only one that produces real answers.
• Keep the staging table after go-live. It costs almost nothing and it is your evidence when a number is questioned six months from now.

The rejects list is usually the most useful artefact of the whole project. It is a precise inventory of the ways the old process let bad data in, and fixing the process is often worth more than the migration itself. Some of the same discipline applies to the design of the target tables, which we set out in database design for beginners.

What to keep in the spreadsheet

A migration that moves everything is usually a migration that gets abandoned. Spreadsheets are genuinely excellent at some things and you should keep using them for those.

• One-off analysis. A question you will ask once does not need a screen built for it.
• Modelling and what-if work, where the whole point is changing assumptions quickly.
• Anything a person needs to hand to an accountant, an auditor or a bank.
• Small reference lists that change twice a year and nobody argues about.

The rule we use: if the data is the record of something that happened, it belongs in the database. If it is a calculation about what might happen, leave it in the spreadsheet. That line holds up better than any rule about row counts, and it keeps the build small. The wider version of this argument, including when a full application is justified at all, is in Excel to web app migration.

When not to do this yet

If one person maintains the file, nobody else edits it, and you have never had a disputed number, you do not have a database problem. You have a backup problem, and it is much cheaper to solve. Fix that first with a proper backup strategy and revisit this in a year.

If the process itself is still changing every few weeks, wait. Constraints encode decisions, and encoding a decision you are about to reverse is how migrations turn expensive. A no-code tool is often the right holding position while the process settles, and we have written about where no code stops so you can see the ceiling before you hit it.

And if the real complaint is that reporting takes too long, the database may not be the fix at all. Sales teams in particular tend to find that the underlying issue is record hygiene rather than storage, which bdg-labs sets out well in six rules for keeping a sales list clean. A tidy spreadsheet beats a messy database, and it costs nothing.