How to Clean a CSV for Mailchimp Import: The Format Rules That Make Contacts Fail

To import a CSV into Mailchimp cleanly, you need one email column with one address per contact, no extra spaces, no special characters before the @, dates in YYYY-MM-DD form, two-letter country codes, and a file saved as CSV or tab-delimited text rather than Excel. Most failed or partial imports come down to one of those rules. CSVPrune from Automata Labs fixes them on your own PC: it trims whitespace, normalizes emails, standardizes dates, removes duplicates and exports a clean UTF-8 CSV, without uploading your contact list to an online cleaner.

Clean your contact export offline, then import it into Mailchimp in one pass.

Get it from Microsoft

What format does Mailchimp need for a contact import?

Mailchimp’s format guidelines for your import file and its contact import troubleshooting page set the rules. The ones that cause most trouble:

Field	Mailchimp’s rule
File type	CSV or tab-delimited TXT. Excel (.xls), ACT!, and vCard files are not supported for upload.
Email address	Required for email contacts, with only 1 email for each contact.
Spaces	No extra spaces before or after an address.
International characters	Supported only after the @ symbol, not in the part before it.
Text fields	Limited to 255 bytes, clipped if longer. Emoji and some alphabets use more than one byte per character.
Address	Parts separated by two spaces instead of commas, each line fewer than 45 characters, two-letter country code. Use GB, not UK.
Dates	YYYY-MM-DD or MM-DD-YYYY.
Birthdays	A birthday field with MM/DD or DD-MM.
Tags	100 characters or fewer, no emoji. A tag containing a comma must be in double quotes.
SMS phone numbers	E.164 format, beginning with a + sign and a country code.
OPTIN_TIME, CONFIRM_TIME	Future dates cause an error. Only checked the first time a contact is imported.
File size	Over 200 MB, or about one million contacts, may cause errors. Split the file.

You can arrange columns in any order. Mailchimp asks you to match each column to a field during the import.

Why did Mailchimp skip some of my contacts?

Mailchimp checks every import for duplicates, bounces, unsubscribes, wrong formats and syntax errors, and leaves out contacts that fail. The troubleshooting page lists the common reasons:

• Extra spaces. A trailing space after an address is a syntax error. Mailchimp suggests the TRIM function in Excel.
• Typos in common domains. Mailchimp blocks misspellings such as “gamil.com” or “yhoo.com” to prevent bounces.
• Unsupported characters in the part of the address before the @.
• Semicolons instead of commas. Some regional versions of Excel save CSV files with semicolons.
• Role-based addresses such as [email protected], which Mailchimp may handle differently because they tend to bounce.
• Previously unsubscribed or bounced contacts, which Mailchimp does not re-add.
• A file that is too large, which may fail and should be split.

The first four are file problems you can fix before you upload. The rest are list-health rules Mailchimp applies no matter how clean the file is.

How do I fix a Mailchimp CSV in Excel?

Excel can do most of this, but it brings risks of its own.

1. Trim spaces with =TRIM(A2) in a helper column, then paste as values over the original.
2. Lowercase emails with =LOWER(A2). Mailchimp does not require it, but it makes duplicates easier to spot.
3. Remove duplicates with Data, Remove Duplicates, on the email column only.
4. Fix dates with a custom number format of yyyy-mm-dd, then check that Excel did not misread day and month.
5. Save as CSV UTF-8, not plain CSV, so names with accents survive.

The catch is that Excel changes data when it opens a CSV. It can strip the leading zeros from ZIP and postal codes, turn long IDs into scientific notation, and reinterpret dates. Our guide to Excel removing leading zeros from CSV files explains why. If the export opens as one long column of text, you have an encoding or delimiter problem, covered in fixing CSV encoding and delimiters.

How do I clean a Mailchimp import file with CSVPrune?

CSVPrune is an offline CSV cleaner, viewer and converter for Windows. Its Microsoft Store listing describes what matters for a contact import:

• Opens files that break Excel, including 100,000+ rows, without a row limit or freezing.
• Encoding and delimiter fixes for UTF-8, UTF-16 and Latin-1, with comma, semicolon, tab or pipe separators.
• Leading zeros and long IDs kept intact.
• Trim whitespace, normalize emails and standardize text, numbers, dates and booleans.
• Remove exact duplicates, or catch near-duplicates with fuzzy matching.
• Find and replace with plain text or full regex, scoped to any column.
• Column profiling that spots invalid values and gives a data-quality score.
• Mask sensitive fields when you need to share a sample.
• Recipes that save your cleaning steps and replay them on next week’s export.
• Export to CSV, JSON or XLSX.
• No cloud, no telemetry and no network calls of any kind.

