How to prepare a contacts CSV before you import it

Importing contacts into an email tool or CRM should take two minutes. When it goes wrong, it eats an afternoon: half the rows are missing, every ZIP code in New England has lost its first digit, and José now appears as "José." Almost every one of those problems starts in the CSV file, not in the tool you are importing into.

By David Ray · Updated November 5, 2026 · 9 min read · How we research

Here are the fixes in the order to make them, with Excel and Google Sheets steps and a checklist at the end.

Start with a backup and a working copy

Before changing anything, save a copy of the original file somewhere safe and name it clearly, for example contacts-original-2026-10.xlsx. Then do all your cleanup in a second copy.

This matters more than it sounds. A CSV keeps only values, not formatting, so some mistakes can't be undone once it is saved. The original is your way back.

Get the shape right: one header row, one contact per row

Import tools expect a plain grid. The first row holds column names, and every row after that is exactly one contact. Things that break that shape:

• Title rows above the header. A line like "Member list, updated March" in row 1 makes the tool treat it as the header. Delete it so your column names are in row 1.
• Notes, totals and subheadings mixed in. A row that says "Board members" or "Total: 412" will be imported as a contact. Remove them, and if the grouping matters, turn it into a column (for example, a Group column with "Board" in it).
• Merged cells. Unmerge them. In Excel, select the sheet and choose Home > Merge & Center > Unmerge Cells. In Google Sheets, choose Format > Merge cells > Unmerge.
• Several emails in one cell. A cell like sam@brightpath.com; sam.lee@gmail.com will usually fail or import as one broken address. Split them with Data > Text to Columns in Excel (choose Delimited, then the semicolon or comma) or Data > Split text to columns in Google Sheets. Keep the first address in your Email column and the second in an "Email 2" column if your tool has one, or give it its own row.

Use clear, consistent column names

During an import, the tool asks you to match each column to one of its fields. Clear names make that step quick, and some tools match them automatically. Use one name per column, no blank headers, and no two columns with the same name.

If you are combining files from different sources, rename the headers to match in every file before you merge them. Our guide to merging contact lists covers that in detail.

Split "Name <email>" cells into two columns

Contacts copied out of an email program often look like Maria Lopez <maria@northwind.com>. Most import tools want the name and the address in separate columns. With these values in column A, starting in row 2:

Email (Excel or Google Sheets):
=IFERROR(MID(A2,FIND("<",A2)+1,FIND(">",A2)-FIND("<",A2)-1),TRIM(A2))

This takes everything between the < and >. If a cell is already a bare address, IFERROR just returns it trimmed.

Name (Excel or Google Sheets):
=IFERROR(SUBSTITUTE(TRIM(LEFT(A2,FIND("<",A2)-1)),CHAR(34),""),"")

This takes everything before the <, trims the spaces, and removes any straight double quotes, which some programs wrap around names like "Lopez, Maria". In Google Sheets you can also use =REGEXEXTRACT(A2,"<([^>]+)>") for the email part.

Once both columns look right, copy them and paste them back as values (Excel: Home > Paste > Paste Values; Sheets: Edit > Paste special > Values only) so they no longer depend on column A. If you need First Name and Last Name separately, split the name column on the space, but check names like "Mary Ann Lopez" by hand afterward.

Remove blank rows and stray spaces

Blank rows can make some tools stop reading early or create empty contacts, so remove them first.

• Excel: select the Email column, choose Home > Find & Select > Go To Special, pick Blanks and click OK. Then choose Home > Delete > Delete Sheet Rows. Because you selected only the Email column, this removes every row without an email. Rows that have a phone number but no email go too, so copy those to another sheet first if you still need them.
• Google Sheets: choose Data > Create a filter, filter the Email column to show only blanks, select those rows, right-click and delete them, then turn the filter off.

Spaces before or after an address are invisible but can make an import fail or create duplicates. In Google Sheets, select the data and choose Data > Data cleanup > Trim whitespace. In Excel, use =TRIM(A2) in a helper column, then paste the results back as values.

This is also the moment to take out duplicates and obvious typos, before they get copied into your new system. ListaFix's duplicate remover and typo fixer both work in your browser. For addresses that look fine but may no longer work, you can verify the list with Veml by uploading the same CSV; each address comes back Passed, Risky, Unknown or Invalid, and you can download a CSV of the results or copy just the Passed emails.

Protect ZIP codes, phone numbers and dates

Excel tries to be helpful by turning anything that looks like a number into a number. For contact data, that causes three classic problems:

