Article
How to Keep Custom Fields When Verifying an Email List
By Unlimited Verifier Team ·

Summary
To keep custom fields when verifying an email list, add a stable ID to every row, upload the file, then join the results back on a trimmed, lowercased email instead of row order. The verifier returns only the address and its status, so your other columns survive only if you reconcile the output before re-importing.To keep custom fields when verifying an email list, give every row a stable ID, upload the file, then join the results back on a trimmed, lowercased email rather than on row order. The verifier returns only an address and a status, so your other columns survive only if you keep a join key and reconcile the output before re-importing.
Quick answer
- Add a stable ID before upload. Use your CRM customer ID, or generate one and paste it as values so a later sort can't change it.
- Join on a normalised email. The verifier returns addresses and statuses, not your row IDs, so match on
LOWER(TRIM(email)). - Verify each unique address once, copy its status to every row that shares it, and confirm the output row count equals the input.
- Respect the caps. Free lists hold up to 150 emails, Pro lists up to 25,000, and each upload is limited to 10MB.
- Download results the same day. Free results are kept for 24 hours and Pro results for 30 days.
This guide covers where fields get lost, a method comparison, an eight-step process, an Excel tutorial and a worked example. For the wider process, see the bulk email list verification workflow.
What happens to custom fields during email verification?
The verifier reads your email column, checks each address and assigns a status. Unlimited Verifier detects the email column automatically in CSV and Excel uploads, checks syntax, domain, MX records and the SMTP mailbox, and sorts results into Valid, Risky or Invalid. Its output is about the address, not your record. First name, plan tier, country and consent source are your data.
Don't assume your extra columns come back in the export. Test with a 20-row file that includes a custom column, export it and compare the headers. If they don't return, rebuild the link yourself using the method below. Either way, your master file is the record you should trust.
Bottom line: The status is what the verifier returns. Your custom fields survive only if you keep a join key and reconcile the file afterwards.
Why do custom fields get lost or corrupted?
Data loss usually traces back to one of seven mistakes, and each is avoidable.
- Uploading an email-only file. Stripping columns "to keep the file small" removes the record context you need for the rejoin.
- Matching by row order. Sorting either file in Excel silently breaks alignment. Pasting results beside the original is the classic error.
- Case and whitespace mismatches.
Jane@Acme.comwith a trailing space won't matchjane@acme.com. RFC 5321 allows the local part to be case-sensitive, but most mailbox providers treat addresses case-insensitively in practice. The sources disagree, so lowercase and trim both sides for matching. - Excel mangling numbers. Excel keeps 15 significant digits. A 16-digit customer ID has its last digits replaced with zeros once saved, and that loss can't be undone from the file. Leading zeros in postcodes and phone numbers also disappear, and dates get reinterpreted by locale. Import these columns as text.
- Broken quoting. RFC 4180 requires fields containing commas, double quotes or line breaks to be wrapped in double quotes. An unquoted
Acme, Inc.shifts every column to its right. - Encoding damage. Saving a UTF-8 file as ANSI turns "José" into garbled characters. Choose CSV UTF-8 when saving from Excel.
- Duplicate addresses. One address attached to two records causes a naive join to duplicate rows or attach the wrong status.
Bottom line: Most losses come from a missing key, a sort, or a file format setting. Fix those three and the rest falls into place.
Which method keeps your custom fields?
The right method depends on list size, whether you must preserve CRM IDs, and how often you clean. This table compares the main options.
| Method | What you upload | What you rejoin with | Works on Free? | Main risk |
|---|---|---|---|---|
| Email-only file | Email column only | Normalised email | Yes, within 150 emails a list | Every other field must be rebuilt from the master |
| Full file with a stable ID | All columns plus a stable ID | Normalised email, with the ID to spot duplicates | Yes, within 150 emails a list | Sorting, case mismatches, Excel formatting |
| Split full file, verify parts, rejoin | Each part with the header row | Normalised email across all parts | No; Pro needed above 150 emails | Missing a part or dropping the header |
| API, store results against your IDs | Each email, sent from your system | Your own record ID | No; API access is Pro-only | Needs development time, and your database holds the join |
For most teams, the full file with a normalised email join is the most practical option. Use the email verification API when your system already stores the record and verification should run continuously. For background, see email verification API and automation and real-time email validation.
Bottom line: Upload the full file, join on normalised email, and use the ID to catch duplicates. Move to the API only when verification needs to run without manual steps.
How do you keep every custom field through verification?
Follow these eight steps in order. Steps 2 and 4 do most of the protective work.
- Freeze the source. Save the export as a dated master, such as
contacts_master_2024-06-03.csv. Never verify the only copy. - Add a stable ID. Use the CRM customer ID if one exists. If not, generate IDs and paste them as values before any sorting. A row number is stable only while the file stays unsorted.
- Create a normalised join key. Add an
email_keycolumn containing the trimmed, lowercased address. - Deduplicate for verification only. Upload each unique
email_keyonce, and keep a map of which IDs share each address so you can fan results back out. - Split if needed. Pro lists cap at 25,000 emails, and an upload cannot exceed 10MB, so wide files may need more parts. Repeat the header row in every part.
- Verify and download the same day. Free results last 24 hours and Pro results last 30 days. Save your own copy.
- Rejoin and reconcile. Match
email_keyto the results and copy the status to every matching row. Confirm the output has the same number of rows as the input and no blank status. - Save the joined file and route by status. Keep the custom fields and consent columns so each segment stays usable.
Bottom line: The unique ID tells you which record is which. The normalised email is what the verifier can match on. You need both.
How do you join verification results back to your original data in Excel?
This tutorial assumes the email is in column B of your master sheet. It also assumes the results file has the email in column A and the status in column B. Adjust the column letters to match your export.
- Export the master CSV with
customer_id,emailand your custom columns. Save it as CSV UTF-8. - Open it with the import option. In Excel, go to Data, then Get Data, then From Text/CSV. In the preview, set
customer_idand any postcode or phone columns to Text, so long numbers and leading zeros survive. - Add the join key. In a new column F called
email_key, enter=LOWER(TRIM(B2))and fill it down. - Create the upload list. Copy the
email_keycolumn to a new sheet, remove duplicates there, and leave the master untouched. Save the new sheet as a separate CSV. - Split if it's over 25,000 rows. For a 56,400-row list, copy rows 2 to 25,001 into a new sheet, add the header, and save. Repeat for the rest.
- Upload and verify. Use Upload and verify a list. The email column is detected automatically.
- Export the results as CSV or Excel. Check the column names, because the layout may differ from the assumption above. How to read an email verification results file explains the fields.
- Pull the status into the master. In column G of the master sheet, enter
=IFERROR(INDEX(Results!$B:$B, MATCH(F2, Results!$A:$A, 0)), "NOT RETURNED")and fill it down. If your Excel version supports it,=XLOOKUP(F2, Results!$A:$A, Results!$B:$B, "NOT RETURNED")does the same job. If the results sheet has mixed-case or padded addresses, add a helper column in it with=LOWER(TRIM(A2))and match against that. - Check the reconciliation. Filter column G for "NOT RETURNED" and expect zero rows. Confirm the master row count still matches the original export.
- Filter and save segments. Filter by status and save Valid, Risky and Invalid as separate files. The full method is in how to segment a list by verification status.
Step 8 is where duplicates are handled. Because every master row carries the same email_key as its address, each duplicate row picks up the single status for that address automatically.
Bottom line: Normalise the key, match on it, and count rows at the end. A "NOT RETURNED" count of zero is your proof the rejoin worked.
What does a 58,000-row CRM export look like after verification?
This worked example uses a hypothetical SaaS export. The arithmetic and plan limits are real. The status counts are illustrative. For a broader look at how these workflows fit SaaS teams, see email verification for ecommerce and SaaS.
The file: 58,000 rows with customer_id, email, first_name, company, plan_tier, signup_date, country and consent_source.
Step 1: Deduplicate. 1,600 rows are repeat copies of an address already in the file, so the list holds 58,000 − 1,600 = 56,400 unique addresses. Verifying each address once avoids paying quota twice.
Step 2: Check the Free plan. Free allows 25 lists of up to 150 emails each per month, which is 25 × 150 = 3,750 addresses a month. Fifteen monthly cycles cover 56,250 addresses, which is short of 56,400, so Free would need 16 cycles. It doesn't fit this job.
Step 3: Split for Pro. Pro allows unlimited lists of up to 25,000 emails each. 25,000 + 25,000 + 6,400 = 56,400, so three uploads. Check each file against the 10MB limit too.
Step 4: Plan deep verification. Pro includes 25,000 deep verifications a month. Running deep checks on all 56,400 addresses takes 56,400 ÷ 25,000 = 2.26, so three monthly cycles. Prioritise deep checks on the riskiest segment first, such as imported or long-dormant records. For the cost side, see email verification pricing.
Step 5: Calculate the cost. One Pro month at $29 covers the three uploads. Billed annually, the same plan is $25 a month.
Step 6: Illustrative results (unique addresses).
| Status | Unique addresses | Share |
|---|---|---|
| Valid | 47,000 | 83.3% |
| Risky | 6,800 | 12.1% |
| Invalid | 2,600 | 4.6% |
| Total | 56,400 | 100% |
Step 7: Fan results back out to 58,000 rows. Assume 900 of the 1,600 duplicate rows point to Valid addresses, 400 to Risky and 300 to Invalid. The joined file then reconciles like this:
| Status | Rows in joined file | Share of 58,000 |
|---|---|---|
| Valid | 47,000 + 900 = 47,900 | 82.6% |
| Risky | 6,800 + 400 = 7,200 | 12.4% |
| Invalid | 2,600 + 300 = 2,900 | 5.0% |
| Total | 58,000 | 100% |
The total matches the original row count, so every record has a status and nothing was dropped. The 2,900 Invalid rows go to your ESP's suppression list. For next steps, see what to do with invalid emails after list cleaning.
Bottom line: A 58,000-row CRM export becomes three uploads, one Pro month, and a joined file that keeps every custom field and reconciles to the original count.
How do you re-import the cleaned list without losing fields?
Unlimited Verifier has no native ESP, CRM or Zapier integrations, so re-importing is a file step. Export the joined Valid segment and import it into your ESP, mapping customer_id, first_name, plan_tier and consent_source to the matching fields.
Check your ESP's duplicate handling before importing. Some platforms match contacts on email and update existing records, and some overwrite populated fields with blank cells from the file. Test with a small segment first, and don't import a file in which custom columns are empty for part of the rows.
Keep Invalid addresses suppressed rather than deleted, because a deleted address can return in a later CSV import. Send Risky addresses to a separate segment, following what to do with unknown emails after verification. Catch-all domains accept every address, so a Risky status there doesn't confirm a mailbox. Catch-all email verification explains how those results are handled. The step-by-step version of the import is in how to re-import a cleaned list into my ESP.
Consent fields matter as much as validity. Verification shows an address can receive mail, not that the person opted in. See email verification compliance and hygiene. For the US baseline, read the FTC's CAN-SPAM compliance guide.
Bottom line: Re-import the joined segment, not a bare email list, and keep Invalid addresses suppressed rather than deleted.
How long are verification results kept?
On Free, results are kept for 24 hours. On Pro, they're kept for 30 days. Plan your rejoin around those windows. A Free results file you don't download the same day may be gone before you reattach it, so download immediately.
Pro's 30-day window is long enough to split, verify and rejoin a multi-part job over a week. Your own saved copy is still the audit trail, because re-verifying spends quota you may need for later cleans.
Bottom line: Download results the day they finish. Your saved copy, not the platform, is your record.
When should you use the API instead of a file?
Use the API when your system needs to keep its own record ID with each result. You send each email from your database, store the result against that ID and skip the spreadsheet join. Unlimited Verifier's email verification API supports single-email checks and bulk batches of up to 10,000 emails per request. Pro includes 100,000 API emails a month. API access isn't included on Free.
For the 56,400 unique addresses in the worked example, the bulk limit means 6 requests: five full batches of 10,000 and one of 6,400. That's 56,400 emails, well inside one month of Pro's 100,000 API emails. The cost is development time. You need a script or service that handles batching, retries and storage, which a one-off clean doesn't justify.
Bottom line: Files suit one-off cleans. The API suits ongoing syncs where your database already holds the record ID.
What mistakes cause custom fields to go missing?
- Joining on row order. Sorting either file breaks the match silently, and the output still looks complete.
- Skipping the header row when splitting. The second and third parts arrive without column names, and the mapping fails.
- Verifying duplicates. You spend quota twice and may get two statuses for one address.
- Letting Excel convert IDs. Once a 16-digit ID is saved with zeros in place of its final digits, the original can't be recovered from that file.
- Forgetting the 24-hour Free window. The results file may be gone before you reattach it.
- Re-importing an email-only file. Custom fields are overwritten or left blank in the ESP.
- Deleting Invalid addresses instead of suppressing them. They can come back in a later import.
- Skipping reconciliation. If the output row count doesn't match the input, something was dropped. If a large share comes back invalid, pause and read what to do if 30 percent of your list is invalid.
Bottom line: Most failures are silent, so check row counts and blank statuses before anything goes back to your ESP.
How we compared (and limitations)
This guide draws on public documentation and Unlimited Verifier's published plan facts: the Free and Pro limits, the 24-hour and 30-day result windows, the 25,000-email list cap, the 10MB upload limit, the 10,000-email bulk API limit and the 100,000 monthly API emails on Pro. The 99.5% deep-verification accuracy is the company's own stated figure, not an independent benchmark. The verification engine is also published on GitHub if you want to inspect the code yourself.
We haven't benchmarked export formats across verifiers. Confirm your own column behaviour with a small test file before a large run. The worked example uses hypothetical data and illustrative results. The method works with any verifier that returns a status per address, and the Excel and reconciliation steps don't depend on the tool. For comparisons of vendors, see best email verification tools and how to choose an email verifier. For file preparation, see how to upload a CSV to an email verifier, what file formats do email verifiers accept and how to split a large CSV for email verification. To clean a messy file before the first upload, see how to clean a messy CSV of emails before upload. For the spreadsheet features used above, Microsoft Support covers the functions, and the pandas documentation covers the same join in Python. To keep the list clean between cleans, see email list maintenance best practices.
To start with your own file, create a free account, run the 20-row column test and do the reconciliation check before re-importing anything. For spot checks on individual records, the free email checker tests single addresses quickly.
Join-back method in four steps
- Add a stable ID column to the master file, pasted as values.
- Upload only the email column (or the full file if the tool keeps it) and download the results.
- Create a key column LOWER(TRIM(email)) in both files, then join on it.
- Confirm output rows equal input rows, then re-import the merged file with custom fields intact.
Frequently asked questions
Does email verification keep my extra CSV columns?
Usually not. Verifiers read the email column and return the address with a status, not your other fields. Keep a stable ID and join the results back to your master file after verification, then check the row count matches the input.
What is the best join key for merging verification results?
Use a normalised email, meaning LOWER(TRIM(email)) applied to both files. A stable record ID is even better when you have one, because it avoids any ambiguity from duplicate or differently cased addresses.
Why not match rows by position?
Sorting either file in Excel silently breaks row alignment, so results end up attached to the wrong contacts. Matching on a key rather than order prevents this.
How do I stop Excel from corrupting customer IDs?
Import long numeric IDs, postcodes and phone numbers as text rather than numbers. Excel stores only 15 significant digits, so a 16-digit ID loses its final digits on save and the loss cannot be recovered from the file.
How many emails can I verify in one upload?
According to the article's stated limits, free lists hold up to 150 emails and Pro lists up to 25,000, with each upload capped at 10MB. Check the current plan page before large runs, as limits can change.
How long are verification results kept?
The article states free results are kept for 24 hours and Pro results for 30 days. Download and merge your results the same day to avoid losing them.
Should I verify every row or each unique address?
Verify each unique address once, then copy its status to every row that shares it. This saves credits and keeps duplicate contacts consistent.
What should I do if the output row count differs from the input?
Stop before re-importing. Look for unmatched emails caused by whitespace, case, broken CSV quoting or encoding damage, fix them, and re-run the join until the counts match.
Cut Bounces and Protect Your Sender Reputation With Unlimited Email Verification
Every invalid address you send to hurts your sender reputation and your inbox placement. Verifying your list before each send means fewer bounces, healthier deliverability, and a predictable cost. With Unlimited Verifier, standard verification is unlimited on every plan, with no per-email fees.
Unlimited Verifier checks syntax, domain, MX records and the SMTP mailbox. It also detects catch-all domains and flags disposable and role-based addresses. Export a CSV from your email platform or CRM, upload and verify the list, then re-import the clean results. You can also automate checks with the email verification API.
| Free | Pro | |
|---|---|---|
| Price | $0 | $29/month ($25/month billed annually) |
| Lists | 25 a month, up to 150 emails each | Unlimited, up to 25,000 emails each |
| Deep verifications | 750 a month | 25,000 a month |
| API access | No | Yes (100,000 emails a month) |
| Result retention | 24 hours | 30 days |
Why teams choose Unlimited Verifier
- Unlimited standard verification on all plans, including Free, with no per-email fees.
- Catch-all detection so you know which domains accept every address.
- Clear Valid / Risky / Invalid results, exportable as CSV, Excel or JSON.
- An open-source verification engine you can inspect on GitHub.
Unlimited Verifier states 99.5% accuracy for deep verification.
Create a free account and verify your next list before you hit send.
Want to test a single address first? Try the free email checker.