CRM 2011 Data Import Wizard in practice

Data migration typically isn’t the most joyful part of a CRM implementation, but you really need to pay attention to carefully importing all the relevant customer data it if you want the users to adopt the CRM system as an integral part of their day to day activities, rather than yet another business application searching for its purpose. When implementing Microsoft Dynamics CRM, the logical place to start planning the import process is having a look at what tools are available in the application itself.

The Data Import Wizard used to be a curse word among the Dynamics CRM crowd for a long time, but you shouldn’t ignore this option right away, just because of its bad reputation. Sure, there are many limitations with the built-in tool, but it has come a long way since the previous versions. Having recently spent some hands-on time with the CRM 2011 Import Wizard, I decided to put together some of the useful links and pieces of information I discovered during the process. There’s plenty of great blog posts out there on individual data import features, but perhaps this can serve as a “getting started” tookit for planning on how to import data into Microsoft Dynamics CRM 2011.

Mapping related records based on custom ID fields

The CRM database by definition is primarily a place for storing information about how different objects relate with one another. This means you will almost always be dealing with source data that needs to reference another set of data once imported into the system. In Dynamics CRM these relationships manifest themselves as lookup fields that point from a child record to its parent.

When you are mapping the lookup fields from a child entity into a parent entity that’s already in the system, you always need to consider the possibility of duplicate values in the list of parent entities. Contact names are not unique, and neither are account names in many cases. Yes, you could import lookup references by using a CRM GUID instead of the primary field (most often the name attribute) of the parent entity, but how often would you have that available in your source data to be imported? Exactly.

The first and in my opinion the best improvement with the Import Wizard in CRM 2011 is the possibility to reference the parent entity with an alternative field. Yes, if you have a reliable unique value available in your data, such as customer number or contact email address, you’re free to use that to link your records together. Alternatively, you can construct specific import ID’s out of your data that you first import into a hidden field, then later on use that as the reference which connects your related child records into exactly the correct parent record without the risk of import row failure.

For step by step instructions, check out this blog post by MVP Leon Tribe: Changing the Lookup Reference When Importing Related Data.

Importing data for multiple entities in one go

A common format for customer data coming from non-relational systems is a flat file that contains both account and contact data on the same “table”. In these cases you will have multiple instances of the account’s information repeated on each line where there is an individual contact related to that account. You first reaction might be “oh well, guess I’ll have to split that into an accounts file and a contacts file, then remove the duplicates“. Well, good news: you don’t have to anymore!

Nowadays CRM comes with a built-in data map called  “For Generic Contact and Account Data”, which will allow you to import a file that has data intended for both account and contact records. First of all, you can map some of the source fields into both the target entities. Address information is a good example, as it’s typically stored separately on both accounts and contacts (yeah, data redundancy, but often it’s just more convenient for your everyday CRM usage).

Secondly, you will not get duplicate account records from each of the rows, as the Import Wizard is smart enough to detect the distinct parent accounts needed for the child contacts. Now, in order to get the expected results, it’s also up to you to be smart with your source data and field mapping. If any of the fields you’ve mapped to the parent account have any variation in their contents (such as phone numbers with different spacing formats), you will get duplicates, simply because the system will not throw away any unique data rows. Additionally, your child record imports to those accounts will result in failures, as the parent account lookup field will point to a non-unique value in the database (unless you used the aforementioned method to specify an alternative lookup reference). You should also take into consideration if the source data actually has intentional duplicate values for account names, such as branch offices with only a different address.

Check out this article for step-by-step instructions on how to import accounts and contacts from a single file. But what if you need to perform the same type of import, only you’re not dealing with accounts and contacts? Say, importing data to custom entities with a parent-child relationship, like “event” and “event attendee”? No problem, you can build a data map just like the “For Generic Contact and Account Data” one, by leveraging the multi-entity data file import mapping feature.

How much data is too much?

Even if you are importing only into a single target entity, there’s a good chance that you’ll cross the line of allowed maximum size of the import file for the Import Wizard, which is 8 MB. While the XML data import templates available for download from the CRM UI provide very nice features for ensuring the input data is in the correct format, they have the downside of increasing the file size considerably. Compared to an Excel file (xls, xlsx) the size of a file saved in the Office 2003 XML file format can easily be tenfold.

One potential way to get around this limitation is to zip up your import files. You can read the requirements for the zip file contents here, but in general your everyday import files should be zip compatible without any extra tricks. This is actually how the multi-entity data file imports are also handled, as there will only be the possibility of uploading a single file into the Import Wizard to be processed, so you’ll need to package your import files into a zip archive.

