Preparing Your Excel File for Data Import

Before Starting

In order to add multiple contacts at once to your CRM, you can import them directly from an Excel file.

Preparing your file before doing the import will save you time correcting import errors. It is also the best way to make sure your file is in order and that all the necessary information is present.

The article will cover the following points: 

• How to correctly import your data
• Rules and specific formats
• Tips and tricks to prepare your file for data import

How to Correctly Import Your Data

Setting Up the Data Fields

When you import your data, the software will match: 

• The information fields created in the software from the page Community > Settings 
• The other columns in your Excel file and the values they contain 

You need to create a field in the software for every column in your Excel sheet.  Check out these articles for more explanations on setting up these fields. 

Using the Dummy Data

The easiest way to avoid import errors is to use the Excel template, which you can download from Community > Contacts > Add multiple contacts > Read the import guide.  

Using this file offers a couple of advantages:

• It already contains all your data fields created on the page Community > Settings
• Each column uses the correct number format, adapted to its values (text, dates, numbers, etc.).

Passing from a date format to a number format in Excel will replace the date of 08/02/1999 with a random number, which will create an error when the data is imported. The template already has all the correct data formats - saving you time when importing your data.

A file with fictional values is equally available from Community > Contacts > Import multiple contacts. This file contains fictional people, which you will need to replace with your contacts. 

Rules and Specific Formats

For the Unique User ID

Every CRM needs a unique user ID. Find out more about this topic in this article.

If you've chosen the email as your unique user ID, all your members need to have an email. 

In the same way, if your unique user ID is a specific field (such as a registration number or license number), all members need to have that specific field filled in.

Check out the page Community > Import multiple contacts > read the import guide to get recommendations specifically for the data fields created on your account. 

For the Default Information Fields

The fields' First and Last Name are obligatory and cannot hold the same values. 

The values possible for Gender are "M", "Male", "F", or "Female". 

Emails must not have any accents, and must follow the format "___@___.__".

The fields Dates are in the format MM/DD/YYYY or MM/DD/YY. 

The columns City, Zip Code and Country must be included, along with the column Address. The address must be exhaustive in order to be verified and accepted by Google Maps.  

If the address is entered incorrectly and cannot be verified by Google Maps, the city center address will be used. 

For the Personalized Fields 

If you create a field that only accepts predefined values (if its a drop-down list or multiple choice), the values filled in the Excel will need to be strictly identical to those defined in Community > Settings. For example, if it is misspelled (even just having a lowercase instead of a capital letter), the value will not be accepted.

If you have several value choices for the same person, the values must be separated by the character "|", for example: Monday|Thursday. 

The fields that are of a text type are limited to 255 characters and the fields that are of a numerical type must be filled in using numbers (i.e."1", "1.2” or “1,2”). The fields that are website addresses (URLs) need to be formatted of the type "mywebsite.ext" or “sub-domaine.website.com". 

There is one field of the type Documents to Upload that allows the uploading of documents. It is however not possible to include files in the Excel file when importing in several contacts at once. 

These will pop-up as an import error, so we recommend deleting them directly in your Excel file before starting to import your data. 

Tips and Tricks to Prepare your File for Data Import

Deleting Duplicates

This check eliminates duplicates based on one or more reference columns.

We recommend this article which explains how to directly delete duplicates in your Excel file. 

Finding and Replacing Values

You can find cells that contain a certain text using the shortcut "Ctrl+F" (PC) or "Cmd+F (Mac). 

It is then possible to replace values, either one by one, or all at once. This can be useful when replacing simple values, for example, if you want to replace all the "Woman" gender values by "F". 

Check out this page for more information on the search and replace feature.

Removing Line Breaks

To delete line breaks, use the find & replace function described above, then: 

• Search "Alt + 010": this will detect the line breaks,
• Replace by " ": this will replace all the line breaks by a space 

Check out this page for more information on how to replace line breaks.