Moving from Spreadsheets to a System: Data Migration Done Right

You have decided to replace your spreadsheets with a proper system. The software choice is made, the screens look good, and then comes the part everyone underestimates: getting years of data out of messy files and into the new system correctly. Data migration is where many projects slip, so it deserves its own plan.

Why migration goes wrong

• Spreadsheets are flexible, which means inconsistent. The same customer appears three ways.
• Important data hides in comments, colours, notes and the memory of one employee.
• Nobody cleaned the data because the sheet "worked".
• Old data uses formats the new system cannot accept.
• The migration is left to the end, when time and budget are tight.
• Nobody checks results, and errors go unnoticed until customers complain.

A little discipline prevents all of this.

Step 1: Decide what to migrate

Not everything deserves a place in the new system.

• Active data that the business needs every day: current customers, open orders, products, stock levels.
• Historical data needed for reference, reporting or legal reasons: past orders, invoices, certificates.
• Data you can leave behind: obsolete records, duplicate files and test entries.

Consider archiving old data in a read-only form rather than importing it all. Importing less makes the project faster and the new system cleaner. Check how long you are legally or commercially required to keep records, and delete personal information you no longer need; see POPIA and your business website.

Step 2: Audit the current data

List every spreadsheet and data source: files on laptops, shared drives, email attachments, WhatsApp exports and paper forms.

• Who owns each one?
• What does each column mean?
• How complete and accurate is it?
• How many records are there?
• Where do the sources overlap or conflict?

This stage often reveals surprises, such as a "master" list that is out of date, and a team member's private sheet that holds crucial information.

Step 3: Define the new data structure

Look at how the new system organises data: fields, formats, required values, relationships and validation rules. Decide how each existing column maps.

Spreadsheet column	New system field	Rule
Cust Name	Company name	Required, no trailing spaces
Tel	Phone	Standard format, e.g. +27 82 123 4567
Status	Customer status	Must be one of: Active, On hold, Closed
Notes	Notes	Plain text, keep as history

Your mapping document is the blueprint for the whole migration. If your process is not yet written down, do that first; see how to document business processes before building software.

Step 4: Clean the data

Clean before you migrate, not after.

• Remove duplicates, using names, email addresses, phone numbers or registration numbers to match.
• Standardise formats for dates, phone numbers, addresses, currency and units.
• Fix obvious errors, such as misspellings, impossible dates and negative quantities.
• Fill in missing required values, or decide on a placeholder.
• Resolve conflicts when two sources disagree. Decide which is authoritative.
• Normalise categories, so "Pretoria", "PTA" and "Pretoria East" become consistent.

Involve the people who know the data. A short clean-up session with the team who use it daily can save weeks of correction later.

Step 5: Plan the technical import

Choose how the data will go in:

• Manual entry is only realistic for small volumes.
• CSV or Excel import tools supplied by the new system suit many cases.
• Scripts or integration tools handle large or complex migrations, such as relationships between customers, orders and invoices.
• Staged imports bring in reference data first, such as customers and products, then transactions that depend on them.

Keep the original files untouched as a record, and work from copies.

Step 6: Test with a sample

Never import everything on the first attempt.

1. Import a small sample into a test copy of the new system.
2. Compare results with the source: counts, totals and random spot-checks.
3. Check relationships: do orders link to the correct customers?
4. Test reports and key workflows with the migrated data.
5. Fix problems in the mapping or the cleaning, and repeat.
6. Run a full trial import before the real one, and time it, so you know how long the cut-over will take.

Step 7: Run in parallel, briefly

For a short period, consider running the old and new processes side by side, or at least keeping the old data accessible in read-only form. This provides a safety net if something is missing and builds confidence. Do not let it drag on, because two sources of truth quickly diverge.

Step 8: Plan the cut-over

Pick a quiet time and define exactly what will happen:

• A freeze on changes in the old system.
• A final export and import.
• Verification checks with sign-off.
• A go-live announcement.
• A clear moment when the old spreadsheets become read-only.
• A rollback plan, if something critical is wrong.

Make sure everyone knows the date and what to do differently.

Step 9: Verify and fix after go-live

• Check key numbers against the old data: customer counts, outstanding balances, stock on hand.
• Ask staff to report discrepancies, and have a quick route to fix them.
• Monitor for missing or incorrect records during the first weeks.
• Archive the old files safely and retire them once confident.

Step 10: Train the team

A clean database is wasted if people keep working in the old way. Train staff in the new system, and make it clear that updates happen there only. Share quick guides, hold short practice sessions and have someone available to help in the first weeks.

Data protection during migration

Spreadsheets with customer data often sit on personal laptops and in email. Migration is an opportunity to tighten control.

• Transfer data over secure channels, not unprotected email.
• Limit who can access import files.
• Delete temporary copies once you finish.
• Make sure the new system has proper access control and backups.

See online store security and backups for backup and security principles that apply here.

Common mistakes

• Starting migration late in the project.
• Importing messy data and hoping to clean it later.
• No test imports.
• Forgetting data that lives outside the main spreadsheet.
• Not assigning an owner.
• Letting staff keep updating the old files after cut-over.
• No backup of the original data.

A simple timeline

1. Audit and decide scope.
2. Map and clean.
3. Test imports and fix.
4. Train users.
5. Cut over, verify and monitor.

Treat migration as a project in its own right, with an owner, a schedule and a checklist. Done well, your new system starts life with clean, trustworthy data, which is the whole reason for the change. If you are still at the stage of deciding whether to build or buy, return to custom software vs off-the-shelf and signs you have outgrown your spreadsheets.