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!

What About…. ExAMining MSCRM??

Welcome to my new Blog. Clearly it’s dedicated to Microsoft Dynamics CRM. But what about ‘ExAMining’? Over the last couple of years I have seen lots of blogs about MS CRM. And lots of them I still follow with much interest. But still I believe there is room on the web for a blog that has something to add to the information available already. This is what this blog is about: Examples And More… on Microsoft CRM.

So why would this add anything to the information already available? Well, to my experience one part of the blogs about MS CRM contains general information, news facts and business stories and another part is more technical and contains javascripts and other types of ‘code’. Now the aim of this blog is to give practical examples of how MS CRM can be customized in a way to make life easier for end users. It will contain (a lot of) javascript, but by giving examples on how to use it in everyday customizations I’ll try to show the practical use of the code used.

Why this explanation in advanced you might think. Well, that’s the good news: I hope my examples inspire you to create your own use. And as a result return the favor with posting your example. So in the end.. we all benefit!