CSV preparation tips before you verify a big list
A few minutes in a spreadsheet makes bulk verification smoother and cheaper. Common file problems and how to fix them.
Photo by Pexels on Pixabay
Bulk verification takes a CSV and returns it with results added. A little preparation avoids most of the problems people run into.
1. Use a real CSV
Save from your spreadsheet as CSV (UTF-8). Plain "CSV" in some versions of Excel uses a legacy encoding that garbles accented names. If your file has non-English characters, UTF-8 matters.
2. Keep a header row
The first row should name the columns. Mailsure looks for a column called something like email, e-mail or email address and picks it automatically. You can also choose the column manually when you upload.
3. One address per cell
If a cell contains several addresses separated by commas or semicolons, split them into separate rows first. Otherwise the whole cell is treated as one malformed address.
4. Trim whitespace
Leading and trailing spaces, tabs and line breaks pasted from other systems cause avoidable failures. Most spreadsheets have a trim function. Also look for invisible characters pasted from web pages.
5. Remove obvious junk
- blank rows
- headers repeated in the middle of the file
- placeholders like
none,n/aor- - test entries such as
test@test.com
Duplicates and blank rows are free on Mailsure, so you don't have to be perfect. But removing them makes your own review easier.
6. Keep your other columns
Leave names, IDs and tags in the file. The results are added as new columns next to them, which makes it easy to match results back to your CRM. If privacy is a concern, remove columns you don't need to send (privacy considerations).
7. Mind the size
Very large files are fine, but if you can, split a multi-million-row export by source so you can compare the quality of each. A per-source breakdown often shows that one acquisition channel is responsible for most of the bad addresses.
8. Back it up
Keep the original file untouched. You'll want it for comparison.
After you upload
Download the results and filter on the status column. Delete the invalid rows, segment the risky ones and keep the valid ones. The step-by-step cleaning guide covers what to do with each group, and this explainer covers what the statuses mean.
