Lead import wizard: mapping fields from a spreadsheet to your CRM

Tuesday morning, 9:40. The marketing team is back from a trade show and drops a 1,240-row spreadsheet on the sales manager's desk: "We can start calling these today." The file has 31 columns. Three of them look alike: "Phone", "Phone 2", "Contact". The city column holds "New York", "new york", "NYC" and "NY" side by side. The first row isn't a header at all — it's a merged cell with the event name in it. The manager uploads the file, ten seconds later 1,240 new records appear, and the next morning three reps call the same company. Two of them hear "someone from your team already rang."

This article is about what should happen during those ten seconds. We start with why a spreadsheet cannot enter a CRM as-is, then cover the cleanup that belongs in the file before you upload anything. After that we walk through every decision on the field mapping screen: phone and date formats, the duplicate rule, custom fields and tags. The last sections deal with the test import, the first 24 hours after loading, how to roll back a bad import, and how to turn a monthly list into a repeatable routine.

Why can't a spreadsheet go into a CRM as it is?

A spreadsheet is cell-based; a CRM is field-based. That single difference explains most of what goes wrong during an import. In Excel, a cell under "Phone" is just text — you can type "(415) 555-0142 ext. 4" into it and nothing objects. In a CRM the same value is an identifier: duplicate detection, click-to-call, SMS delivery and reporting all assume that field holds one consistent value. A value that looks perfectly readable as text can be unusable as a field.

The second difference is information density. Spreadsheet users save space by packing two facts into one cell: "Dana Reeves - Head of Procurement" or "Austin / TX". In a CRM these are separate fields, and they are separate for practical reasons rather than tidiness. You cannot segment by job title, you cannot route by state, and if the title sits inside the name field your next email opens with "Hi Dana Reeves - Head of Procurement".

The third difference is the idea of a required value. A blank cell in Excel is nobody's problem. In a CRM some fields have to be filled for the record to do any work: a lead with no owner appears on nobody's list, and a lead with no source cannot answer the question of which channel actually pays. Import a file full of blanks and your record count goes up while your follow-up capacity stays exactly where it was. We looked at where spreadsheet-based tracking breaks down in the comparison of running sales on a spreadsheet versus a CRM; here we are looking at that same breakdown at the moment of import.

What has to be fixed in the file before you upload it?

Import quality is decided before the wizard ever opens. Fifteen minutes of cleanup in the file removes hours of correction afterwards. The checklist is short:

• One header row. Delete decorative rows at the top — event names, dates, logos. The header must be row one and every column heading must be unique.
• No merged cells. A merged cell collapses into a single row on import and leaves the rows beneath it empty. Unmerge, then copy the value down.
• No subtotals or blank rows. A row reading "Total: 412" will land in your CRM as a lead named Total.
• Colour is not data. If yellow rows mean "call first", write that into a column. Fill colour is never imported.
• Merge the tabs. If three tabs hold three events, combine them into one sheet and add a "Source" column recording where each row came from.
• Trim the whitespace. " Boston" and "Boston" are two different values and they will split your segments in half.

All of this is a few minutes of work with tools the spreadsheet already has: text-to-columns, TRIM, remove duplicates and a filter. If the import is part of a larger move rather than a one-off list, plan the file preparation as a scheduled task instead of an improvisation — the one-week migration guide shows which day this preparation belongs on.

What do you actually decide on the field mapping screen?

When the file uploads, the wizard reads your column headings and proposes a CRM field for each one. Rocketly's Import Wizard makes that proposal automatically, but you have the final word: each row shows your column on the left and the target field on the right, and you can override it. The job here is not to approve the automatic match — it is to catch the places where the automatic match guessed wrong. The usual failure point is similarly named columns: a heading like "Contact" can mean a phone number or an email address, and the wizard has to infer it from the content.

The second decision covers unmatched columns. Not every column in your file needs a home. You have three options: ignore the column, bind it to a new custom field, or turn its contents into a tag. The right choice depends on how many distinct values the column holds, which we unpack in a moment. The underlying logic is the same in any job that moves data between two systems; the piece on field mapping shows how the same reasoning is applied in integrations.

