A guide to help you determine the best solutions for you.
Extracting Data from Outlook for Dynamics CRM Data Migration
So you are moving to Microsoft Dynamics CRM, but you don’t want to lose all of your legacy data surrounding contacts, accounts, sales leads, or other business data. If you are replacing another system with a relational database, migrating data can be fairly straightforward—use an integration tool like Scribe Insight to import data from the legacy system, extract data to flat files and import with the CRM Data Migration Manager, or use the SDK to import directly through the CRM web services.
But what if your data is not in a relational database? Many small to mid-sized companies find that they have data stored in multiple spreadsheets or Outlook PST files. These cases present extra challenges because unlike migrating data from one source, migrating data from multiple little sources is not a straightforward process.
Some of the challenges include accessibility of data, data quality, and duplication.
For example, Acme company has a bunch of legacy data stored in Outlook PST files. They want to bring the data in to Microsoft CRM. What are their options?
You could just track them in CRM. Given that Microsoft Dynamics CRM for Outlook integrates CRM with Outlook, one option is to just have users track their old emails/appointments/contacts in CRM; however, in most cases, this is usually not the best option. Only a subset of contact fields synchronize from Outlook to CRM. If any fields like category or any custom fields are used in Outlook, these values will not be tracked to CRM. If you have a large number of users, most likely some of them will include the same contacts in their Outlook Contacts. If all users track all of their contacts, contacts will be duplicated in CRM, and potentially the more recent e-mail or phone number may get overwritten by an older version.
In most situations, it is preferable to extract all Outlook data, clean/de-duplicate it in Excel or SQL Server, then import the scrubbed data in to CRM. This is sometimes easier said than done. There are several ways to do it:
1. Outlook import/export wizard
Outlook includes a standard Import/export wizard, which enables exporting of data to flat files. In Outlook 2010, it is somewhat hidden—go to the File tab of the ribbon, then under the “Open” ribbon you will find an “Import” option. This also does exports.
From the import/export wizard, you can export Outlook data to csv, txt or other formats. The major limitation of the export wizard is that it will not export custom fields. If your data is only using standard fields, it works great, but if your data in Outlook uses any custom (user-defined) fields, it will not be sufficient.
If you read the CEI blog, you will see that we strongly recommend Scribe for CRM integrations and migrations. It has a fantastic CRM adapter and works with just about any data source for very painless integrations and migrations, and Scribe has an Outlook/Exchange adapter that can extract data from PST files.
However, the Scribe adapter for Outlook requires Microsoft Collaboration Data Objects to connect to Outlook and Exchange. CDO was a standard component of Microsoft Outlook through Outlook 2003. Starting with Outlook 2007, they took it out of the product but still made it available as a download. With Outlook 2010 CDO is no longer supported. The installer for CDO will not run if you have Outlook 2010 installed. So the Scribe option is good if you have Outlook 2007, but is not an option if you are on 2010. Scribe is still probably the best option for bringing in the data to CRM once you have it extracted from Outlook, but in this case, not the best option for the data extraction.
3. Copy and paste to Excel
If you are looking at your Outlook data in a tabular view, you can select multiple records and copy then paste in to Excel. This can be a good option for small data sets, especially contacts, where most fields contain just one line of data. When you paste it in Excel, it typically will paste neatly.
The limitations of this approach are that it can be very tedious if you have large data sets in Outlook. If someone has 200,000 contacts, you won’t be able to easily copy and paste all of them in one step. Also, if you are copying data, like the email body field, that have multiple rows of data, when you paste to Excel, the multiple rows will not neatly paste into the spreadsheet columns.
4. Microsoft Access
This falls in to the category of something I used to know. Not having worked with Access for years, I forgot that Access is the only Microsoft Office application that can import data directly from Outlook PST files. In Access 2010, go to the “External Data” tab and select More—>Outlook Folders.
You can then select any folder from Outlook and import as a table in an Access database, from which you can clean your data prior to importing to Dynamics CRM. I’ve found that this option works great in most cases, but may have some issues with exceptionally large pst files or accessing pst files that are not stores on the local pc.
This is a fantastic free application that makes exporting Outlook PST data very easy. It can extract all types of data, including custom fields to CSV files, and gives full control of what columns get extracted.
I found that the options for exporting email party and date fields especially useful
Date fields are available as date/time, date only, or time only. Email parties, suc
h as From, To, CC offer both the address or the display name. This granularity can help to pre-parse the data in a format that will be more convenient to import to CRM.
So the answer to the question “How do we get our legacy data from Outlook?” is “It depends . . . “ If your data is simple data in small quantities, 1-3 may work, but if you have custom fields or large PST files, you will want to try option 4 or 5.