Moving spreadsheet data safely is mostly planning. Freeze a copy, map each column to a proper field, fix only what will break the import, and test with a small sample. Then run the old and new systems side by side before the spreadsheet goes read-only.
Most data moves go wrong in the details, not the big steps. Dates, codes and duplicates are the usual culprits. A short plan, written before you start, catches most of them.
What should you do before moving any data?
Make a copy and stop changing the original. This copy is your safety net, so store it somewhere nobody edits it.
Then find out what depends on the sheet. A spreadsheet is often used in ways nobody wrote down.
- People. List everyone who opens or edits the file, including the person who only checks it on Fridays.
- Reports. Note every total, chart or export that someone relies on. Each one needs a home in the new system.
- Workflows. Write down what happens when a row changes, such as a quote being sent or a job being booked in.
- Data you no longer need. Decide what stays behind. Old rows and columns nobody uses do not have to travel.
The last point matters for personal data. UK guidance from the Information Commissioner’s Office says personal data must be adequate, relevant and limited to what is necessary. Its checklist includes: “We periodically review the data we hold, and delete anything we don’t need.” That is UK guidance, so check your own rules if you are elsewhere.
If you are not sure the sheet is the problem, read signs you have outgrown spreadsheets first.
How do you map spreadsheet columns to a database?
Start by asking what real things the sheet describes. Customers, jobs and orders are usually separate things, even if they share one tab today. Each real thing becomes its own table.
Then give every fact its own field. A column should hold one kind of fact, and a spreadsheet often breaks that rule in three ways.
- Merged cells. A merged heading hides which row each value belongs to. Split it so every row stands alone.
- Catch-all notes columns. If “Notes” holds a phone number, a delivery tip and a price change, that is three fields.
- Colour as meaning. If red means “overdue”, add a status field. A database cannot read cell colour.
Write the map as a simple list: old column, new table, new field. It becomes the checklist you test against later. Our note on when a spreadsheet stops being a system explains why this shift matters.
What breaks during the import?
Spreadsheet software guesses what your cells mean, and the guesses are not always right. Microsoft’s help says that when Excel opens a .csv file it “uses the current default data format settings to interpret how to import each column”. The same page says a column may be month-day-year while Excel expects year-month-day.
The same page says you may need to convert a column of numbers to text to keep leading zeros. Phone numbers and product codes are typical examples.
Silent conversion has caught out experts too. The Register reported in 2020 that scientists renamed human genes because Excel turned names into dates, so MARCH1 became MARCHF1. It also cited a 2016 study finding that about 20 per cent of more than 3,500 papers had Excel files with gene name errors. Treat that as a caution about auto-conversion, not as a small-business figure.
Watch for three things:
- Leading zeros. Codes that start with 0 lose it when read as numbers.
- Dates. Check that 03/04 means the day you think it does.
- Duplicates and mixed formats. “Smith Ltd” and “SMITH LIMITED” may be one customer entered twice.
How much to clean first is disputed. Softr’s guide argues that cleaning the whole sheet first is unnecessary, though vendor guides like it sell their own tool. Our advice is to clean only what will break the import or the new rules.
How we handle a data move
We include the move in the written scope, so it is priced and agreed before work starts. The Scoping Sprint is a good place to map your data.
| What | Includes | From |
|---|---|---|
| Scoping Sprint | Mapping your data and writing the scope and quote. The fee comes off the project. | $150₹2,100 |
| Starter Tool | A focused web app with logins and your spreadsheet data moved across, as agreed in the scope. | $750₹15,000 |
| Starter Portal | A customer login area with an admin side, if customers need to see their own records. | $1,200₹25,000 |
You tell us about your current data on the call, and we write the move into the scope with milestones. Payment is a 30% deposit, then milestone payments. You own the code and your data, and we fix problems free for 30 days after handover. See how we work and the pricing page. Our note on the software scoping phase shows what a scope contains.
How do you check the move worked?
Work through these in order, and do not switch off the spreadsheet until the last steps.
- Match the row counts. The number of records in the new system should equal the number in the sheet, table by table.
- Match the totals. Compare key sums, such as order value or hours, in both places.
- Spot-check records. Open a handful of real customers and jobs and compare every field by eye.
- Capture changes made during the move. Copy the data, then add any records created or edited in the sheet while you worked.
- Run both side by side. Use the new system for real work for a short period while the sheet stays available.
- Make the sheet read-only. Once you trust the new system, lock the old file so nobody edits it by mistake.
- Keep the archive. Store the frozen copy for as long as you need a record of the data.
If a step fails, fix the cause and repeat the checks. Do not carry on and hope.
Plan your data move
Send us a description of your spreadsheet: the tabs, roughly how many rows, who uses it and which reports matter. We will say plainly what the move involves. Request a quote or book a free 20-minute call.