The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

Importing data into Microsoft Dynamics CRM Online – Accounts & Contacts

Importing data into Microsoft Dynamics CRM Online – Accounts & Contacts

  • Comments 4

How to import data from a single file into multiple record types

Microsoft has released Microsoft Dynamics CRM Online Delivers the November 2009 Service Update earlier this month. To assist organizations with importing their data into Microsoft Dynamics CRM, a new enhanced Import Data Wizard is available among other new cool features in this release. This blog is about how you can import your data into Microsoft Dynamics CRM using MSCRM Import Data Wizard and some of the tips to make it a simpler experience. In the first blog Import Data Wizard was introduced.

In this blog, we’ll take a look at importing your contact list – a task that you are likely to try first to get a little data in the system as you try it out. Before we get started, you will need a data file that contains your contact list, usually in CSV format.

Most systems have a way of exporting your data at through some sort of report or similar export facility. For example, in Salesforce, you can export your Contacts using the “Contact Role Report” or "Contacts & Accounts Report".

If you take a look at a typical contact list, you’ll see that it contains information about both people and the businesses that they work for. It is important to know that Microsoft Dynamics CRM treats these as two different record types: Contact for people and Accounts for the businesses.

Why does this matter? Well, the MSCRM Import Data Wizard supports importing one record type data per file. So how do you import such data into two different record types in MSCRM? There is a small trick that makes this possible in MSCRM.

In this blog, we’ll show you how to do this by processing the data file twice, once for Accounts for the account information, then once for Contacts for the contact information contain in the file.

Let’s go over it.

Step 1: In this example the file used has data from Contact and Account record types. In such files, the contacts will be unique but the account data (e.g. Company Name) may be repeated because there may be several contacts for same account. So first make sure duplicate detection rules are published to avoid duplicate accounts in MSCRM. These rules are available out of box in MSCRM, so as a user you do not need to do anything. However, it is a good idea to understand this dependency.

image

Step 2: Start MSCRM Data Import Wizard and upload the csv file as shown below.

image

Step 3: In order to map the source data to MSCRM record types and fields, you need to have a map that can be used for your import.

If you are importing data from Salesforce, there are two such out of box maps available:

1. Salesforce Map for Full Export

2. Salesforce Map for Report Export

The first Map should be used if you are bringing your Salesforce that you have obtained by requesting a full export (this is a zip file containing other CSV files). The second map should be selected when you are bringing the data that you exported using one or more Salesforce Reports.

If you are importing data from some other system, you can select another data map that map matches your export or start with Map Automatically.

image

Step 4: On the next screen, select Account as the target record type. This is important as the contact data will have reference to account data and hence we should bring in Accounts before bringing in Contacts.

image

Step 5: You should see all Account record type fields mapped, rest of the fields will appear as unmapped. At this stage you will need to do following:

1. If you have Custom account fields, create new attributes for them by selecting “Create New Field” from the drop down and specifying the Field Name and Type as shown below:

image

Note: these fields aren’t automatically added to the form for the record, so after importing your data, you’ll separately need to customize the form to place the newly created fields where you want them.

2. For the fields that belong to Contact entity and appear as Not Mapped, choose ignore as shown below:

image

Do this until all the fields are either mapped or ignored.

Step 6: Click next and make sure Allow duplicate is set to No and submit the import.

image

Go to WorkPlace -> Import and look at the import session details. You may find few accounts have failed due to duplicate account information existing in the file as shown below. Do not worry, this is because the file has repeated account information and the duplicate detection rule prevents duplicate accounts to be created.

image

Step 7: Now we will import the Contact information. Lets start the import Wizard and point again to the same file. This time choose Contact on the Map Record Types screen as shown below.

image

This time you will see that all the out of box Salesforce Contact record type related fields will appear as mapped and rest of the fields will appear unmapped. If you have custom Contact fields in this unmapped list, create new attributes as described earlier in the blog. For rest of the fields, choose Ignore. Once you have everything either mapped or ignored, click Next and submit the import.

image

All the Contact data should import this time, linking to the Accounts you created in the previous pass. It’s that simple!

Now that you know how to bring in your contact list, there may be situations when you run into problems and your data needs additional processing. Let us look at them.

Case 1. In case you get Duplicate Column Header error while importing:

If you get the following error while importing the data, you have one or more duplicate columns:

image

This is not very common, however if you have Salesforce Enterprise Edition subscription, and the Accounts are Territory enabled, you may end up in a situation where few columns like "Last Modified Date", "Description" and "Skype name" may occur twice in your data file (one from contact and one from territory record types). If this happens, you should open your file in Excel and either rename the duplicate column headers to something unique or delete those columns.

Case 2: Account creation fails with "duplicate lookup reference" error:

Lets walk through an example when this may occur. Consider you have your contact list with following data:

First Name

Last Name

Account Name

Parent Account

John

Smith

Adventure UK

Contoso

Jay

Singh

Contoso

 

Harry

Lint

Contoso

 

There will be other columns, but I have taken smaller set to explain the situation. Let’s see how import behaves in such a situation. When the Adventure UK account is created, the Parent Account column that has reference to another account (Contoso) fails with error duplicate lookup reference as it finds two entries for account Contoso in the data file. What do you do in such situations? We use our friendly application Microsoft Office Excel to accomplish the task.

Make a copy of your contact list file and open the saved copy in Excel. Go to Data -> Remove Duplicate ribbon menu.

image

Select Account Name and hit Ok button.

image

You will see that the duplicate account entries will be gone:

image

Use this file to import the Accounts. You should still use the original file for the Contact import. This does the trick and your data is successfully migrated.

More tips and tricks on the way. Stay tuned.

Cheers,

Arun Kumar

  • Can I also import relationships when I use this wizard? We build some custom entities, and I would like to import a role between a list of accounts of contacts and this entity...

  • Snoek - Yes you can import relationships using this wizard. When you map the attributes, Wizard is smart to look into existing CRM records and the source files to resolve the relationship and import the data correctly.

  • This works great, with one exception. I am unable to figure out how to designate a primary contact in the account record. Is there a way to do this?

  • This works OK unless you want to also designate a primary contact in the account record while importing. If so, then try this:

    1.Put the Accounts and Contacts csv files together in a zip file a.Highlight both files b.Right Click c.Choose Send To d.Zip file

    2.Open CRM

    3.Click on Tools

    4.Click on Import Data

    5.Browse to the path of the zip file

    6.Map the Primary Contact of the Account to the Contact

    A big thank you to customer support!

Page 1 of 1 (4 items)
Leave a Comment
  • Please add 1 and 3 and type the answer here:
  • Post