How to Clean a HubSpot Contact List Before Import

Why HubSpot is picky about your file

HubSpot reads your file row by row. It trusts the words you give it. It will not fix your mess for you.

You need to clean a HubSpot contact list with care. Each row should be one real person. Each row needs a clear way to find them.

For most lists, email is that key. HubSpot uses email to spot a contact. If the email is wrong, the match fails.

Two rows can share the same email. Then HubSpot may make two records. Or it may merge them in a bad way.

Your team then sees split notes and split tasks. One rep calls a lead twice. One lead gets two mails on one day.

Small flaws grow fast in a CRM. A space at the end looks like nothing. To a tool, that space is part of the text.

Upper case and lower case can clash too. One file says Lead with a big L. The next file says lead in small case.

HubSpot may treat them as two picks. Your counts then split in two. Your reports look wrong and vague.

Bad dates cause the same kind of harm. One row uses day, month, and year. The next row swaps month and day.

You can not tell which date is right. The tool can not tell you either. So you must set one form and keep it.

Phone fields are just as prone to drift. One row has gaps and brackets. One row has just the raw digits.

Search then fails for half your list. Calls fail for the same cause. Staff waste time as they type by hand.

None of this means the tool is harsh. It means the tool needs clean input. Clean input gives you clean output each time.

The good news is that the fix is plain. You work in a set order, step by step. You check each field with the same rules.

You do not need to be a tech pro. You just need a calm, fixed routine. This guide gives you that routine in full.

Do the work once, before you import. It costs far less than a fix later. A fix later means edits in live records.

Live edits are slow and hard to track. They can also break notes and past work. A clean file stops that risk at the start.

Think of this as prep work for a paint job. The prep looks dull but it saves the coat. Here, the prep saves your whole list.

Step 1: export the list and keep a backup

Start with the list as it is now. Export it from your old tool or sheet. Save that file with a clear name.

Add the date to the file name. Add the word raw to the name too. That tells you it is the first copy.

Now put that raw file in a safe place. Do not edit the raw file at all. Do not save over it by mistake.

Make a second copy for your work. Do all your edits in that copy. If you slip, you can start fresh.

This one habit saves teams a lot of pain. Staff can lose hours from one bad save. A backup turns that loss to mere mins.

Open the work copy and look at it. Check how many rows you have. Write that count down on paper or in notes.

Check how many fields you have too. Each field should have one clear use. If a field has no use, you can drop it.

Look for rows that are blank in full. Look for rows with just one stray word. Those rows will not help your team.

Look for test rows you made long ago. They may say test or fake in the name. They should not go into HubSpot at all.

Look for rows with no name and no email. Those rows have no way to reach the person. Flag them now and set them to one side.

Do not rush this first look at the file. A slow look now finds big flaws early. Big flaws are cheap to fix at this stage.

Ask who owns the list in your team. Ask if some rows must stay out. Some folks may have asked to opt out.

Opt out rows must not be mailed later. It is best to drop them now. Keep a note of why you dropped them.

Check if the list came from more than one source. Two sources mean two sets of rules. That is why the fields may not match.

Write down each source next to your count. This note helps when you check the results. You can trace a flaw back to its source.

Keep file names short and plain each time. Use the same name style for each export. Your team will find the right file fast.

When the backup is safe, you can move on. The next step deals with the top row. That top row steers the whole import.

Step 2: fix headers and map each field

The top row of your file holds the headers. Each header names one field in HubSpot. The import tool reads them first.

If a header is vague, the match will fail. If a header is wrong, data lands wrong. That can put a city in a name field.

Start by reading each header out loud. Ask what kind of data sits below it. If the name does not fit, change the name.

Use one plain name for each field. Do not use two names for the same thing. Do not use one name for two things.

Old lists use lots of odd header names. One sheet says E-mail with a dash. One sheet says Mail or Contact Mail.

RowSift maps these kinds of names for you. It runs in your web browser, not far away. It maps common header forms to one schema.

That saves you from hand edits on each file. It also keeps the same map each time. Your team can trust the same result.

Still, you should check the map with your eyes. A tool can guess, but you know your list. Scan each pair and nod or fix it.

Watch for fields that sound the same but differ. Work phone and home phone are not the same. First name and full name are not the same.

If two fields hold the same kind of data, pick one. Move the best values into that one field. Then drop the spare field from the file.

This table shows the kind of mess to expect. It also shows the clean form to aim for. Use it as a quick check for your file.

Keep headers short and free of stray marks. Do not add dots or quotes in a header. Do not leave a blank header in the row.

A blank header can break the whole map. The tool may skip that whole field. Or it may stop and show an error note.

Count your headers when you think you are done. The count should match your field count. If it does not, find the gap at once.

Save the file and keep the same name style. You now have a file HubSpot can read. Next, you deal with rows that repeat.

Step 3: dedupe by email

Now find rows that share the same email. Those rows point to the same person. You want just one row for each person.

