CRM 2011 Data Import Wizard in practice

13 comments

Posted on 8th November 2011 by Jukka Niiranen in Tips

, , , ,

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.

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.

13 Comments
  1. upen waghela says:

    Hi,
    Is there any way to import long list of option set values in to newly created option set ? e.g. Countrylist option set.

    regards,

    upen.

    16th November 2011 at 15:29

  2. Jukka Niiranen says:

    I haven’t come across a tool yet that would allow you to import values into option sets. The SDK does support this (http://msdn.microsoft.com/en-us/library/gg509070.aspx), but you’ll need a bit of code to get the data in. Another option would be to directly update the solution XML file to add the values. There was an XML generator for CRM 3.0 and 4.0 (http://www.beatnik.at/picklist_for_mscrm.php), but I haven’t seen a version for 2011. Ideally Microsoft should of course add proper support for entering a large number of values through the customization UI, since the current editor available is even worse than the old one (see related Microsoft Connect item and vote for it: https://connect.microsoft.com/dynamicssuggestions/feedback/details/682675/option-sets)

    Of course one way to import the option set values is to simply let the Import Data Wizard generate the values for you (up to 400 values: http://rc.crm.dynamics.com/rc/2011/en-us/online/5.1_CTP/Help/ug_idw_type_list.htm). While I personally would prefer to keep metadata definition separate from data import, I guess you could just create a dummy import file with one line for each option set value needed, run it into the system and then delete the imported data while keeping he resulting option set values from the import job.

    Update 2012-06-16: There’s now a free tool available on CodePlex, called CrmXpress OptionSet Manager For Microsoft Dynamics CRM 2011, that allows you to import option set values.

    16th November 2011 at 19:50

  3. Tony Bridgeman says:

    We have a product that effortlessly migrates data from 4.0 to 2011 in the cloud – The Product is called ECHO see http://www.synchronicity-systems.co.uk/echo

    5th March 2012 at 19:14

  4. Tristan Newton says:

    When using the entity maps to import multi entities is there a way to control the order in which these entities appear in the import? Example would be:
    Entity A and Entity B
    Entity B has a many-1 relationship to Entity A – as such when importing i require Entity A to be created prior to Entity B. But when creating the custom map CRM decides B willbe imported first. Is there a way of changing this??

    Hope this makes sense!!

    21st March 2012 at 15:37

  5. Jukka Niiranen says:

    I haven’t tested this myself, but I remember reading from somewhere that the import wizard will actually make multiple passes through the data during the import process. This would mean that, for example, importing both account data and contact data in the same file should still allow you to populate the Primary Contact field on an account record, regardless of the fact that there are references both ways (from contact to parent account, from account to primary contact). So, the order in which the records for each entity are created shouldn’t matter, in theory.

    21st March 2012 at 16:19

  6. Tristan Newton says:

    Thanks for the feedback, I have tried it out a few different ways and CRM instists on Re-ordering the Entity Map into an order that doesnt meet my input order requirement! On the data import i receive errors indicating that the child entities cannot resolve their parents.

    To rectify this, I am importing the first 2 entities in the first import job and the third entity in the second job; this has worked ok. Ideally i would like to do them all in one go but time constraints prevent any more digging around!! If I do find a way I will let you know!

    22nd March 2012 at 17:19

  7. yung fei says:

    hi, I always failed if I upload more than 60 line item…. Can you give the solution?Why my import file can’t more than 60 line….

    21st November 2012 at 08:35

  8. Jukka Niiranen says:

    Yung, are you experiencing this with different source data and different entities? I’d recommend checking the source files for any irregular characters, or trying to export existing records and then re-import them to test if it really stops at the 60th line regardless of the source data.

    22nd November 2012 at 23:08

  9. Ruth says:

    Hi Jukka,

    Do you provide, or would you be able to recommend consulting services for MS CRM 2011 implementation?

    I am unsure if there are many MS Partners that just claim to be competent, and aren’t or if they are all just so busy. I am having a hard time finding one that can meet my company’s needs.

    20th December 2012 at 16:57

  10. Jukka Niiranen says:

    Hi Ruth,

    We do provide Dynamics CRM consulting services at CodeBakers, but mainly for customers established in Finland. I’ll send you a private email, perhaps I can help you in finding the right kind of expertise for your CRM implementation.

    20th December 2012 at 23:01

  11. How to Import Primary Contacts into Microsoft Dynamics CRM | Surviving CRM says:

    [...] may initially seem possible. I’ve covered some of these features in a previous article called CRM 2011 Data Import Wizard in Practice. Among these capabilities is the possibility of importing records to different entities that have [...]

    9th July 2013 at 17:25

  12. James Houck says:

    I have been using the import wizard and having success, as well as the export for re-import function. What I’m wondering is if there is a way to create a live connection in the exported Excel spreadsheet that would perform the look-up function with my CRM database for records like Contacts and Accounts? This would be the same ideas as typing a contact name into an entity within CRM and having the contact name auto-populate as you type or give you the option to click the look-up icon and search the database. What I’m ultimately trying to get around is that I may have contacts with the same name, and when importing the Excel template, the records with look-up boxes that cannot be resolved, fail. I am also aware that I can use CRM ID numbers and map those to the contact/account fields, but I would still really benefit from a ‘live look-up feed’ within my Excel spreadsheet. Thanks.

    12th May 2014 at 17:30

  13. Jukka Niiranen says:

    I can’t think of a way how such a feature could easily be added for the Excel sheets exported from CRM, unless you would develop an actual Excel add-in that would retrieve the data from CRM on demand and present a custom dialog there.

    12th May 2014 at 18:58

Leave a comment