Topic: ‘Data Import Utility’

Are you interested in learning how to create new record types or new fields in CRM Online? Check out this post on the MSCRM Team Blog by Veera Bansal – it describes how to utilize the Import Data Wizard inside of CRM Online to create new record type and new fields in the system. This enhanced feature of the Import Utility is explained in more detail in the November 2009 Service Update for CRM Online. Click here to view a previous post on this information.

Microsoft has a blog post on their MSCRM Team Blog that provides information towards importing more than one entity type (in this case, both Accounts and Contacts). The post contains numerous screenshots, which allows for a higher level of understanding towards the process flow to follow.

Some people use data maps to do data imports, but I don’t. It’s annoying and time consuming, especially if you make a mistake.

I always, always, always use the Automatic mapping functionality that comes along with the Data Import tool. You need to export a view with the columns you want to import (column header = field on the form), and then remove the data from the spreadsheet you create (from row two down). Insert the new data under the correct columns headers, and save the new, edited spreadsheet as a CSV file. When you import it back into the tool, you will only have to upload the CSV file you edited during the Data Import – sans data map.

One thing I’ve found that makes things quick is to save the CSV file on my computer – one for each entity. This way, when I want to do another import on the same entity, I just have to insert the new rows of data under the correct column headers, re-save the CSV file (sometimes you have to close out of the file in order for the changes to take place), and then run through the Data Import tool again (this will only apply if you are importing the same fields). It will save me from having to create and export the view all over again.

One of the great features in Microsoft Dynamics CRM 4.0 is the ability to import data from a CSV file. However, some clients have expressed to me the need to create a data map and load a sample file for each import job is a bit laborious. This is when I show them the ease of the auto mapping function. Not only does the auto mapping feature expedite the import process, it will still allow users to map lookup and picklist values. For a lookup value, the user may identify the display name of the attribute, or its GUID, and for picklists, one may utilize the display value, or its designated integer.

Here are some important points to bear in mind when attempting to utilize the auto map functionality:

1. In order to have CRM automatically map the data for your import, make sure your import columns match EXACTLY to the attribute display name. An easy way to do this is to do a blank export from a view, or an advanced find, to create an Excel template for your source file. This will ensure you have column headings matched exactly to the attribute display names.

2. Make certain the related lookup records or picklist values exist in CRM prior to import. The import functionality in CRM will not create associated records.

3. You can use the display value or the actual value of the lookup or picklist record. For lookup relationships, the display name will be the primary attribute and the record GUID will be its value. For picklist attributes, the display name will be the name shown to users in the picklist, and its value will be an integer.

4. Make certain you have all required fields in your source data file.

5. Save your source data file in the CSV format.

6. If the display name (for either the lookup or the picklist) has duplicates, the record will not be imported. You will receive an error similar to A duplicate lookup reference was found. You can avoid this however by specifying the GUID of the related record, or the integer of the picklist value. Fortunately, only the record with the duplicate name will fail, not the entire import.

Here is a quick example of importing contacts via auto map that are associated to 2 different parent account records. Let’s say that the following two accounts, Fabrikam and I.B.I.S. Inc., already exist in your Dynamics CRM system. Fabrikam’s record id is D94D44AD-36D0-DC11-AA32-0003FF33509E, and conversely, the I.B.I.S. Inc. record id is 40A45765-8C77-DD11-AD80-0003FFFDFFFF. You now want to import the following 4 contact records into CRM, and each belongs to one of those two accounts. The Excel file is shown below. This file will show you that you can use either the Parent account’s name or identifier and the import will still be successful. Note that the file was created from an export of the ‘My Active Contacts’ view to ensure I have the correct attribute names.

Save your file as a CSV. Now, back in CRM, click Tools > Import Data. Select the saved CSV file and then click Next. Select the record type (contact in this case) and you should see “Automatic” dynamically listed for the Data Map. If you do not see this, then one of your columns does not exactly match an attribute for that record type. You will then need to go back and correct your source data file.

By default, all records are automatically assigned to the user conducting the import. However, the importing user may select a different record owner, via the Import Data Wizard, for all records in the source file.

Finish the import wizard steps. When complete, navigate to Workplace and click Imports. Here you can monitor your import status. The image below shows the actual import record from the example above. This record tracks your settings from the Import wizard, as well as what records failed and those created successfully in the ‘Results’ section.

Once complete, you will see your new records in the contact grid.

Keep in mind, the native Import functionality isn’t meant to solve all import challenges, even when creating a detailed Data Map. If you find that you have stretched its capabilities, consider the free Microsoft Dynamics CRM Data Migration Manager (DMM) tool, or a third party tool such as Scribe.