In order to import data from a CSV file it is critical that the CSV file be properly formatted.

The most frequent problem that people run into is that " looks fine in Excel but when I try to import the data appears in different columns"!

The cause of this is how the data is formatted in the CSV text file.

It is important to know that a CSV file uses the comma “,” as the field separator between all the values it contains.  Without the right numbers of commas in a text line the CSV importer will be confused as to how many columns there are to import.

You may need to open the CSV in a text editor, not in Excel, to see what the text file looks like.

Here are some of the things to avoid:-

  • do not use formatted numbers; i.e. a number should be recorded as '1250.25' rather than '$1,250.25'
  • ensure that both the first and the last column in your file has a value for every record!  If not, then Excel may generate an incorrect number of commas-separated values for that line.
    One way to meet this rule is to make the last column in your import file a dummy column (e.g. column name 'DUMMY') and with the same value for all records (e.g. 'IGNORE').  You would then ignore that column during the import mapping phase .
  • There are two special characters that can cause problems.  The single quote (as in O'Donoghue) and double quote (as in Jack “The Knife” Jones) characters need to be removed from the import data you are trying to import.  
    In Excel, select all columns and perform a 'Find & Replace' search on  ‘ and “ and replace them either with a space or with a character that wouldn’t normally occur, e.g.  #@^.  
    After the import use the search function in Legrand CRM to find all the records that contain the special character and replace each one back to a ‘ or “
  • Ensure that the first 10 characters of the column headers are not identical. E.g. “Address 1 – City”, “Address 2 – City” etc. The import wizard can get confused and return the error “an item with the same key has already been added.”

These few tips should make your next CSV import work smoothly.