Using the Native CRM 4.0 Import with Automatic Data Mapping
Posted Wednesday, September 3rd, 2008
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.
Tags: Automatic Data Mapping, CSV File, Data Import, Native Import
Posted in Data Import Utility, Dynamics CRM
Not yet using Dynamics CRM? I.B.I.S., Inc. gives you the Dynamics CRM options you want, and the business results you need.
Learn more about our Dynamics CRM solutions
