# How to Clean a CSV Before Importing It Into Your Email Tool

> A step-by-step routine for fixing duplicates, broken emails, messy names and bad encoding before a lead list goes anywhere near a campaign.

You bought, scraped or inherited a spreadsheet of leads, and now it has to go into your email tool. Open it and the problems show up fast: three emails in one cell, names in ALL CAPS, the same business listed twice, a column of phone numbers turned into 4.47E+11, and a header row that says "Unnamed: 3".

Import that as-is and you pay for it later. Merge tags print blanks or shouting names, duplicates get two emails, and dead addresses push up your [email bounce rate](https://getclient.pro/learn/email-bounce-rate), which can hurt how your sending account is treated. The fix takes about half an hour for most lists. Here is the routine.

## Step 1: Make a copy and open it safely

Never clean the original. Save a copy named something like `leads-raw.csv` and work on `leads-clean.csv`.

How you open the file matters:

- **Excel** often mangles CSVs on open. It strips leading zeros from postcodes, turns long phone numbers into scientific notation and can break accented characters. Use Data > From Text/CSV instead of double-clicking, choose UTF-8, and set phone and postcode columns to Text.
- **Google Sheets** handles UTF-8 better. File > Import, then turn off "Convert text to numbers, dates and formulas".

If names like "José" show up as "José", the file was read with the wrong encoding. Reimport as UTF-8 before doing anything else.

## Step 2: Fix the header row

Your import tool maps columns by header, so headers need to be boring and unique.

- One header row only. Delete any title rows or notes above it.
- Lowercase, no spaces or symbols: `business_name`, `first_name`, `email`, `website`, `city`, `category`.
- Delete empty columns and columns you will never use.
- No two columns with the same name.

Decide now which columns will feed personalisation. If you plan to write "Hi {{params.firstname}}" or mention {{params.city}}, those columns must exist and be filled.

## Step 3: One email per row

Scraped lists often pack several addresses into one cell, separated by commas or semicolons. Pick one per row.

1. Split the column (Sheets: Data > Split text to columns; Excel: Data > Text to Columns).
2. Keep the best address in `email`. A rule of thumb: a named person (anna@) beats a role address (info@, sales@), and a role address beats nothing.
3. If you want to contact several people at one company, duplicate the row and give each person their own row, not their own column.

Then trim the column: remove spaces, trailing dots, `mailto:` prefixes and anything in angle brackets. A formula like `=LOWER(TRIM(A2))` handles case and stray spaces.

## Step 4: Remove obviously bad addresses

Filter the email column and delete rows that are clearly unusable:

- Blank cells, or cells without an @.
- Image file names that a scraper mistook for emails, such as `logo@2x.png`.
- Placeholder addresses like `example@`, `test@`, `your@email.com`.
- Addresses on the web builder's domain instead of the business's own (often left over from templates).
- `noreply@` and `donotreply@`.

This catches formatting junk, not dead mailboxes. An address can look perfect and still bounce. That is what verification is for (Step 7).

## Step 5: Remove duplicates

Duplicates mean the same person gets your email twice, which looks careless.

- Deduplicate on `email` first. In Sheets, Data > Data cleanup > Remove duplicates; in Excel, Data > Remove Duplicates.
- Then check `website` or business name. The same business can appear with two different addresses. Decide whether you want both or just one.
- Check against people you already contacted or who unsubscribed. Keep a master suppression list and filter it out every time.

## Step 6: Clean the fields your emails will print

Anything that goes into a merge tag will appear in the email exactly as it sits in the cell. Read it like a recipient would.

| Field | Common problem | Fix |
|---|---|---|
| First name | ALL CAPS, "Dr.", full names, blanks | `=PROPER()`, strip titles, keep first word only |
| Business name | "Ltd", "LLC", SEO stuffing like "Best Plumber NYC 24/7" | Shorten to what a person would say out loud |
| City | Postcodes or full addresses | Keep the city name only |
| Category | Odd scraped labels | Rewrite into natural words ("dental clinic", not "Dentist_Service") |
| Website | Tracking parameters, http vs https mix | Strip after the domain |

For blank first names, either fill them by hand or plan a fallback greeting in your copy. Don't send "Hi ,".

Sort each column A to Z and Z to A. The weird values float to the top and bottom where you can spot them quickly.

## Step 7: Verify before you send

Formatting checks can't tell you whether a mailbox exists. Run the cleaned list through an email verifier and remove addresses marked invalid. Treat "risky" or "catch-all" results with care: you might send to them in a smaller batch after your main list, or drop them if your sending account is new.

Also check the basics of whatever rules apply to you. Many places require a working way to opt out and honest sender details in commercial email. Check the rules for your country and your recipients' countries; this isn't legal advice.

## Step 8: Save and import correctly

- Save as **CSV UTF-8**, not plain CSV or .xlsx.
- Open the saved file in a plain text editor once. You should see commas between fields and quotes around any value that contains a comma.
- Import a small test batch first, then send yourself a preview to confirm every merge tag fills.

If you'd rather skip the messy-CSV stage for local businesses, leads scraped from Google Maps arrive already in columns; see [how to scrape Google Maps without code](https://getclient.pro/blog/scrape-google-maps-without-code). New accounts get 100 free credits; details on [plans and credits](https://getclient.pro/pricing).

## A quick checklist

- Copy saved, opened as UTF-8
- One clean header row
- One email per row, trimmed and lowercase
- Junk and placeholder addresses removed
- Duplicates and past contacts removed
- Names, cities and categories read naturally
- List verified
- Saved as CSV UTF-8 and test-imported

A clean [lead list](https://getclient.pro/learn/lead-list) also makes the rest easier: your [follow-up sequence](https://getclient.pro/learn/follow-up-sequence) only works if each step reaches a real person with their name spelled right.

## Frequently asked questions

### Should I keep info@ and other role addresses?

For small local businesses, info@ is often read by the owner, so it can be worth keeping. Prefer a named address when you have one, and verify role addresses like any other.

### Can I clean a very large CSV that Excel or Sheets can't open?

Split it into smaller files first, clean each, then combine. Sheets has a cell limit and Excel has a row limit, so very large files may need splitting or a scripting tool.

### Why do my merge tags come out blank after importing?

Usually the column wasn't mapped, the header had a typo or space, or the cell was empty. Re-check the column mapping and send a preview to yourself before launching.

### How often should I re-clean a list I already used?

Re-verify before each new campaign if the list has been sitting for a while, since mailboxes close and people change jobs. Always re-apply your unsubscribe and suppression list.

Source: https://getclient.pro/blog/clean-a-csv-before-importing