Importing Data into Microsoft Dynamics CRM – the Native Import Tool vs. Scribe
I’ve recently been assigned many different data import tasks. For me, one of the most difficult decisions is what tool to import the data with; the out of the box Import Wizard, Scribe, Direct SQL imports, a custom C# tool? I knew that the most recent version of Microsoft Dynamics CRM had an improved out-of-the-box native import tool, but wondered how it compares to the other well-known tools. During this article I will discuss my experience importing data using the native data import tool and scribe.
Using the Native Import Tool
Using the out-of-the-box CRM data import tool I encountered a series of challenges. Immediately I noticed the contacts name was in one field, first name, last name, middle name, and of course “nick names” in quotes. The second column in the file was a text field with several skills separated by commas that were supposed to be child record for each skill. Using the Text to Columns feature in excel, I separated the name and skills into individual columns.
Challenge 1: Could not create a custom parent record and use it as a lookup
I tried to use the out of the box import tool and ran into a few problems. First, I wanted to look up the parent customer by name, and if more than one was found, then I wanted to lookup by name AND city. I knew there were hundreds of accounts with the same name, but none had both the same name and the same city.
The next limitation I ran into was that I couldn’t import to child record. This required me to ignore those skills columns that I just separated.
Challenge 2: Could not have multiple lookup methods for the parent customer
One thing that was really nice about the native CRM import tool is that it allowed me to make a new field on contact right from the import wizard using the value from the source file. The problem was that this field needed to be a lookup and also needed to create the parent record during the import as well. Unfortunately, all of this was not possible using the native CRM import tool.
Challenge 3: Could not import a child record in the same import file
The next limitation I encountered with this import tool was in regards to a boolean field on the contact. This required me to first add a column to the cvs first and set all the values to “yes.”
Challenge 4: Could not update the parent customer’s address
The next limitation was that I couldn’t update the parent customer’s address from the same import file. So I had to set up two imports prior to the contact import, one to create the custom lookup field, the other to update the parent customer’s address. Then three more imports for the child skill sets. As I was mentally preparing for a 3 hour task to import a few hundred contacts, I received my favorite error in the world.
Challenge 5: Unable to test or run the imports
Once this happens, you cannot go back and change any column mappings, cannot retry, and cannot save the data map. All you can do is cancel and start over from the beginning. This is the point I gave up with the import tool and went to scribe.
I was able to create logic that if there was one match on account name, then to proceed with the account update. If there was more than one matc on the name, then seek on name AND city. See steps 2 and 3 in screenshot.
I was able to create and update a parent record, see steps 1 and 4.
I was able to import a child record if required. If there was no skill, or just one or two, that wasn’t a problem. Scribe lets you set a Pre-Operation control. See steps 6-8 in screenshot.
Setting a default value for all records in the import is no problem.
And it was simple to walk through the import, to test what would happen. If there was an error, I could go back to the map, fix it, and run again.
What would have taken three hours using the import tool for 6 imports, I was able to set up and run in about 45 minutes using scribe. I’ve come to realize that for anything other than leads, scribe is the preferred tool for data imports.
Features: Native Import Tool vs. Scribe
For data import tools or assistance, please contact FMT Consultants at (760) 930-6400 or email firstname.lastname@example.org.
Senior II Developer