In addition to the source data files, you can also include attachments into a zipped import file. Yes, the Import Wizard does support attachment import as well. You’ll need to be careful with the data structure, so have a look at this article for specifications on how to Import attachments with notes. Keep in mind that the 8 MB file size limit does still apply here, so a large number of big file attachments may not be a fun task to perform throug the Import Wizard.

Field and value mapping made easier

If you are working with a data set that contains several picklists with lots and lots of values, mapping them could potentially consume a lot of your time. The first thing you want to make sure is that there are matching values in the CRM picklist fields (nowadays known as option sets) for all the distinct values available in your source columns. Auto mapping will do the heavy lifting for you and match the source and target values as long as they are identical in both. One thing you may not initially notice while mapping the data, though, is that the Import Wizard will also automatically append the list of values in the option sets if it encounters new source values. While it sounds like a neat feature, this may mean you end up with an unexpected set of values, duplicates with slight differences in spelling, breaking workflows or plugins due to mismatch of value ID’s etc. In my opinion, it’s much better to plan ahead and be in the driver’s seat of how your CRM is customized, even if the Wizard offers powerful but dangerous new features that can extend the schema with new fields or even entities.

When you’re working with development, test/QA and production environments, performing the same data mapping procedures time and time again could quickly become a very tedious task. Not only that, but the chances of making a mistake in the process of mapping the fields and values becomes ever more likely if you have to repeat a manual task like that. Luckily Dynamics CRM allows you to save your data maps after you create them (and before you start the actual import job), so be sure to take advantage of this feature. Of course, saving your data map into a test server won’t provide you with that data once you move to production. That’s where the export/import feature of data maps comes in handy. Just create your field mapping once and then take it with you to the next organization you’re working on.

More handy improvements in the Wizard

There used to be limitations on some of the entity fields which you weren’t allowed to update in previous versions. A common pain point was the inability to directly set the record owner, so you had to import this information in a temporary field and perform bulk updates on the records after the import. This limitation has now been removed and you’re free to assign the records directly to users or even teams.

Another caveat of the Import Wizard was that you weren’t allowed to set the state of the imported records, meaning you couldn’t easily import inactive records for historical purposes. Well, now you can, so no more need to leave out information on past activities with your customers, just because you don’t want to re-send all your emails to get them appear as closed activities. Just set your activity status as completed, import opportunities as won/lost or whatever status it is you require.

One thing to note while importing records is that the status change will actually take place after record creation. Why is this important? Well, the closing event will trigger workflows you may have in the target system. Also with the new Activity Feeds functionality introduced in the Q4 2011 update, there’s a chance you may have activity feeds rules in place that will spam your import actions all over the personal wall of your CRM users. Since no one wants to see hundreds of “activity X closed” notifications in their activity feed, be sure to remember to deactivate all rules which could wreak havoc on your brand new internal collaboration channel.

How about update existing?

While creating new records with the CRM 2011 Import Wizard is supported, updating existing records isn’t. In case you would like to only import some new fields for existing customers, by using an identifier field like email address or customer number to locate the records to be updated inside the CRM database, you’ll need to look for alternatives to the Wizard.

It is supported to perform an “export for import” extraction of data from Advanced Find that provides you an Excel sheet you can import back to update records (by selecting “make this data available for re-importing by including required column headings” option). However, unless you’re willing to dump all your records into this Excel and then match them against your import file with your custom ID field by using a tool like Access, to get the corresponding GUID’s, this won’t be the tool you are looking for.

I guess you could also create a temporary child entity for the target entity, then import new records here with the required lookup reference linking them to the parent, followed by a set of workflow magic that would transfer the required values from child to parent. It all depends on how much effort you’re willing to put in working with the out-of-the-box data import tool.

Beyond the Import Wizard: ISV solutions

There will always be many data migration needs that simply cannot be covered with a wizard like application, no matter how much Microsoft would improve the feature set of the Dynamics CRM Import Wizard. At some point it would have so many parameters and options that it would no longer resemble a wizard at all. Since the out-of-the-box functionality has to remain approachable for the “normal” user who just wants to get a simple Excel list uploaded into the system, I’m pretty certain that the market for 3rd party solutions is not going to go away anytime soon.

Instead of rooting for one particular vendor, I’m going to provide a list of the data import solutions that I’m aware of and let you evaluate which one best fits your needs.

• Scribe Insight
• InaPlex Inaport
• CRM Extensions Import Manager
• Starfish ETL
• eOne SmartConnect
• QuickBix Integration Suite
• …and many more

As further reading, I recommend everyone to have a look at this awesome article by Joel Lindstrom on Lessons Learned Migrating Data to Microsoft Dynamics CRM 2011. In it Joel lists the most important gotchas to be aware of before starting a data migration to Dynamics CRM, even when using a 3rd party tool like Scribe, such as activityparty data handling, record status, removed users etc.