A lot of people struggle with importing data, either when trying to update existing information or bringing new information into their database. There are a few simple steps that you can take to reduce the likelihood of errors and warnings.

Check our Knowledge Base

We have a number of Forum Posts and Knowledge Base pages that will not only guide you through the Import Wizard, but also contain examples for specific types of imports, like the import of Campaign Memberships.

Check previous Imports

If a similar import has been done before, it may be that you can use the ‘Use mappings from previous import’ option. This will require you to have an Import File that has the same column headings as the previous Import File. You can Export this from the previous Import to have a look and see how the data has been formatted.

Check your data

This is the most important thing you can do to help with a smooth import process; bad data will cause problems either in the import or once it is in your database.

1. Check for duplicates

As your data will be in a CSV file take advantage of Excel’s Duplication Tool. Simply highlight a column where you are likely to find duplicates that could present issues, such as Name, Description or Email Address.Then select the Conditional Formatting > Highlight Cell Rules > Duplicate Values. 

This will highlight cells with duplicate information in them.You can then run your eye over these - are they actually duplicates, and therefore can you remove the extra rows?

If your data is of a poor quality, duplicates can still get through, like the example below:
 

We can see that these records are almost identical, except the first row has a double space between the first and last name. A duplicate check will not recognise these rows as duplicates based on the Name column, but would have if we did the duplicate check using the email address. If you are doing a large import with a lot of information we would recommend checking for duplicates on as many columns as possible. Think ahead: which fields are you going to use to identify duplicates in the Import? This needs to be something unique - if it is not, then the import will update an existing record rather than creating a new one. For example, if you are de-duping using the Subject field, and you have multiple rows with the same Subject, then the import will overwrite the data from the previously imported row. If we do want the Subjects to be the same, identify another column that can also be used when de-duping - in the above set of data, we might de-dupe on both Subject and Due Date.
 

2. Check for bad values

• Are telephone numbers are in the correct format?
• Are the email addresses valid? Email addresses like the ones below will be rejected by the import.

• If the column is mapping to a picklist on Workbooks, does the picklist contain all of the expected values. Are there values in your file that do not match the picklist values? Remember that when matching this may be case sensitive.
• Are Date Formats all the same? e.g. dd/mm/yyyy. When mapping a date column, are you sure that you have selected the correct Date Format?
• Are your currency values split into 2 columns? There should be a column for just the value, without the currency code, and this should be formatted as a number. The currency code should be a separate column. For example, ‘£4,500.00’ should be split into a number column storing ‘4500.00’ and another storing the currency code ‘GBP’.

3. Check you have all of the required data

• When creating new records, there are some fields that are mandatory. These are marked with a star in the Import Wizard. Make sure that your Import File contains a column for each of these. NOTE - if you are only updating existing records, you do not need to map to the starred fields. 
• If you running an import to update records, make sure that you file contains a unique identifier for finding the records that already exist.
• Are all of the columns labelled in such a way that makes it clear where they should be mapped to? Don’t have columns with the same name. If your Import will be extended to different record types, make sure it is clear which column should be mapped to which record type. Open up a Workbooks record to view all of the fields if it helps.

4. Mapping to DLIs

• If you are mapping to a DLI field, your import file must contain a unique identifier for the record that is being populated in the DLI. For example, if you are trying to populate a Primary Contact field with a Person, it is preferable to use the Person Reference to match, as Person Name is not always unique, may have been misspelled, or may contain a Nickname rather than Full Name. If the Import Tool finds multiple possible matching records, the DLI will not be populated.
• Are there any constraints on the DLI that is being populated? If so, are you sure that your data fits those constraints?

Remember - you can reject your import if it does not work first time, and this will take you back to the mappings stage of the Import Wizard. Do not approve the Import until you are happy with it.