Moving from spreadsheets to a CRM without losing data

Data is usually lost in the preparation that did not happen, not in the import itself. A seven-step plan for moving from spreadsheets to a CRM.

Moving from spreadsheets to a CRM without losing data

In short

  • Data is usually lost before the import, not during it: nobody listed where it was, cleaned it, or decided where each column should go.
  • Follow seven steps: inventory, decide what to keep, clean, map, test, import, check.
  • Keep a dated, untouched copy of every original file before you change anything.
  • Switch over on one agreed date and make the old spreadsheets read-only, or you will end up running both.

What “losing data” really means

When people say a migration lost data, they usually mean one of four things:

  • It was never moved. A spreadsheet on someone’s laptop was missed.
  • It went to the wrong place. Two columns were merged, or notes were cut short.
  • It lost its meaning. Colour coding, comments and “see row 40” do not survive an import.
  • It lost its links. Contacts arrived, but no longer attached to their company or their quotes.

Step 1: find every spreadsheet

Ask each person what they use to keep track of customers, enquiries, quotes and follow-ups. Expect more than the one shared file: personal copies, old versions, a list in an email folder, contacts on a phone.

For each source, write down who owns it, what it holds, how many rows it has and when it was last updated. Then take a copy of every file, put the date in the name, and store the copies somewhere they will not be edited.

Step 2: decide what to keep

Not everything deserves to move. For each source, decide:

  • Move it. Active customers, open enquiries and quotes, recent history.
  • Archive it. Old records you may need to refer to but will not work on.
  • Leave it. Duplicates, test entries and contacts with nothing usable.

Personal data you no longer have a reason to hold should not be moved just because it exists. That is general information, not legal advice, so check current Information Commissioner’s Office guidance.

Step 3: clean before you move

Cleaning is far easier in a spreadsheet than in a CRM. Work on a copy and go through this list.

  • One row per record, one header row, no merged cells, no blank rows.
  • One piece of information per column. Split “name and phone” into two.
  • One format for dates, phone numbers and postcodes.
  • Agreed values for anything that will become a drop-down. “Won”, “won”, “WON” and “sold” should become one value.
  • Duplicates merged. Match on email address or phone number, not on name alone.
  • Anything held as colour or a comment turned into a real column, such as “Priority” or “Next action”.

Step 4: map every column to a field

Write down where each column will go. This is the single most useful document in the whole move.

Example (illustrative):

Spreadsheet columnCRM fieldField typeNote
CustomerFirst name, Last nameTextSplit into two
TelPhonePhoneOne format, restore any lost leading zero
Where fromLead sourceDrop-downReduce 14 spellings to 6 values
StatusPipeline stageStageMap old words to new stages
CommentsNote on contactLong textCheck the length limit
RepOwnerUserEach rep needs a user account first

Create any custom fields in the CRM before you import.

Decide the order as well. Companies usually go in before contacts, and contacts before deals, so each record has something to attach to. Give every row a unique reference so the links can be rebuilt.

Step 5: run a test import

Most CRMs can import a CSV file and let you match columns to fields. Before you load everything, import a small sample of around 20 records chosen to include the awkward ones: long notes, missing emails, foreign phone numbers, a company with several contacts.

Then open each one in the CRM and compare it with the original. Check that:

  • Every field arrived in the right place and nothing was cut short.
  • Dates are the right way round. A UK date read as a US date is easy to miss.
  • Phone numbers kept their leading zero.
  • Contacts are linked to the right company and deal.
  • The owner and stage are correct.

Fix the file or the mapping, delete the test records and repeat until the sample is right.

Step 6: import, then count

Pick a quiet time. Ask everyone to stop editing the spreadsheets, take a final copy, and run the full import in the agreed order.

Afterwards, count. The number of contacts, companies and open deals in the CRM should match the number of rows you meant to import. If the CRM reports skipped or failed rows, download that list and deal with each one.

Step 7: switch over properly

Set one date from which the CRM is the only place new information goes. On that date:

  • Make the old spreadsheets read-only and move them to an archive folder with limited access.
  • Agree who will fix data problems as they are found in the first month.

Running the spreadsheet and the CRM side by side for a few weeks feels safe, but it means two versions of the truth. Keep the archive only for as long as you need it.

Try this

  • Ask every team member which lists and spreadsheets they keep, and write them all down.
  • Take a dated copy of each one before anyone changes anything.
  • Mark each source as move, archive or leave.
  • Clean one spreadsheet using the checklist in step 3.
  • Write the column-to-field map for that spreadsheet.
  • Import 20 awkward records as a test and compare each with the original.

Data migration is included in our CRM implementation work, from £2,950. See Systems Integration & CRM or book a free call.

Want this fixed in your business?

Book a free 30-minute call. You’ll leave with at least one thing you can fix this month.

Find out where your week goes.

Free 30-minute call. No preparation needed. You’ll leave with at least one thing you can fix this month.