Saturday, September 22, 2012

Import Tricks For Dynamics CRM 2011

One of my most popular posts is from four years ago, Import Tricks For Dynamics 3.0 and 4.0. Having just done another multi-lingual import for a client, I thought this is a good time to update with my latest discoveries and tricks.

This blog is NOT a review of the features of the import tool as this has already been covered in multiple places elsewhere. This is a few tips and tricks when working with the import tool to make your life a little easier.

Templates and Lookups

A lot of work was done with the Import Wizard in 2011 and it is greatly improved. Back in version 4, there were two ways of getting data into CRM: the Import Wizard and the Data Migration Manager. The version 4 Import Wizard was not as powerful but much easier to use. The Data Migration Manager was a little unfriendly but potentially quite powerful (or so I was told, I always managed to get away with using the version 4 Import Wizard). In 2011, there is no longer a Data Migration Manager but the power of the Import Wizard has greatly improved.

You can now import practically any entity and CRM will provide you an Excel-compatible XML template to use which tells you handy things like field lengths and which fields are mandatory. You can also import lookup fields and link off any unique value you like. For details of both of these features check out this previous blog post of mine.

One thing that I will add to my previous post is that, while you can often click the Import Data down triangle and get a template, the behaviour is not always completely consistent.

image

Therefore, if you are having troubles accessing a template for a specific entity, you can always go to Settings – Data Management – Templates For Data Import and download any possible template from there.

image

Information That Cannot Be Imported

Unfortunately it is still not possible to import non-printable characters such as carriage returns and line feeds and these must be stripped out of things like the description text before importing. In my most recent import, the data came from an Oracle source and had SYN signal characters through it which caused no end of grief until they were purged. My old Import Tricks For Dynamics 3.0 and 4.0 post has an Excel formula for removing such characters, once you have identified the column, if this is causing you problems.

While you can now bring in the Owner of the records, you cannot bring in Inactive records (***STOP PRESS*** See Jukka’s comment below for how to work around this). While the template and mapping allow you to select ‘Inactive’ as an option for the Status Reason, the records will fail on import as the Import Wizard assumes all records have a Status of Active and the combination of an Active Status and an Inactive Status Reason is forbidden. My workaround was to create a new Status Reason of ‘Inactive_Temp’ for the Active Status. I also created a workflow which, when a record is created, checks the Status Reason and, if it is ‘Inactive_Temp’, deactivates the record with the Change Status step. This completely automated the importing of inactive records once I had adjusted the mapping to import records with an Inactive Status Reason to be ‘Inactive_Temp’ in CRM.

Another thing that cannot be imported are relationships across N:N links. There is, however, a codeplex project by L33t coder and fellow CRM MVP Andrii Butenko (a33ik) which promises to get around this. I am yet to try it but I do have this requirement on another project so it may come in handy very soon.

The Old Chicken and Egg Problem

One of the problems I mentioned in my previous post was that Accounts and Contacts have a 1:N relationship with each other through the Parent Customer lookup on the Contact and the Primary Contact lookup on the Account. This always caused problems as, with the version 4 Import Wizard, you could link one but not the other. I am now told you can link both (I did not need to do it on this project). The trick is to zip both the Contact source file and the Account source file together and point the Import Wizard to this zip file.

There is also a system mapping for ‘Generic Contact and Account Data’. While I had some difficulties using this (but in the end did not need to) the idea is if you have a big spreadsheet of Contacts, you can use this as a source file to create both the Accounts and the Contacts at once. While untested, I expect if this had a Primary Contact column in it, CRM would do the right thing linking Contacts to Accounts and also populating the Primary Contact field.

Unicode Data

As with version 4, you can still import unicode data, allowing you to populate fields with Chinese, Korean, Japanese or any other unicode text. The xml templates generated by CRM are unicode compliant BUT Excel is really bad at dealing with unicode files. If you plan to save your xml file as, say, CSV, Excel will make this a non-unicode compliant CSV file. The only option you can save the xml file to in Excel and preserve the unicode data is ‘Unicode Text’ which is a tab delimited text file (which the CRM Import Wizard is happy to deal with and was the source format for most of my imports).

One ‘feature’ of Excel 2010 which was very embarrassing was if you double-click a unicode-compliant CSV file generated elsewhere, Excel opens it but strips out the encapsulating double quotes on the first column. The upshot is if the text in the first column contains a comma, this shifts that row’s data out of sync. This was embarrassing in that I was accusing the client of not encapsulating the data properly when it was simply Excel screwing it up. What is more bizarre is that if you open Excel and click File-Open… to access the file (rather than double-clicking the file in the folder) it treats the unicode CSV file properly and does not strip out the encapsulating quotes.

