Using the Native CRM 4.0 Import for Lookup and Picklist Values

I have recently been asked if you could use the native Tools-Import functionality of Microsoft Dynamics CRM 4.0 when importing records with lookup and picklist values. My answer was "of course", as I use it all the time with our production CRM system internally.

However, I never really thought about what I was doing, since it just happen to work for me the first time. I suspect that one of the reasons for my apparent success was that I started by exporting from a view (or advanced find) and used that as my data source template, which CRM in turn used an automatic data map for me. I decided to do some quick research and detail my findings in hopes that it is useful for others trying this.

Here are some key things to consider when importing with the native Import functionality:

1. You need to have CRM automatically map the data map for your import. To do this, ensure your import columns match EXACTLY the attribute display name. The easiest way to do this is to export from a view or advanced find. Ensure the related records or picklist values exist in the system prior to import.
2. 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 and its value will be an integer.
3. Ensure you have all required fields in your source data file.
4. Save your source data file in the CSV format and then run through the native import process.
5. All records imported through the native Import functionality will be owned by you. If you need the records owned by someone else, you should either use an alternate method of import or simply reassign the records after you get them into the system (manually or with a workflow rule).
6. If your 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 by specifying the GUID (record id of the referenced value) or integer (identifier for the picklist value) instead of the name. Keep in mind that only the record with the duplicate name will fail, not the entire import.

Here is a quick example of importing contacts associated to different parent account records. Let's say that the following two accounts, Contoso and Fabrikam, already exist in your Dynamics CRM system. Fabrikam's record id is 072AA102-11F5-DC11-8C5D-0003FF1FCD52. You now want to import the following 5 contact records from Excel to 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 work fine.

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

Finish the import wizard steps, and then from the Workplace click Imports and you can then monitor your import status from here. The next image shows the actual import record. This record tracks your settings from the Import wizard, as well as what records failed or were created successfully.

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

Of course, the native Import functionality wasn't meant to solve all import challenges. When you find that you have stretched its capabilities, consider the free Microsoft Dynamics CRM Data Migration Manager (DMM) tool, a third party tool like Scribe, or write your own import (using the supported CRM API's of course).