How to Update Dynamics 365 Records Externally Using Excel

Dominic Jarvis, 28 September 2017

When needing to update large amounts of records, you may find it easier to update these records in an external file with Excel, rather than edit them all individually in Dynamics 365. If you want to do this, here are some things to keep in mind.

Export Using Advanced Find

It’s a good idea to export your records using advanced find, with only the columns selected that you want to update in Dynamics 365. This allows you a greater degree of flexibility around which records you export due to Advanced Find’s filtering capabilities. It also helps to lessen the risk of something going wrong by only making available the columns you want to edit. This can happen with some auto-populated fields such as ‘createdon’ and ‘createdby’, which can throw validation errors when being imported back into Dynamics 365. This is due to the fact that these normally cannot be edited, and so when importing data containing these fields to update, Dynamics 365 will throw a mapping error.

Use the Original File

When wanting to reimport records back into Dynamics 365 to update existing records, it is important to update the records in the original file that you downloaded from Dynamics 365. This is because this file contains metadata that Dynamics 365 uses to identify the original records and fields that have not been updated, preventing Dynamics 365 from either creating duplicate records or updating fields that don’t need to be updated.

If you copy the data into another file, or export to another format (.csv, .txt etc.), Dynamics 365 will not recognise the entities as the same, and will create duplicates in the system. This may be caught by Duplication Detection rules, but will not update the existing entities.

Take Note of the Tooltips

When editing a selection of records in Excel, if you hover over a cell, you will get a tooltip which gives you information about the corresponding field in Dynamics 365, letting you know max field lengths, field types, and some other handy things to make sure you don’t put the wrong data type in the wrong box.

Import Using the Import Wizard

The import wizard can be found by navigating to Data Management, which is in the Settings tab, then selecting Imports. When there, click the Import Data button in the ribbon and select the data that you wish to import. As you haven’t made any format changes to the original document, you shouldn’t need to do any mapping, Dynamics 365 will handle this for you. Navigate through the wizard, and you should be good to go!