Moving from spreadsheets to a CRM: an import plan that does not breed duplicates

Moving from spreadsheets to a CRM takes four decisions, made in this order: what to bring across, in which order to import it, which column identifies a record as “the same one”, and what should happen when a row matches a record that already exists. Get those four right and the import itself is a few minutes of mapping columns. Get them wrong and the import still succeeds, which is the problem: duplicates do not raise errors.

The damage shows up later. Two reps call the same customer. A campaign emails the same person twice. A report counts one company as three. Cleaning that up inside a live CRM takes far longer than preparing the file properly, so most of this guide is about the hour before you press import.

Decide what deserves to move

An old spreadsheet carries years of residue: test rows, people who left their company in 2021, columns someone added for a single campaign. Importing everything feels safe, but it moves the clutter into a system where it now shows up in searches, filters and reports.

A reasonable cut for most teams:

• Bring customers and prospects with any activity in the last two or three years, open deals, and the fields you actually filter or report on.
• Archive the full original file somewhere safe, untouched, so nothing is truly lost.
• Drop internal admin columns, formulas, colour-coding that carried meaning nobody wrote down, and rows with no way to contact anyone.

Colour-coding deserves a special mention. If green meant “paid” and red meant “do not call”, that information disappears in a CSV export. Turn it into a real column before you export.

Import in dependency order

Records in a CRM link to each other: a contact belongs to a company, a deal belongs to a contact and a company. If you import deals before the companies they refer to exist, the links have nothing to attach to. The safe order is:

1. Companies, matched on name or website. If your CRM cannot match on a domain, store a normalised domain or a legacy ID in its own column and match on that.
2. Contacts, with a company column that matches the company names you just imported.
3. Deals, referencing the contact and company.
4. Leads, if you keep unqualified enquiries separate from customers.
5. Tasks and notes, last, once the records they attach to exist.

If your spreadsheet has one row per person with the company in a column, split it into a companies file and a contacts file first. One sheet that mixes both is the single biggest source of duplicate companies, because “Acme Ltd”, “ACME Limited” and “Acme” all become separate records. The reasoning behind this split is in the five records a CRM actually needs.

Choose a match key and clean it

A duplicate rule is only as good as the column it matches on. For each record type, pick one key:

Most duplicates that survive an import are formatting differences in the key: “[email protected] ” with a trailing space is a different string from “[email protected]”. Ten minutes with a lower-case formula and a trim function removes a whole category of problems. Then sort by the key and look for exact repeats inside the file itself; a file that contains the same person twice will produce two records under some duplicate rules.

Pick the duplicate rule deliberately

When an imported row matches an existing record on the key, the import tool has to do something. There are three sensible behaviours, and each fits a different situation:

• Skip duplicates. The matching row is ignored and the existing record stays as it is. Use it when the CRM already holds the better data and the file is only meant to add new people.
• Update existing. The file’s values are written over the matching record. Use it for a second pass after fixing the file, or to enrich records with new columns.
• Create new. No matching at all: every row becomes a record. Use it only for a first import into an empty system with a file you have already de-duplicated.

The trap in “update existing” is the empty cell. A blank phone column in the file is a value, and overwriting with it can wipe a phone number the CRM already had. Before an update run, delete columns you do not intend to change, or fill the blanks.

Test with ten rows, then compare

Before the full import, run a file with five to ten representative rows, including the awkward ones: a name with an apostrophe, a non-Latin name, a company with two contacts, a row with an empty email. Then open each imported record and compare it to the source. Check that dates came through in the right format, that the company link resolved, that custom fields landed where you meant them to, and that accented characters are not garbled. Garbled characters almost always mean the CSV was not saved as UTF-8.

Only when the ten rows are right should you run the full file. Afterwards, read the result counts: how many were imported, skipped and failed. Skipped rows are your duplicate rule doing its job. Failed rows are the list to fix and re-import.

How the import works in Nextora

Nextora imports CSV files from the Import/Export tab of settings, or from the … menu of any list: leads, contacts, companies, deals and your own tables. The data type can be leads, contacts, companies, deals, tasks or a custom table, and the file limit is 50 MB. An .xlsx file has to be saved as CSV first. If the file came out of HubSpot, Salesforce or Pipedrive, Nextora recognises the format from the headers and fills in the data type and column mapping for you.

The import runs in four steps: the file, the column mapping, the duplicate rule, the preview. On the mapping step each of your columns is listed with a sample value from the first row beside the field it will land in; anything that could be matched already is, and columns you do not need are set to — Skip —. Required fields carry an asterisk, and a preview of the first three rows sits below. Records are created only at the last step, and every step can be reviewed and stepped back from.

The duplicate rule offers Skip duplicates, Update existing and Generate new (the “Create new” option above). For the first two you pick the key field: email, a phone number or your own customer ID. The result shows how many records were imported, skipped and failed, with the first ten errors listed by row number. Columns with no built-in field can go into custom fields, which are added from the “+” at the end of any table header and appear at once in filters and search. Export from the same menu always takes what you see, respecting active filters and search, in UTF-8 with a choice of comma, semicolon or tab separators.

Imports are one way in; ongoing data should arrive on its own. Web enquiries that create proper records are covered in forms that reach the CRM, and the CRM page shows the lists you will be importing into.

The first week after the import

An import ends when the last row is created. A migration ends when the team has stopped using the old spreadsheet, and that takes deliberate steps.

Freeze the old file. Make the spreadsheet read-only on the day the CRM goes live. If both stay editable, someone will keep updating the sheet out of habit, and within a fortnight you have two diverging sources of truth and a second migration to run.

Assign owners. Imported records often arrive with no owner or with whoever ran the import. Use bulk edit to give every open deal and active customer a real owner in the first days, before follow-ups start slipping between people.

Check the reports against the old numbers. Count customers, open deals and pipeline value in the CRM and compare them with the spreadsheet totals. A gap of a few records is usually the failed rows. A large gap means a mapping went wrong and is worth catching while the source file is still fresh in everyone’s mind.

Fix forward, not by re-importing everything. Small errors are faster to correct in the CRM itself. Re-run an import only for a whole column that came through wrong, and then with update existing on the legacy ID and only that column in the file.

The migration checklist

1. Archive the original files untouched.
2. Turn colour-coding and comments that carry meaning into real columns.
3. Split mixed sheets into companies, contacts and deals.
4. Pick a match key per type; lower-case, trim and de-duplicate it inside the file.
5. Keep old system IDs in a “Legacy ID” column.
6. Save as CSV in UTF-8.
7. Import companies, then contacts, then deals, then leads and tasks.
8. Run ten rows first and compare record by record.
9. Choose the duplicate rule per run, and remove blank columns before any update.
10. Read the result counts and fix the failed rows.

A careful migration takes a day for a typical small business. It is the one day of CRM work that pays back every day afterwards, because every report, campaign and automation built later inherits whatever quality the import left behind.