Size Limits

While most of the news with the 2011 Import Wizard is good, one very frustrating feature of the new tool is the source file size limit. Your source files cannot be larger than eight megabytes. This limit is really frustrating, especially with unicode files. You have no choice but to split the files into sets of, say 10,000 records and queue them up for importing. In my case I was only importing in about 100,000 records so while a little annoying it was not a show-stopper.

Here are a few tips for getting your files down below the eight megabyte limit:

  • The XML file, given it contains a lot of metadata about the source fields, is larger than a CSV or Unicode TXT file. Therefore, before importing, try saving your source file in a ‘simpler’ format
  • Delete empty columns as this can save a few hundred kilobytes from the file

Error Export Bug

There is a great feature in the Import Wizard where you can export the rows which failed to import. CRM will package these rows up ready for re-importing once you have sorted out the issue.

image

However, the feature has a bug. In my case one row had caused an error due to a lookup failure. I exported and noticed all the data had shifted relative to the column headers. My culprit was the first column which was the salutation field and this particular record had an empty salutation. Therefore, my suggestion is to ensure whichever column you have first in your source file, make sure it is always populated e.g. the Last Name field for Contacts. Otherwise there is the risk that the data will be corrupted i.e. shifted if you Export Error Rows.

Similarly, I did see occasion where the Import Wizard got confused, when reviewing the import file, by the number of columns because the last column was not completely populated (something that also used to happen in version 4). So I would also recommend the last column in your source file be always populated. Status or Status Reason are good fields for this.

Slowness of Importing

Any tool used to import data into CRM, if it is using supported methods, needs to go through the web service layer. Unfortunately, the web service layer can only insert one record at a time causing a bit of a bottleneck for mass imports. Discussions on the web indicate the best you can hope for is about 50 records per second. If validations come into play or you have a slower server, this can go down to 10 records per second or lower. In my case, depending on server load, I was getting as low as 3-4 records per second in some cases.

I know no way of improving this in any significant way, other than using unsupported import methods i.e. direct SQL writes which I would not recommend given the potential for long-term disaster. It is simply something to be aware of and plan for.

Conclusions

Overall, the 2011 Import Wizard is a great tool and a huge improvement over its predecessor. However, like the previous version, it also has a few peccadillos which can cause headache if you are not aware of them. My hope is, with the above, you can get your data into CRM and avoid too many problems.

18 comments:

Jukka Niiranen said...

Great tips once again! One thing I'd want to point out is that the Import Wizard does in fact allow you to import also inactive records. Yes, the XML import template does not by default have the Status column, but you can add it there and set the value (of course it will need to be compatible with the Status Reason value provided in the same file).

One caveat of this import is that the system will initially create the records as active and only later change their status to inactive (completed, cancelled etc.). Any business logic that you may have configured in the system for these status changes will therefore fire up, as can be seen in one of the screenshots posted on my blog post about CRM 2011 Data Import Wizard in Practice: http://niiranen.eu/crm/2011/11/crm-2011-data-import-wizard-in-practice/

Leon Tribe said...

Thanks for the tip on the active records and the link to your comprehensive article (and for the awesome Apple commentary on Google+ :)).

One thing you mentioned was the 'two sweeps' the Import Wizard does with inactive records. I have noticed the same thing happens with parenting of records such as Contacts and Accounts; the records get brought in without a parent and then a second sweep adds the parent in.

We had some curious Scribe behaviour which I suspected was due to this but we are still investigating.

Ankit Shah said...

Hi Leon,
Thanks for the helpful tips.

I would also like to share one of my experiences related to import wizard. This wizard does not allow us to bulk update inactive/deactivated records. Say, the completed phone calls activities will never be changed via bulk update, you first need to change the status as open (via workflow or web service call)& then perform the bulk update for the columns that you either missed or imported incorrectly in first import. Is there any workaround to update completed activities or deactivated records?

Leon Tribe said...

Hi Ankit,

I'm not sure I quite understand what you're trying to do but if you are looking to mass-change the status of activities, you could try an on-demand workflow using the change status step.

Ankit Shah said...

Hi Leon,

Thank you for a quick response.