• Leading zeros vanish. ZIP code 02134 becomes 2134. Phone numbers stored without punctuation can lose a leading 0 the same way.
• Long numbers turn into scientific notation. A long ID or phone number can show up as something like 1.23E+15, and Excel only keeps 15 significant digits, so anything beyond that is lost for good.
• Dates get reinterpreted. 03/04/2026 means March 4 in the US and April 3 in much of the world, and some values that are not dates at all get turned into them.

To prevent this, don't open the CSV by double-clicking it. In Excel, use Data > From Text/CSV, pick the file, then click Transform Data (called Edit in some older versions). In the editor, select the ZIP and Phone columns, choose Home > Data Type > Text, pick Replace current, then Close & Load. Recent versions of Excel for Microsoft 365 and Excel 2024 also let you switch off these conversions under File > Options > Data > Automatic Data Conversion (on a Mac: Excel > Preferences > Edit).

In Google Sheets, import with File > Import and turn off the option to convert text to numbers, dates and formulas.

Already lost the zeros on five-digit US ZIP codes? =TEXT(A2,"00000") adds them back as text. For dates, the safest format for an import is year-month-day, like 2026-03-04, which no tool misreads. Check what your tool asks for, though, since some want a specific format.

Save the file the right way

Use UTF-8 so names stay readable

If accented or non-English names come out as gibberish, like "José" instead of "José" or "Müller" instead of "Müller," the file was saved in one text encoding and read in another. Encoding is just the rule a computer uses to turn letters into stored bytes. UTF-8 is the modern standard that handles every language, and it is what most import tools expect.

• Excel: choose File > Save As (or Save a Copy) and in the file type list pick CSV UTF-8 (Comma delimited) (*.csv), not the plain "CSV (Comma delimited)" option. Excel saves only the sheet you are viewing, so make sure the right tab is open.
• Google Sheets: File > Download > Comma-separated values (.csv) already saves as UTF-8.
• Opening a UTF-8 file in Excel: if a file you know is fine looks garbled when double-clicked, open it with Data > From Text/CSV instead and set File Origin to 65001: Unicode (UTF-8).

Let the spreadsheet handle commas and quotes

A CSV separates values with commas, so what happens when a value contains a comma, like the company name Brightpath, Inc.? The format handles it by wrapping that value in double quotes: "Brightpath, Inc.". A double quote inside a value is written twice: "The ""Northwind"" Group".

Excel and Google Sheets do this for you when they save. Trouble starts when someone edits the CSV by hand in Notepad and a stray comma pushes every value after it one column to the right. If one row's ZIP code ends up in the Phone column, look for an unquoted comma in that row. When you need to make changes, make them in the spreadsheet and save again rather than editing the raw text.

Test with 10 rows before the full import

Copy your header row plus about 10 contacts into a new file, choosing a mix that includes an accented name, a ZIP code with a leading zero, a company name with a comma, and a contact with a blank phone. Save it the same way as the full file and import it. Then open those contacts in the tool and check every field.

If the test looks right, import the full file. If not, you have 10 contacts to delete instead of a few thousand. If the full list is too big for one upload, our guide to splitting an email list shows how to break it into smaller files.

Your pre-import checklist

• Original file backed up, working on a copy
• Column names in row 1, nothing above them
• One contact per row, one email per cell, no merged cells
• Clear, unique column names that match the tool's fields
• "Name <email>" cells split into separate columns
• Blank rows, stray spaces, duplicates and typos removed
• ZIP codes and phone numbers stored as text, leading zeros intact
• Dates in one consistent format
• Saved as CSV UTF-8, with the right sheet active
• Commas inside values handled by the spreadsheet, not by hand
• Test import of 10 rows checked field by field

Using Mailchimp? See how to clean a Mailchimp audience before your next campaign.

This is general information, not legal advice.

Questions people ask

Should I import an Excel file or a CSV?

Use whatever your tool asks for. Most email tools and CRMs ask for CSV because it is plain text that any program can read. If yours accepts .xlsx, that can avoid some encoding problems, but the same cleanup steps still apply.

Why does my CSV look fine in Google Sheets but garbled in Excel?

Usually the file is fine and Excel is guessing the wrong encoding when you double-click it. Open it through Data > From Text/CSV and set File Origin to 65001: Unicode (UTF-8).

What should I do with contacts that have no email address?

If you are importing into an email tool, leave them out, since the tool can't use them. Keep them in your backup or a separate file if they have phone numbers or mailing addresses you still need.

Do I need to keep the column names my old tool used?

No. Use simple names like Email, First Name and Last Name. The import screen lets you match each column to the right field, and clear names make that quick.