Scenario

You have received a list of company and contact data and you would like to import it into Crelate. You are having trouble importing the data because the columns in your spreadsheet seem to correspond to different record types all in the same row of data.

Overview

Crelate stores data of Companies and Contacts in two separate tables, with each individual Company or Contact getting its own row of data in that table.

Sometimes when we want to import data into Crelate from a spreadsheet, all this different data has been merged into a single row in the spreadsheet.

In this example, the Company record, as well was multiple Contact records have been merged into one row in our spreadsheet.

Solution

To import this data, we will need to first break the data up into a structure that matches how Crelate stores Company and Contact data and break each individual Contact into its own row. This process is called normalization.

Note: To help keep your Crelate database clean, before importing large amounts of data, please verify that your Duplicate Detection settings are configured correctly.

There are several ways to do this, but here is one method that is relatively easy:

1. Start by analyzing your spreadsheet and make note of how many entities you are dealing with. In our example above, there are two. Companies and Contacts.

2. Next analyze your spreadsheet and note if there are multiple of each type of entity described in each row. In our example, each row represents two different Contacts.

3. Make a several copies of your spreadsheet that maps to the number of entities and rows you discovered in step #1 and #2.

Ex: Here we have named them, Companies.xlsx, Contact1.xlsx and Contact2.xlsx.

4. Next open each spreadsheet and delete the columns that do not correspond to the record you are going to import with that particular sheet.

Ex: The Companies.xlsx sheet does not need any of the Contact columns

5. First, we will clean up our Companies spreadsheet.

6. Next, we will clean up our Contacts1.xlsx file. Make special note that we are keeping the Company Name column. This is important because we will use this to link Contacts and Companies together during the final import.

7. Repeat Step 6 on Contacts2.xlsx. If you have additional Contacts sheets, continuing repeating this step.

Note: Again, we are keeping the Company Name column.

8. You are now ready to import your data.

9. Open Crelate and go to the Navigation Menu and select Import Data…

10. First import your Companies spreadsheet. Because Companies are used later by each Contact spreadsheet, this must be imported first.

For information on how to map fields and import refer to this article: Importing a Spreadsheet

11. Next, Import your first Contact spreadsheet. But first, we need to make sure we take care of empty rows.

Important: The process of normalizing your spreadsheets will create many empty rows of Contacts. Empty rows will occur, because not every Company in our original spreadsheet had two Contacts. The more Contacts that were munged together, the more empty rows this process will create.

The first Contact spreadsheet will have the least number of empty rows, if any. This is because it is likely that each of the Companies in your original sheet had at least one Contact. However, its less like they will have a 2nd, 3rd or 4th Contact and so on. The number of empty rows will increase as you repeat this process.

Fortunately, Crelate has a way to ignore these rows during import. When mapping your data, be sure to click the Configure:

12. Repeat the process until all of your sheets are imported.

Did this answer your question?