Spreadsheet column	Target field	Expected format	If it doesn't match
Phone	Phone	Country code + number, no spaces	Row is held back, reformatted and re-uploaded
Email	Email	One address per cell	Second address moves to a custom field
Company	Company	Free text	Falls back to the contact name
City	City	One spelling, no abbreviations	Spellings merged, codes expanded
Event / Source	Source	Value from a fixed list	Converted to a tag if not on the list
Notes	Note	Free text	Written to the note without truncation

Why do phone, email and date fields cause the most trouble?

Most rejected rows fail on one of these three. Phone comes first. One file can contain "(415) 555-0142", "+1 415 555 0142", "4155550142" and "415.555.0142" at the same time; they are the same number but four different strings. Duplicate detection runs on the phone field, so a file uploaded without normalising the format opens the same person as four records. Convert to one shape before uploading: country code, then digits, no brackets and no spaces. Extensions do not belong in the phone field — give them a column of their own or push them into the note.

Email has two traps. The first is two addresses in one cell: "[email protected]; [email protected]". The CRM expects a single address, so the second one is lost or the whole value is rejected. The second trap is the free mailbox domain. Records arriving with a consumer email address are not wrong, but they behave differently: the Lead Enrichment feature matches the company from the email domain, so records with a corporate domain fill in industry, location and website on their own, while records on a free provider leave those fields empty. Knowing this in advance saves you the later question of why half the imported list has no company data.

Dates fail quietly. Day/month order differs by market, and 03/04/2026 reads as 3 April in London and 4 March in Chicago. Above the twelfth of the month the error becomes visible; below it the record simply goes in with the wrong date and nobody notices. Excel storing dates as serial numbers is the second hazard — break the cell format and "45812" shows up on screen. Lock date columns to text before you upload. These dates are what later builds the tracking timeline for each lead; get them wrong and the count of how long a record has been waiting is wrong too.

What happens when the same record arrives twice: Skip, Update or Create New

Trade show lists and web form submissions overlap constantly. When the wizard finds the same phone number already in the system, it offers three options, and the choice applies to the whole file. Skip leaves the existing record untouched and drops the row. Update overwrites the existing record's fields with the values from the file. Create New opens a separate record despite the match.

Which one is right depends on the age of your file. If the file is newer than what sits in the system — last week's event, for instance — Update makes sense, because titles and numbers change. If the file is an old archive, Skip is safer: writing months-old information over a live record deletes whatever the rep entered since. Create New is only defensible when the same number genuinely belongs to two people, such as two contacts sharing a switchboard line.

For the near-duplicates that accumulate after the import there is a separate route. Duplicate Detection and Merge scans for duplicate groups based on phone and email; you choose which record survives as the master and the rest merge into it in a single operation. That is how you correct a decision you made during the import. The wider discipline of moving data in both directions is covered in the guide to safe import and export.

Should leftover columns become custom fields or tags?

Every file has columns that do not fit the standard CRM schema: "Square Footage", "Vehicle Age", "Grade Level", "Budget Band", "Visited the Booth". Dumping them all into the note field is easy, but a note cannot be filtered, cannot become a segment and never shows up in a report. The right call depends on how many distinct values the column holds.

If the column contains a handful of repeating values, a tag is the better carrier. Markers such as "visited booth", "took a brochure" or "asked for a demo" make bulk filtering trivial once they are tags. If the column carries a number or a value that differs on every row, you need a field: Custom Fields and Tags lets you define fields in your own vocabulary, and those fields can then be used as segment criteria. Genuinely free-form text that never repeats — "came with a partner, deciding in October" — belongs in the note.

Making this three-way split before the import is far cheaper than making it afterwards, because creating a field later means re-importing the file to populate it across 1,240 records. The reasoning behind designing the schema up front sits in the article on custom fields, stages and views.

Why run a 50-row test import first?

Copy the first 50 rows into a separate file and import that one first. This step changes the economics of the whole job: fixing a mis-mapped field across 50 records takes five minutes, fixing it across 1,240 takes half a day. Do not take the rows at random — pull some from the top, the middle and the end, because formatting damage in spreadsheets tends to cluster in blocks.

What to check after the test import is specific. Open five records at random and confirm the phone is in a dialable shape, the name field holds no job title, and the city matches a defined value. Verify the source field is populated — if it is empty your campaign reports cannot tell you how many customers came out of which list. Test the date columns with a known example: a row reading 25/01/2026 in the file should read 25 January in the CRM, not 1 January.