Trim, normalize and dedupe your contact list without sending it to an online tool.

Get it from Microsoft

A step-by-step Mailchimp cleanup in CSVPrune

1. Open the export. Open the CSV from your CRM, shop or form tool. If it shows one garbled column, switch the encoding or delimiter until the columns line up.
2. Profile the email column. Look for invalid values, blanks and addresses with stray characters.
3. Trim whitespace and normalize emails across the email column.
4. Fix common domain typos with find and replace scoped to the email column, for example replacing @gamil.com with @gmail.com. Check each pattern before you apply it.
5. Remove duplicates so each contact has one row. Review fuzzy near-duplicates by hand before you remove any.
6. Standardize dates in signup or purchase date columns to YYYY-MM-DD.
7. Fix country codes. Replace UK with GB in the country column, and convert full country names to two-letter codes.
8. Check tags. If one column holds several tags, separate them with commas and wrap any tag that contains a comma in double quotes.
9. Compare original and cleaned data with one click to confirm what changed.
10. Export as CSV and import it into Mailchimp.
11. Save the steps as a recipe, so next month’s export takes one click.

If the file is larger than about one million contacts, split it into smaller files before you import.

Mailchimp import errors and the fix for each

What happens	Likely cause	Fix
Upload rejected	File saved as .xls or another format	Export as CSV or tab-delimited TXT
All data in one column	Semicolon delimiter or wrong encoding	Re-save with commas and UTF-8
Some contacts missing	Extra spaces, typos, unsupported characters	Trim and normalize the email column
Accented names garbled	File not saved as UTF-8	Export as UTF-8
UK addresses fail	UK used as the country code	Replace with GB
Dates imported wrong	Ambiguous day and month order	Standardize to YYYY-MM-DD
Tags split oddly	A tag with a comma not in double quotes	Wrap that tag in double quotes
Import errors on opt-in time	Future dates in OPTIN_TIME	Correct the dates
Large import fails	Over 200 MB or about 1 million contacts	Split into several files

Common mistakes

1. Saving from Excel as plain CSV. Use CSV UTF-8, or export from a tool that does not change your data.
2. Deduplicating on the full row. Two rows with the same email but different names are still duplicates for Mailchimp. Deduplicate on the email column.
3. Fixing typos with a broad replace. Replacing gmail everywhere can damage real addresses. Scope the replace to the domain.
4. Uploading the customer list to an online cleaner. That is personal data. Clean it locally. If you need to share a sample, mask personal data first.
5. Cleaning by hand every month. Save a recipe and batch clean CSV files automatically.

Frequently asked questions

What file format does Mailchimp accept for imports?

CSV or tab-delimited TXT files when you choose the Upload File option. Mailchimp does not support Excel .xls, ACT! or vCard files for upload.

Why does Mailchimp say my email addresses have syntax errors?

Usually because of extra spaces, missing parts of the address, unsupported characters before the @, or typos in common domains. Trim and normalize the email column and import again.

What date format does Mailchimp use for imports?

Use YYYY-MM-DD or MM-DD-YYYY for ordinary date fields. Birthdays go in a birthday field as MM/DD or DD-MM.

How do I import a large contact list into Mailchimp?

Mailchimp says files over 200 MB, or about one million contacts, may cause errors. Split the file into smaller files and import them one at a time.

Does CSVPrune upload my contacts?

No. It runs entirely on your PC with no cloud, no telemetry and no network calls. For a broader look at the app, see the CSV cleaner for Windows page.

Try CSVPrune

Get CSVPrune from the Microsoft Store. It is a one-time purchase, and the Store listing shows the current price. Moving products rather than contacts? See our guide to Shopify product CSV import errors.

A clean, UTF-8 contact file every time, with your recipe ready for next month.

Get it from Microsoft

Mailchimp is a registered trademark of The Rocket Science Group. Microsoft, Windows and Excel are trademarks of the Microsoft group of companies. CSVPrune is not affiliated with or endorsed by them.

Last updated 19 October 2026. Published by Automata Labs, a software company in Canada.