How to Export a Clean Lead List to CSV (Without Breaking Your Spreadsheet)

Finding leads is the part everyone talks about. Nobody mentions the ten seconds after the export finishes, when you open the file and find that every phone number has lost its leading zero, half the business names have "| Home | Book Online" welded to the end, and there are four versions of the same plumber in Leeds.

A lead list is only worth what your spreadsheet and your CRM can actually read. A messy export costs you twice: once when you clean it by hand, and again when the bad rows reach your outreach and somebody gets contacted twice under two slightly different names.

Here is what to fix, roughly in the order it tends to bite.

Get the Encoding Right First

Almost every UK lead list contains characters that plain ASCII cannot hold — the pound sign, en dashes in business names, the occasional accented word in a restaurant or salon name. If your export is not UTF-8, those turn into mojibake, and Café becomes Café.

Exporting as UTF-8 solves most of it. The remaining problem is Excel. Excel on Windows will not assume UTF-8 for a .csv file unless the file opens with a byte order mark. Without one, it falls back to Windows-1252 and mangles precisely the characters you were trying to protect.

So the rule is: UTF-8 with a byte order mark if the file is going to be double-clicked into Excel, UTF-8 without one if it is going into a CRM importer or a script. A stray byte order mark occasionally attaches itself to your first column header, which then silently fails to map during import — worth checking if your first field is the only one that won't line up.

If you can't control the encoding, skip CSV and export XLSX instead. It stores encoding and cell types internally and cannot be misread this way.

Decide What an Empty Cell Means

This sounds trivial and causes more failed CRM imports than anything else on this list.

There are three ways to represent "we did not find an email for this business": an empty cell, the literal text N/A, or the string null. Pick one and apply it everywhere. Mixed conventions are how you end up with a CRM contact whose email address is the word "null", a mail merge that opens with "Dear N/A", and filters that quietly miss a third of your rows.

Empty is almost always the right answer. An empty cell is unambiguous, sorts predictably, and every importer on the market reads it as "no value". Text placeholders are just data wearing a disguise.

Phone Numbers Are the Most Common Casualty

UK landline and mobile numbers start with a zero, and every spreadsheet ever written will strip it the moment it decides a column looks numeric. 01132 496 000 becomes 1132496000, and it has stopped being a phone number and become an integer.

The fix is to convert numbers to E.164 format — +441132496000 — before the file reaches a spreadsheet at all. The leading plus forces text interpretation, the number is unambiguous internationally, and it is the format every serious CRM and dialler expects. This is fiddly enough to be worth its own walkthrough: see formatting UK phone numbers for your CRM.

Strip the Tracking Junk From URLs

Scraped website addresses often arrive with query strings bolted on: utm_source, utm_campaign, fbclid, gclid, session identifiers. These come from wherever the link happened to be harvested. They are not part of the business's web address.

Left in, they break two things. Deduplication stops working, because the same domain appears half a dozen times with different suffixes. And any team-mate clicking through from your CRM is attributing a fake campaign to themselves in the target's analytics.

Cut everything from the question mark onwards unless the path genuinely needs it, and normalise to a consistent form — same protocol, no trailing slash, no www inconsistency. You want one canonical string per business so that matching works later.

Trim the Junk From Business Names

Scraped business names are frequently page titles rather than names. "Bright Smile Dental Practice | Leeds | Book Online Today" is a title tag. "Bright Smile Dental Practice" is the name, and it is the only part you can safely drop into an email.

Look for the usual separators — pipes, hyphens with spaces either side, en dashes — and everything that follows them. Watch out for genuine hyphenated names, which is why this should be a reviewable step rather than a blind find-and-replace. Strip trailing location tags, "Official Site", and the ubiquitous "Home".

Dedupe Before You Export, Not After

Deduplicating inside a spreadsheet means Remove Duplicates on an exact-match column, which catches almost nothing real. The same business genuinely appears twice as "Smith & Sons Plumbing" and "Smith and Sons Plumbing Ltd", and exact matching sees two businesses.

Do it before the file is written, while you still have structured fields — a place identifier, a postcode, a normalised domain — to match on. Proper lead list deduplication is a topic in itself, but the important part is the ordering: clean, then dedupe, then export.

Getting a Clean File Out

If you would rather not do any of this by hand, this is exactly what LeadSnipe's export engine handles. It formats UK phones to E.164, strips tracking parameters from websites, trims name junk, applies a consistent empty-cell rule, and dedupes non-destructively — then hands you a CSV or XLSX that opens correctly in Excel and imports cleanly. You can start by scraping a lead list from Google Maps, or by finding email addresses for businesses you already have.