I regularly get requests for assistance with data migration challenges. Data migrations are never easy due to the differences in data values and formats that exist between systems. The easiest way to import data into Dynamics CRM is using the Track in CRM capabilities available within the Outlook client. But assuming your needs are to import a .csv file of leads (or other record type) the easiest method is to use the Import Wizard. To ensure you experience a successful data migration I recommend the following tips:
- Put records for each record type in a separate file.
- Put records for different owners in a separate file.
- Ensure that the first line of the file contains column headers and that they match the CRM attribute display names EXACTLY if you would like the import wizard to automatically* map the data. *Automatic mapping is required if you intend to import records that are related to another record (See #6). I HIGHLY RECOMMEND USING AUTOMATIC MAPPING EVEN IF YOU DO NOT DESIRE RELATED RECORDS.
- Make sure the first column heading is not the name of a record type.
- Make sure data exists in your source file for all CRM required fields (denoted with a red asterisk on the CRM form).
- Make sure some data exists for each column. If a column has no data delete the column.
- If your data should be related to another record (i.e. Adding Contacts to existing Accounts) make sure the column header matched the CRM lookup attribute display name. If you have two different attributes in CRM with the same display name you will receive an error (EX. if you created a custom State picklist value to replace the text version and keep the display name the same "Address 1: State/Province"). Also make sure the company name values will match a corresponding account record (i.e. contacts with a Parent Customer value of Microsoft Corp. will not be added to the CRM Account Microsoft) and that there are no duplicate Accounts in the system as the import will not be able to resolve the proper Parent Customer.
- If you are importing into a drop-down list, ensure all the values exist within CRM and there are no typographic errors and consistent capitalization in the source file.
Step by Step Example
- I am going to import the following .csv file of Contacts into CRM while still maintaining the relationship to the proper Account record (denoted in Parent Customer).
- My Account records are already loaded into CRM so my first step is to ensure the column headers EXACTLY match the CRM Contact records attribute display names.
- To ensure I get the display names EXACTLY correct I recommend you create a CRM contact view (using Advanced Find and editing the columns) with the exact same layout as the import file.
- Create a test contact, save and close and then click on the Excel icon to Export to Excel and select the radio button for Static worksheet with records from this page.
- Copy the column header row from the export and paste on top of the import file column headers. Notice the different column headers for the address fields compared to the source file. Also note the Gender column is a drop-down on the CRM Contact form. The import wizard will map the values properly based on the text display.
- The Microsoft CRM Import Wizard attempts to automatically map your data to CRM attributes using the column headers in the import file. The Import Wizard will successfully auto-map the values IF the column header EXACTLY matches the CRM attribute display name. NOTE: the attribute display name can be different than the display name on the CRM form so ensure you have the attribute display name (see below). You can also go into the entity customization area within CRM for the particular entity and verify the values for each of these attributes and then manually update your .csv file to match those values. However, I HIGHLY recommend the above method to ensure accuracy.
- Now that our data is properly prepared simply use the import wizard to import these Contact records. As the screenshot below shows the data map was able to resolve automatically.
- The resultant import demonstrating the successful import and linking of the contact to the Account (Parent Customer) record.
A majority of this data is taken from the CRM Online Help.
NOTE: The maximum size of the file that is supported by this import method is 4MB. If your source data is greater than 4MB or requires significant relationship mapping beyond the capabilities of this import tool I would recommend the Data Migration Manager or Scribe Insight.
- Eric Boocock