Clean customer data before import: duplicates, VAT, emails

Before importing customers, work in a copy of the file: give every customer a stable ID, merge duplicates, put VAT numbers in one format with their country prefix, trim and lower-case email addresses, and use one country code style. Then import a small sample before the rest.

An import copies whatever you give it. A spreadsheet with three spellings of the same company becomes a system with three customers, and fixing that afterwards takes longer than cleaning the file. An hour of tidying up front saves a week of corrections later.

The cleaning checklist

Check	What to look for	How to fix it
Stable IDs	Rows without an ID, or the same ID twice	Give each customer its own ID that never changes
Duplicates	The same company under slightly different names	Merge them into one row before the import
VAT numbers	Missing country prefix, spaces, dots	One format: prefix plus digits, no separators
Email addresses	Two addresses in one cell, stray spaces	One address per cell, trimmed, lower case
Countries	"Belgium", "BE", "Belgique" in one column	One style, ideally two-letter ISO codes
Empty rows and totals	Subtotals, notes, blank lines	Delete them; one row is one customer

Finding duplicates

Identical rows are easy to spot; near-duplicates are the ones that hurt. To catch them, add a helper column with a normalized name: lower case, punctuation removed, legal suffixes stripped. Sort by it and read down the list. Two other strong signals:

• The same VAT number on two rows almost always means the same legal entity.
• The same email domain on two rows is worth a look, though large customers can genuinely have several entities.

When you merge, keep the most complete row and copy across anything the other one has that it lacks. Note which old IDs you merged, in case an old invoice refers to one of them. If you are unsure whether two rows are the same company, ask the customer or check the registered details before you merge.

VAT numbers

An EU VAT number starts with a two-letter country prefix, followed by the national number. Store it in one format, with the prefix and without spaces or dots. A format check catches typos, but it does not prove the number is registered. For that, check it against VIES, the European Commission's lookup service; how to check an EU VAT number explains what the answers mean.

Email addresses

One address per cell, with no spaces before or after it. Lower case avoids near-duplicates such as Info@ and info@. If a customer has a general address and a personal one, keep the general one on the customer and the personal one on the contact. A format check just tells you an address could exist; how email validation works covers what a deeper check adds.

Cleaning for an import into Swiftly Workspace

In Swiftly Workspace, an admin brings customers in through Administration, then Data Import, from a CSV or XLSX file. The rows are staged, and nothing is saved until you click Commit, so you can check the result before anything reaches your customer list. A few things to know while cleaning:

• The ID column is required. Every customer row needs an external_id. Use your stable IDs from the checklist above.
• Repeated IDs are caught. Validate flags a second row in the same file that reuses an external_id, along with missing required values.
• Re-imports update. Importing a row whose external_id matches a customer you already imported from the same acquisition source updates that customer instead of creating a second one. It does not look for duplicates by name: two different IDs are two customers, which is why you merge before you import.
• Emails are checked, VAT numbers later. For a customer file, Validate Identifiers checks the email addresses, and its results never block the import. VAT numbers are not checked during a customer import; once the customers are in, Verify on each customer checks the number against VIES.
• Addresses and contacts get their own checks. An address whose country is not a recognized two-letter code is flagged, and so is a contact with a malformed email address.

The full click path is in how to import customers on Mac, and preparing a CSV for import covers the template and the most common errors.

Test with a small sample

Import five or ten customers, then open them and look: names, addresses, contacts, VAT numbers. If something is off, fix the cleaning rule in your file, not the individual records, and run the sample again. When it comes out right, import the rest. If you are moving a whole business off spreadsheets, our migration guide covers the order to do it in.

Frequently asked questions

What if two customers share one VAT number?

Usually they are the same legal entity entered twice, so merge them. Sometimes they are genuinely different branches or departments of one company that you invoice separately; then keep both, give each its own ID, and note why they share the number.

Should I remove inactive customers before importing?

Keep any customer you may still need to invoice, credit or answer questions about. Customers you have not dealt with in years can stay in an archived copy of the file instead of cluttering the new system.

What makes a good customer ID for an import?

Something short and permanent that you already use, such as the customer number from your old tool. Avoid the company name: names change, and two companies can share one.