How to update CRM data from Excel

With CRM 4.0 it is possible to export data to Excel.
A much heard question is if it’s also possible to re-import changed data from Excel into CRM. That means an update of the existing data.

The next steps will explain how to update the data in the mobile phone field for the contact entity. Only using the default import functionality of CRM.

Step 1: Export and edit existing data
The easiest way to obtain the necessary data is to use Advanced Find. Select the contacts you want to edit the mobile phone numbers using Advanced Find and choose to export to Excel.

   Figure: all contacts have same mobile phone number.

When exporting, use the option ‘Dynamic Worksheet’.

 
   Figure: choose Dynamic worksheet.

Using a Dynamic worksheet export, causes the CRM GUIDs to be exported as well. This is a hidden column which can easily be unhidden. Just select the columns, rightclick  and choose unhide.

   Figure: unhide CRM GUIDs

Make sure that the column with the GUIDs becomes the first column in the sheet and rename the column header to the RM entity’s single name, i.e. Contact.

Edit the data in the column you want to update and remove unchanged columns.

  Figure: Edited data, GUIDs first 

When all necessary data is changed, save the file as a .csv-file.

Step 2: Import Data
With the standard import the .csv-file can be imported in MS CRM.

  Figure: import data

Select the file and set the correct field delimiters.

  Figure: set field delimiters 

CRM will recognize that an existing entity is imported and will show an automatic Data map. It will also show a field you normally don’t see when importing data “Enrich data by updating existing records rather than creating new records”. This field is already selected.  

When clicking Next, there is a screen where duplicate check options can be set (which will not be there as every GUID is unique). Clicking through the screen will start the import. 
When the import has completed, the CRM records will be updated.

  Figuur  - The contacts have a new mobile phone number.

Conclusion: with het steps mentioned above it is possible to export-update-import the data from MS CRM to Excel and back without any third party tools.

So does this work for every field? Unfortunately the answer is: No. The mobile phone field example used is a free text field (type: nvarchar). I haven’t tried all of the field types available in CRM, but lookup fields for example I have not been able to update. So depending on the field you will or will not be able to update. Tip: try your business case in your test environment first!

Happy updating!