Using Excel to easily update Dynamics CRM 2011 records

January 22, 2012 1 comment

A number of my clients have asked for an easier method to perform mass updates of records in CRM.  If you’re updating a couple of records it’s easy enough to individually open them and make modifications but it can be time consuming to update large amounts of records using this method.  The out of the box bulk edit feature works great if you’re updating a specific value for multiple records but what about when you need to update a multitude of data for a group of records?  By leveraging the export to Excel function in Dynamics CRM 2011 you can easily do just that and save a lot of time compared to updating individual records.

The following is a step by step example of how you can use the export to Excel function in CRM to update records via Excel 2010 and reimport the changes back into CRM.

Start off by opening up CRM and by browsing to the record type you wish to edit.  For this example we’ll use Accounts.

Next, select the view of records you wish to export.  When selecting a view ensure that all fields you wish to edit are included as those will be the fields exported to Excel.  If necessary, you can select the ‘Create Personal View’ option from the view dropdown and create a new view.

If you choose to create a personal view the following window will open.  Select the ‘Edit Columns’ button from the ribbon.

The Edit Columns window will then open.  Select the ‘Add Columns’ button from the right nav bar.

Select the individual columns you wish to include in the view.  When complete click ‘OK’ to close the Add Columns window and ‘OK’ to close the Edit Columns window.

Click on the ‘Save’ button in the ribbon and when prompted, input a name for the view and click ‘OK’.

Browse back to ‘ Accounts’ and select the view you just created.  Then click on the ‘Export to Excel’ button in the ribbon.

The ‘Export Data to Excel’ window will open.  If you have a view with records that exceed more than one page select the option for ‘Static worksheet with records from all pages in the current view’, otherwise select ‘Static worksheet with records from this page’.  Tick the option to ‘Make this data available for re-importing by including required column headings’ and click the ‘Export’ button.

When prompted save and open the file.

The file will open in Excel and  then you can complete all desired changes to the data.

You’ll notice that option set fields in CRM will have a dropdown list of values to select from in Excel.  When complete, save the Excel document.

When you select  “Save” you’ll be prompted to keep the file in the XML Spreadsheet 2003 format. Select ‘Yes’.

Open CRM again and select the ‘Import Data’ button from the ribbon.

The Import Data Wizard window will open; use the ‘Browse’ button to select the Excel file for import.  When complete click the ‘Next’ button.

In the next window select whether you wish to allow duplicates upon reimport and select  the default owner for any new records.  If you only updated records in Excel and didn’t add any new records to leave the defaults and click on the ‘Submit’ button.

Congratulations!  Your data has been submitted for import.  If you wish to monitor the import click on the ‘Imports’ link.

The My Imports view will open and you’ll be able to see the submitted import and whether the data was successfully updated.  Doub-click on the import to see details including any errors.

The detail view for the import will allow you to see all updated or created records.  Click on the ‘Failures’ item in the left nav bar to see any records that were not imported successfully.

Finally, browse back to the exported view in CRM and validate the data was updated successfully.

And that’s it! The process is pretty straightforward. There are some limitations regarding what you can update using this process. Here’s what I consider to be the major ones:

–        You can update lookup fields to other entities but the records need to already exist in CRM, you can’t create related entities via the import process.

–        You cannot relate records to other entities that have a ‘one to many’ or ‘many to many’ relationship with the entity whose records you’re modifying.  For example, using the account scenario above you couldn’t relate sub-accounts or create connections to the account records.

–        There are certain fields in CRM you cannot modify by default, e.g. the ‘Created On’ field, and this does not change when editing records in Excel and re-importing.

Overall the ability to update CRM records in Excel is an excellent new feature in CRM 2011 that should save users a lot of time when large amounts of data need to be updated.

Thanks,

Andrew Jeffers

Advertisements