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.
Step 2: Start MSCRM Data Import Wizard and upload the csv file as shown below.
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.
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.
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:
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:
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.
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.
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.
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.
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:
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.
Select Account Name and hit Ok button.
You will see that the duplicate account entries will be gone:
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,