So it is a common issue that when new CRM clients (especially CRM Online clients) come online, they have data they need to import. Well, not all of them have proper databases, or even really a budget to get a custom-developed import solution, or Scribe. Well, we've put together a budget-oriented method that can help.
First off, the most common issue is that the client has their info in just one big Excel file. Contacts, Company, Notes and maybe even Opportunities are all in one big flat file. Well obviously that won't do for CRM. However, if you just break them up into different files and upload, they aren't associated with one another. Now if you only have a few dozen contacts and companies, it might be ok to go in after the fact and set them manually, but that's not going to be a viable solution for most. So we need a way to associate all these different entities.
Fortunately, CRM makes a unique identifier for every record it creates (called a GUID), and we can use this record to tie record types together. However, to do this, you're going to need a couple tools.
1. The Microsoft CRM Data Migration Manager. This comes in both a local/hosted version and a CRM Online version. Don't get these confused, they are not compatible. Also, I very strongly recommend setting up a virtual machine to install these on, it will save you a lot of heartache in the future, and you can avoid some "gotchas" about how the DMM works on a clean VM.
2. The CRM Bulk Data Export Tool. This is Arke Systems' updated version of the Bulk Data Export Tool. The changes we made include being able to get GUIDs out of CRM, and not having to set a date limit for how much data you're getting out of CRM. If you have any trouble with the app, please post the errors, and we can take a look. It's not technically a supported app by us or by MS, but I'll try to help.
So now that we have the tools we need, let's get down to brass tacks. First things first, extract the information you need about the Account into its own CSV file. At the minimum, you'll need a Company Name. Once you have that file, go ahead and upload it to CRM via the Data Migration Manager. Once that's done, open up the Bulk Data Export Tool. Authenticate to your CRM, and then export the Accounts back out. Make sure to set in the dropdown that you want the IDs exported too. That this does is give us the GUID of every Account you just uploaded to CRM.
Below: The Bulk Export Tool. Important field highlighted in red.
Now that we have those GUIDS, we need to find a way to set those in your Contacts list (or whatever list you need to associate with those Accounts). Sure, you could just copy and paste those GUIDs next to the appropriate contact, but that would take forever. What we need is a little Excel magic. First, you're going to need to understand how to use the OFFSET and MATCH functions in Excel. Say you have an Excel document with two sheets in it (this can also work across documents). the first one has a list of all companies and the related GUIDs. The second one has a list of contact names and the companies they are part of. So on the Contacts sheet, you're going to add two more columns, Match and Account ID. On the Match column, you're going to write something like this: =MATCH(A2,Sheet1!A:A,0). What that tells me is that on Sheet1 (where the Company list resides), I want to find the relative position of the Company Name and report a row number back. On the Account ID column, you'll put something that looks like this: =OFFSET(Sheet1!$B$2,Sheet2!B2-2,0). What that means is that you want Excel to go look on Sheet1 and grab the GUID relative to the Match number you've pulled to Sheet2 (the Contacts sheet).
Below: Sheet2 and Sheet1 examples
So now you should have a list of contacts that also has a list of GUIDs under the heading Account ID. Save that file as a raw CSV, it's time to go back to the Data Migration Manager. Start another migration with your contacts, and match up fields like normal until we get to the Account ID. For this one, we're going to do things a little different. First, select the field in CRM called "Parent Customer". When you do, it should take you to another page asking you to link up this field with one in Accounts. That's good, we want that. So select the dropdown that says Account ID and continue on.
Assuming the rest of the migration goes smoothly, you should be able to go into a Contact and see that it has a Parent Customer all set. If you prefer to have the Primary Contact field in Accounts populated instead, just reverse the order of uploads. Do Contacts first, and then upload Accounts with the Primary Contact ID instead. Speaking of which, that is a limitation of this method. You will always have one standalone Entity at the beginning (normally Accounts or Contacts). I have not found a good way around that, yet.
So I hope that helps all of you that have been struggling with a simple, yet reasonably effective way of importing data to CRM on a budget. Please hit up the comments with suggestions on improving this, or any questions you have about the process!
