Migrate Your Customer List From Excel: A Step-by-Step Guide

To migrate a customer list from Excel into a field service system, do it in five passes: export one clean sheet per entity (customers, addresses, price list, suppliers), delete every column you have not used in 12 months, de-duplicate on a normalised phone number and postcode before you upload anything, import a 20-row test batch first, then verify the full import row by row against your top 30 customers by revenue. For a firm with a few hundred customers this is an afternoon, not a project. The part that takes the afternoon is the cleaning. The upload itself takes about four minutes.

Why the cleaning is the whole job

Spreadsheet import and export is now standard in field systems, so the mechanics are boring: map a column, press a button. What is not boring is that a spreadsheet has no rules. Nothing stopped you typing 070-123 45 67 in one row and +46701234567 in the next. Nothing stopped a customer existing three times because they moved, married, or rang you from a different phone.

Import that as-is and you have not migrated a customer list. You have moved the mess into a system that will now enforce it, put it on your engineers' phones, and print it on invoices. So clean it in Excel, where you have filters and formulas and no consequences. Import once, clean.

What to bring across

Bring the data that appears on a job, a quote, or an invoice. That is a shorter list than you think.

Split addresses into columns before you export

The single most common blocker: one column called Address containing Storgatan 12, 3 tr, 411 38 Göteborg. Every import tool wants postcode and town separately, because that is what the map, the route, and the invoice need.

Split it in Excel with Text to Columns, or lift the postcode with a formula, and eyeball the results. Swedish postcodes are five digits, usually written as three plus two. UK postcodes are alphanumeric and Text to Columns will mangle a few. Sort by postcode length afterwards; anything that is not the right length is broken and sitting at the top or bottom of the sort.

What to deliberately leave behind

A migration is the only free chance you will ever get to throw things away. Take it.

• Dead customers. No job, no quote, no contact in three years. They are not customers, they are 400 rows of noise in a search box. Keep the sheet in cold storage; do not import.
• Colour coding. Yellow means something to you and nothing to a database. Before you export, convert every colour rule into an actual text column (for example status = slow payer), then drop it or keep it as a tag. Do not assume you will remember what green meant.
• Historic job columns. Sheets grow columns like Job 2023-11, Job 2024-03. Do not force these into a customer record. Job history belongs to jobs. If you need it, keep the old file and move on.
• Free-text money. Columns like owes me approx 4 000. That is an accounting question, not a customer field. Settle it in your ledger, not in a notes box.
• Personal ID numbers you do not have a reason to hold. See below.

Org numbers, VAT numbers and personal ID numbers

Business identifiers are easy: bring the company registration number and VAT number, and normalise the format (Sweden: ten digits, NNNNNN-NNNN; the VAT number is the same digits prefixed with SE and suffixed 01). Strip spaces. Check the length. A missing VAT number surfaces later as a rejected e-invoice, which is a worse day than fixing it now.

Personal identity numbers are different, and this is where a lot of firms get careless. In Sweden you need a customer's personnummer to file a ROT claim with Skatteverket — but you need it for that claim, at the point you make it. You do not need it sitting in a spreadsheet column for 300 people who might one day want a ROT job.

Under GDPR, holding it requires a purpose. If the purpose has expired, so has your right to keep it. The Swedish regulator, IMY, is explicit that personal identity numbers may only be processed when clearly justified. The practical rule: do not bulk-import personnummer. Collect it in the job flow when a ROT deduction is actually being applied, encrypted at rest, and let it live against that job. If your old sheet has a personnummer column, that is a reason to delete the column, not to migrate it. (Outside Sweden, the same logic applies to national insurance numbers, IRD numbers, or anything similar.)

For the ROT rules themselves — 30% of the labour cost, with an annual per-person ceiling shared with RUT — check the current amounts at Skatteverket rather than trusting a number in a blog post.

Clean the duplicates before you import

Do this in the spreadsheet, in four passes, in this order. Each pass gets narrower.

1. Normalise the keys. Add three helper columns. Phone: strip spaces, hyphens and brackets, drop the leading zero, force one country format. Email: lowercase, trim. Postcode: strip spaces, uppercase. Do not clean by hand. Formulas do not get bored at row 200.
2. Exact matches on phone. Sort by the normalised phone column and use conditional formatting to highlight duplicate values. This catches the honest duplicates: the same person entered twice with a typo in the name.
3. Match on postcode plus house number. This catches the sneaky ones: the same address held under a husband's name and a wife's name. Usually you keep both people and merge the address, not the other way round.
4. Read the near-misses. Sort alphabetically by surname and scroll. Andersson, K and Karin Andersson on the same street are one customer. No formula finds this; ten minutes of your eyes does.

When you merge, keep the record with the most recent job and copy the useful fields onto it. Add a column called merged_from holding the old row as text, so a bad merge is visible rather than lost. Then filter for blank email and blank phone: a customer you cannot contact is not worth importing.

The import itself, step by step

Order matters, because later imports reference earlier ones.

1. Suppliers first. Small list, low risk, and your price list may reference them.
2. Price list second. Import cost and sell price, not just sell. Without cost price, job margin is a guess forever. If your wholesaler ships a price file, import that rather than typing it — most field systems, OdinTask included, take supplier price files directly and keep them updated, which is a better answer than a one-off spreadsheet.
3. A 20-row customer test batch. Pick your 20 weirdest customers: the housing association, the one with an apostrophe in the name, the one with a site address in a different town, the one with no email. Import them. Look at what happened. Fix the mapping. This is the step people skip and then regret.
4. The full customer import. Once the 20 look right, the other 380 will too.
5. Addresses last, if your system holds them separately.

Keep the exact file you uploaded, timestamped, in a folder called migration. If something looks wrong in week three, you will want to know what you actually sent.

How to verify the import row by row

Do not verify by scrolling and feeling good. Verify with counts and samples.

• Count first. Rows in your cleaned sheet minus header, versus records in the system. A mismatch is never random. It is either silently skipped blanks or a de-dupe rule you did not know about.
• Check your top 30 by revenue, individually. These are the ones where an error costs real money. Open each record and compare against the sheet: name, phone, invoice email, VAT number, address, terms. Twenty minutes, and it covers most of your turnover.
• Check ten at random. Random beats chosen. Use a random-number formula and check whatever comes up.
• Spot-check the price list on both ends. Sort by sell price, check the most expensive article and the cheapest. Decimal-comma errors hide at the extremes: 1 250,00 imported as 125000 is obvious at the top of the sort and invisible in the middle.
• Check VAT rates as a group. Filter the article list by VAT rate. If you expect one rate and see three, a default got applied somewhere.
• Then run one real job. Create a quote for a real customer, from the imported price list, to the imported address, and read the PDF. Everything wrong with the import shows up on that page.

Then close the spreadsheet

The migration is not finished when the data is in. It is finished when the old file cannot be edited. Move it to an archive folder, mark it read-only, and tell whoever else touched it that it is dead.

A firm running two customer lists is worse off than a firm running one bad one, because now nobody knows which is true. Pick the new one. Fix the rest as you go — records get corrected fastest by the engineer standing in the customer's hallway, not by an admin day you will never schedule.

If you want somewhere to import to, OdinTask takes customers, price-list articles and suppliers straight from a spreadsheet, re-runs as an upsert so a second import corrects rather than duplicates, and gives you a 14-day trial to test the migration before you commit. Start a free trial and try it with your 20 weirdest customers first.