Importing tricks for Dynamics CRM 3.0 and 4.0

The import tools came a long way between version three and four but they are far from perfect. Here are a few tricks to make the process of importing using the import wizard as painless as possible.

The main tip I can offer for version 3.0 is getting the GUID of records already in CRM. For details see my other post:

http://leontribe.blogspot.com/2008/12/how-do-i-get-guid-in-crm.html

This is most commonly used for adding the parent account to contacts. The process is import your accounts, get the sheet of GUIDs and then use Excel's vlookup to insert the appropriate GUID into the Parent customer column of the contact import sheet.

For version 4.0, you no longer need the GUID as the import wizard will match off of the account name. This (rather naively) assumes the account names in the system are unique but the gain is it greatly simplifies the process of attaching contacts to their parent account. Either ensure account name uniqueness, try using the GUID or be prepared to manually attach those records which will fail.

Despite being a great improvement over its version 3.0 counterpart, the version 4.0 import wizard has a few shortfalls. The first one is you are almost certainly going to fail if you create your own data maps.

While the import wizard allows to define how the data from your source sheet gets mapped into CRM, if you choose to actually do this you are very likely to get errors on your import. The problem is manual data maps do not support mapping values to other tables without a lot of messing about. So, for example, if you are bringing contacts into CRM and you have account names in the parent customer column, this will fail with a manually created data map as the map needs to go to the account table. Even if you use the version 3.0 method of specifying the GUID it will fail.

Update: Apparently Rollup 2 has fixed this but if you haven't gone to Rollup 2 yet, the below gets around the issue.

The only way around this is to get CRM to automatically map the values for you. The only way you can get CRM to automatically create the map is to ensure the column headings in your source file match what CRM is expecting to see (and yes, capitalisation does matter). Generally speaking the values need to match the labels on the form. More accurately, the values need to be the attribute names. You'll know if it is working because when you go through the Import Wizard, the moment that you specify the entity you're trying to import, the data map value will show 'Automatic'.

If it fails and you're stumped which column value is confusing CRM, go to Data Mapping as if you're setting up a manual data map and you'll see the values it's expecting.

Another tip at this point is if your import file is sufficiently large, you will not be able to import sample data into the data mapping entity. The only way around this is to copy the first few lines of the source file and use this for your sample data.

Once you have the correct column headings and CRM is automatically mapping the import you're almost set. The only thing you need to check is that the source data is 'clean'. There are two things which commonly upset imports. The first is the last column in your source file needs to be completely populated. The order of the columns is not important to shuffle them around to make sure the last is completely populated otherwise you risk CRM not understanding the how many columns you have.

The second issue, and often most frustrating, is you cannot have line feeds or carriage returns in your source data. These are guaranteed to confuse CRM in regards to the number of columns. Finding which values actually have the offending characters can be tricky. The only tricks I know of are to run the cursor down the suspect column in Excel and if the value box at the top expands to more than one line, you have an offender. The other trick is to open the source file in notepad or some other text editor and look for rows which start in the 'middle' of a source row. Common columns including carriage returns and line feeds are address columns and note/description columns.

Once you've identified the offending column, Excel has a formula for stripping out the bad characters called SUBSTITUTE which allows you to replace one character with another in a string. Using the formula =SUBSTITUTE(SUBSTITUTE(ORIGINAL_CELL,char(10),""),char(13),"") will strip out the hidden characters and let the import go through.

Other issues can cause problems with import, such as a value in the source file not matching the value in a picklist attribute which you're trying to import the value into but the error messages for these are generally quite self explanatory. For version 4, to see the error messages, go to Settings-Data Management-Imports-Double click on the import job of interest and look at Failures. Alternatively, you may also get insight from Settings-System Jobs where the import job will also appear. Filter on Type: Imports if there are too many entries to navigate.

One time when the error logs will be of no use to you is with importing subjects and business units. The one thing to be absolutely sure of is that any subjects or business units you import are ultimately linked to an entry in the import file or to an entry in CRM. You CANNOT import the subject or business unit at the top of the tree. If you try, it and any entries linked to it simply will not appear in CRM. They are in the tables but never surface in the client.

As an aside tip, always populate a dummy field with an import-specific value. This way if you need to delete or modify the import in some way, the records are easy to identify in an Advanced Find. This is often used if you need to assign users to the records. Certain properties such as status and owner cannot be directly imported, so setting a value and changing the records within CRM is the only way to go if you're using the import wizard (the Data Migration Manager handles this better).

Another tip if you want to bring in records with non-English characters is to use the txt-unicode format for your import file. CSV simply won't work. The wizard works exactly the same way and the data should come in without a hitch. I've done this with Japanese, Chinese and Korean records and never had a problem.

Also, if you plan to import someone's contacts from Outlook, don't. Or at least be prepared for the consequences. Other than the data often containing hidden characters, CRM will not match the new record to its original in Outlook. Therefore, if the user is using the Outlook client and contacts are being synced, they will suddenly get a duplicate of every contact in Outlook as CRM syncs down the new records to their Outlook contacts. You can delete the originals by viewing your Outlook contacts in a list format and using the different icons for synced and non-synced contacts to work out which is which.

Finally, in a few cases, as you can only import into one entity at a time, you'll hit a 'chicken and egg' problem. The most common example is with accounts and contacts. Before you can link one entity to the other, one of them must be imported into CRM first. The upshot is you can either link the account to its primary contact (by importing the contacts in first) or you can link the contact to its parent account (by importing the accounts first) but you can't do both. Apparently you can get around this with the Data Migration Manager though.