Let me give you an example. We need to migrate around 200,000 phone calls activities & all should be migrated as completed activities. We completed the migration via import wizard & everything was migrated as per expectation. Now, customer tell us that they would like to set one boolean field as true & not false though previously they decided to keep it as false. At this time we cannot export the migrated records to perform bulk update for that particular field in batch of 10,000 records. Because, import wizard cannot update the completed activities. So, for that we first need to change the status from completed to open, then update the field via bulk update & finally change them back to as completed. As you said we could configure on demand workflow that allows us to update 250 max (Dynamics CRM online) to update the status of phone call activities from completed to open & vice versa. If we are not using any migration tool then is there any workaround to update these completed phone call records?

Leon Tribe said...

Hi Ankit,

If you do an Advanced Find on a specific activity type i.e. Phone Call and list all completed records, you can export for enrichment. However, I had some troubles getting this to work on a demo online instance with a completed Phone Call record.

If may be a case of delete with the bulk deletion tool and reimporting, unfortunately.

Leon Tribe said...

For on-premise implementations, Microsoft have now released a SQL update to increase the 8M file limit to 32M. This has been a godsend for the project I'm currently on. for details, read about it here:

http://blogs.msdn.com/b/emeadcrmsupport/archive/2012/10/05/default-file-size-limit-for-the-crm-2011-data-import-wizard-and-how-to-modify-it.aspx

Gordon Brown said...

I apologise asI'm a newbie on this,- grateful if you canpiut mie on right track, looks like I'm close?

- I have existing accounts that I want to enrich with data from an excel spreadsheet.
I simply want to:

a) import a database - it will have three columns A, B,C.

b) IF CRM finds a match of data in column A, then it adds the data in Column B & C (overwriting if anything there)

c) If no match - ignore.

d) tell me what its done.....

Hope you can help a layman!

Thanks

Leon Tribe said...

Hi Gordon,

See if this helps. If not, let me know :)

http://leontribe.blogspot.com.au/2012/10/an-alternative-method-for-data.html

webprogramming said...

Hi, Why am I can import only 60 line data....when I mport more thean 60 the next line over 60 is failed....That is very trouble if I want to import a lot of data....any solution?

Unknown said...

Hi Leon,

I have been following your posts recently given that I have been having many similar problems to what you have been blogging about and I have a quick question for you.

I am currently trying to reimport my Account and Contact information to CRM 2011 but I have been stuck on this for a while.

I have zipped my accounts and contacts together as a .csv file, but for some reason the Account file is coming up as corrupt and I get the following error window:

"Error in unzip. The .zip or .cab file could not be uploaded because the file is corrupted or does not contain valid importable files."

To see which of the files the problem lay in I tried to launch the Import Wizard with the unzipped .csv files seperately. Accounts will not upload to the system. I have not touched any of the information except for deleting columns with nothing in them, and I have also converted my original Salesforce data file for accounts to csv without deleting anything and it comes up the same.

Any ideas as to why the file would now come up as corrupt when it didn't before, and also would you have any ideas on how to find what is causing the corruption preventing the upload?

I would really appreciate your help in this as you seem to have a great knowledge base for this topic! :D

Leon Tribe said...

Hi Dee,

Contact me via my email address (in the About Me section) and we can see if we can work it out.

Leon

Leon Tribe said...

Webprogramming: My guess is there is a problem with your source file around the 59-61 line mark. My v4 and 2011 import tips articles may help

Leon Tribe said...

Webprogramming: My guess is there is a problem with your source file around the 59-61 line mark. My v4 and 2011 import tips articles may help

Chrizzzie said...

Hi Leon,

I'm currently struggeling with using the bulk importer for creating campaign responses. It doesn't recognize the contactperson. The format we use is "Tribe, Leon". I first thought it might have to do with the comma but this doesn't seem to be it. Do you maybe know whay causes this problem?

Leon Tribe said...

Hi Chrizzzie,

My guess is the format in CRM is different to this. You may need to change it to look like 'Leon Tribe' rather than 'Tribe, Leon'

Unknown said...

Hi Leon.

Great Post. I am running into an issue where it seems to be stuck on "uploading {text filename}". My file is 933 KB; under the 8 MB. You mentioned that it could take 10 seconds or more for 50 records. It hasn't gotten to the point where it lets me choose the entity and then verify the mapping. My question is; How can I tell that it is still working and running super slow? I see nothing in the system jobs at this time. I have access to the backend SQL tables. I haven't been to determine if there is a table where I can see how many records it has read it? Is there one? Any guidance on this would be great!

Theresa

Leon Tribe said...

Hi Theresa,

Apologies for the delay. If the file is getting stuck on the initial upload, it could be a badly formatted file.

If it is a csv, try opening it in notepad or wordpad and look for something wrong, like a blank column title.

Otherwise, try splitting the file and see how that works. It could be a corruption in the data so splitting the file will help isolate the issue.