Finally, look at the quality of the record itself: does the data you imported let anyone decide whether this lead is worth pursuing? If budget, need and decision-maker are missing from the file, the record enters the system but stalls at the first question of the qualification process. When that happens, asking the team who sent the file is faster than filling the gaps record by record.

What do you do in the first 24 hours after the import?

An imported list that nobody owns is no more useful than the spreadsheet it came from. Distribution comes first. Automatic Assignment distributes unassigned new leads by rule: round-robin order, rule priority and a per-person capacity limit work together. The capacity limit matters most in bulk imports, because an uncapped round-robin splits 1,240 records evenly across six reps and buries every one of them under two hundred open leads. For combining territory, source and product conditions, the guide to assignment rules works through the setup step by step.

Ranking comes second. Imported records are scored on the 0-100 Lead Score, combining demographic, behavioural, engagement, intent and financial signals; above 80 counts as hot and above 50 as warm. In a bulk import the first calculation rests only on what the file contained, so the starting scores cluster together. The Score Breakdown screen shows why a score is what it is, listing the contribution of email, phone, location, budget and the other factors one by one. Reading that breakdown is the fastest way to spot a missing column: if the same factor contributes zero across every record, that column either arrived empty or was mapped to the wrong field. How the score is built is covered in the article on lead scoring.

Timing comes third. On sources like an event list the value of first contact decays quickly, so the deadline for the first round of calls should be written down rather than assumed. The Response Time Dashboard measures time-to-first-response for every lead and groups it into tiers, and records with no response at all sit in their own group — which means the hundred untouched rows from a bulk import cannot quietly sink to the bottom of the list.

Is there a way back if the import goes wrong?

There is, on two conditions. The first is leaving a marker before you import: add a "Source" column and write the same value into every row — the event name and date, for example. Afterwards you can filter on that value and use Bulk Actions to select hundreds of records at once and tag, reassign, restage or delete them in one pass. Without a marker there is no easy way to separate the bad rows from the good ones.

The second condition is the restore window. A deleted lead does not vanish; it lands in Trash and Restore and can be recovered with its full history for 30 days by default, after which it is permanently removed. That makes it safe to delete a botched import, fix the file and load it again. The better path, of course, is never needing the rollback — which is exactly what the test import buys you.

How do you turn a monthly list into a repeatable routine?

A one-off import is a task; a list that arrives every month is a process, and processes run on templates. Give the team that sends you the file an empty template: fixed column headings, fixed order, and the expected format written into each heading. The mapping screen then looks identical every month and the only live decision left is the duplicate rule. Always include "Source" and "Date" columns in that template; six months later those two are what let you measure which list was worth the effort.

If the imported records also have to reach other systems, the same discipline applies downstream. The Lead Sync Console sends leads to connected target systems, asks you to set the field mapping yourself, lets you filter by source channel and shows the status of every delivery in one list; failed deliveries can be retried individually or in bulk. In other words, the mapping logic you learn during an import repeats itself on the way out of the CRM as well.

Finally, do not write off old imports. An event list uploaded a year ago and never called can be worth a second pass with the right message; the methods for warming cold leads back up were written for archives exactly like that.

If you want to try the flow on your own file, open a free account and run a small list through the import wizard end to end.

FAQ

Should I upload CSV or Excel?

Both are accepted. If your file contains formulas, several tabs or heavy formatting, converting to CSV is safer because CSV carries values only. When you convert, choose UTF-8 encoding, otherwise accented characters break.

Why were some rows skipped during the import?

The usual causes are an empty required field or a phone number the parser could not read. Skipped rows are listed in the import report; fix them in the file and re-upload a small file containing only those rows.

What happens if I upload the same file twice?

With Skip as the duplicate rule, the second upload creates nothing. With Update, existing records are refreshed from the file. With Create New, your record count doubles and you will need duplicate detection and a merge to clean it up.

How many rows can I import at once?

In practice the limit is set by file quality rather than row count. Splitting very large files into blocks of ten thousand makes both verification and rollback easier, and giving each block its own source value means one filter tells you which block caused a problem.