Skip to content

Cleaning a CSV before you import it

Last checked November 14, 2026

The shape of a file (header, separators, quotes, encoding) is a common cause of failed imports. Fix the shape first, then the values. Always work on a copy and keep the original untouched.

Start with the header

The first line should name each column, once. Many import tools read it to decide where data goes. A CRM maker's guide asks for a header row where each column header corresponds to a property.1

Check these:

  • Every column has a name.
  • No two columns share a name.
  • There's no title row, logo text or blank line above the header.
  • Names match the new system's fields, or you've planned the mapping. See field mapping.

Check the separator

Not every "CSV" separates fields with a comma. One help page warns that some European spreadsheet programs use semicolons to separate data fields in CSV or TXT files.2 Its advice is to replace the semicolons with commas and try again.2

You'll spot this in a text editor. If a whole record sits in one column after import, the separator is the first thing to check.

Check that every row has the same shape

Each record should have the same number of fields as the header. A row that's short or long pushes values into the wrong columns, and everything after it can shift.

The usual causes:

  • A comma inside a value that isn't wrapped in quotes.
  • A line break inside a note or an address.
  • A stray quote mark.

The RFC that describes the format says fields containing line breaks, double quotes or commas should be enclosed in double quotes.3 If a field holds a quote mark, it also needs to be written in the way the format expects. A spreadsheet program normally handles this for you when it saves a CSV. Hand edits in a text editor are where mistakes creep in.

Remove rows that aren't records

Spreadsheet exports often add extras. Look at the bottom and top of the file for:

  • Total rows.
  • Blank rows between groups.
  • Notes or report titles.
  • Subtotals mixed in with the data.

A tool may import each of these as a real record, leaving you with a customer called "Total".

Mind the stray spaces

The format's description says spaces are considered part of a field and should not be ignored.3 The Library of Congress notes that the treatment of whitespace next to separators varies among applications.4 A name saved as " Ana Ruiz" with a leading space can fail to match "Ana Ruiz" in the new system. Two copies of the same email, one with a trailing space, may both be created.

Because programs differ, trim leading and trailing spaces in names, emails and codes.

Use one sheet and one table

If your data lives in a spreadsheet workbook with several tabs, split it up. One import guide says an uploaded file must contain only one sheet.1 Save each tab as its own file.

Check the encoding

Encoding is how a file stores letters like é or ñ. If it's wrong, names turn into odd symbols. One guide says files need to be UTF-8 encoded if they include foreign language characters.1 Save as UTF-8 if your program offers the choice, then open the result in a text editor to confirm.

A cleaning orderCopy the original fileCheck header and separatorsCheck rows and quotesCheck values column by columnSave as CSV and reopen
Work from the structure of the file down to the values.

Make values consistent

After the structure is sound, go column by column.

  1. Use the same wording for the same thing. "USA", "US" and "United States" are three values to a computer.
  2. Keep each column to one kind of data. Don't mix phone numbers and notes.
  3. Split combined fields if the new system has separate ones, such as a full name that should be first and last.
  4. Fill in any field the new system requires.

Dates, phone numbers and money have their own page, on format fixes.

Save and reopen

When you finish, save as CSV and open the saved file in a text editor, not only in the spreadsheet. Confirm that the header, separators and quotes still look right. Then load a few rows first, as described on the trial-run page.

Notes and sources

  1. Format import files, HubSpot Knowledge Base
  2. Import Contacts to Mailchimp, Mailchimp Help Center
  3. RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) Files, RFC Editor
  4. CSV, Comma Separated Values (RFC 4180), Library of Congress