Start by making all emails look the same. Trim the space at the start and end. Make each email lower case in full.

RowSift does this trim and case work for you. It will lowercase and trim each email. It also flags rows where emails repeat.

That flag list is your work list for this step. Go through it one row at a time. Pick the best row and keep that one.

What makes a row the best one to keep? Pick the row with the most full fields. Pick the row with the most new data.

Sometimes two rows each hold one good part. One row has a phone and no firm name. One row has a firm name and no phone.

In that case, merge the good parts by hand. Put them in one row with care. Then drop the spare row from the file.

You can learn more ways to remove duplicate rows in a file. That guide covers whole rows, not just emails. Use both checks for a full clean.

Watch for emails that look close but differ. One may have a typo in the name part. One may use a work mail, one a home mail.

Do not merge those rows on a guess. Ask the owner of the list first. A wrong merge is hard to undo later.

Watch for group mails that many folks share. A team inbox is not one real person. It may need to stay out of the list.

Watch for rows with no email at all. RowSift flags rows with no key field like this. You must add an email or drop the row.

A contact with no email has no firm key. HubSpot will find it hard to match it. It may make a fresh record each import.

Count your rows again when this step ends. The new count should be less than the old. Write the new count next to the old count.

The gap shows how much waste you cut. It also helps you check the import later. The import count should match this new count.

Save a fresh copy at the end of this step. Name it so you know dedupe is done. Then move on to the small field flaws.

Step 4: clean phones, dates, and stray spaces

Small flaws hide in plain sight in each row. A space here, a dash there, a wrong case. Each one can break a search or a sort.

Start with stray space in each field. Trim the start and the end of each cell. Cut double space in the middle too.

Names need the same care as emails do. Make the first letter big and the rest small. That makes lists look neat and pro.

Do not force this rule on each firm name. Some firms style their name in odd ways. Keep brand names as the firm writes them.

Phone fields need one set form for all rows. Pick that form and use it each time. RowSift helps to normalize phone strings for you.

That means it strips out gaps and brackets. It keeps the digits in a clean line. Your team can then search by phone fast.

If you want the full method, you can clean up phone numbers step by step. That page goes deep on codes and gaps. It pairs well with the steps on this page.

Check each phone has the right count of digits. Short phones are most often old or wrong. Long phones may hold two phones in one cell.

Split any cell that holds two phones in it. Put one phone in the main phone field. Put the next phone in a spare field.

Dates need the same one form rule too. Year, month, and day is a safe form. It sorts well and reads the same each time.

Do not mix short years with full years. A year like 25 can mean more than one thing. Write the full year in each date field.

Check dates that can not be real dates. Day 32 of a month is not a real date. Month 13 is not a real month at all.

Fix those rows from the raw file if you can. If you can not fix a date, leave it blank. A blank date is less bad than a wrong date.

Pick lists need one case and one spelling. Stage, status, and type fields work like this. Make a short list of the right values.

Then fix each row to match that short list. Do not leave near match values in the file. Near match values split your counts in reports.

Zip codes and codes with zeros need care too. Some tools drop a zero at the start. Check those fields as text, not as math.

When each field looks clean, save the file. You are now set for the last check. That check guards the import from new slips.

Step 5: run a preflight check, then import

A preflight check is a last look at the file. You run it just before you import. It finds flaws you may have missed.

Open the file and scan the top ten rows. Then scan ten rows from the middle. Then scan ten rows from the end.

Check that each header still maps right. Check that no blank header crept back in. Check that each field holds the right kind.

Run a count check on key fields first. Count rows with a blank email field. That count should be zero or very low.

Count rows with a blank name field too. Names help staff know who they call. A list with no names feels cold and vague.

Check for rows that repeat one more time. Sort the file by email from A to Z. Rows that share an email will sit side by side.

Check the file size and row count again. Match them to the counts you wrote down. If they do not match, stop and find why.

Test the import with a small slice first. Take ten rows and import just those rows. Check how they land in HubSpot.

Open each test record and read each field. The name should sit in the name field. The phone should sit in the phone field.

If a field lands in the wrong place, stop. Fix the map and test once more. Do not push the full file yet.

When the test looks good, import the full file. Use the same map you used for the test. Do not change the map at this point.

You can run the file through the RowSift HubSpot CSV cleaner before this step. It runs in the browser and exports a clean CSV. That gives you one last clean pass.

After the import, check the result at once. HubSpot will show how many rows it took. It will also show rows it skipped or failed.

Read each error note with care and in full. Fix the rows it names in your file. Then import just those fixed rows again.

Spot check ten live records when you are done. Pick some from the start, mid, and end. Make sure each one looks right and full.

Write down what you did for next time. Note the map, the rules, and the counts. The next import will then take far less time.

Keep the clean file and the raw file both. Store them with the date in each name. You now have a clear trail of the work.

That is the whole job, from start to end. Clean a HubSpot contact list in this order. Your team gets data they can trust each day.