You may encounter some data maintenance scenarios more easily handled by using Excel to update data in Dynamics 365. Luckily, the system allows us to export existing data into Excel, make changes to the spreadsheet, and then use that same file to import updates to the data. Here are the simple steps you need to know to properly use this functionality in Dynamics 365.
Navigate to the entity you want to make edits on. If any of the current Views reflect all the columns of data you want to edit, then you can simply export that data by clicking the “Export To Excel” button in the ribbon. When you click the button, the system will export all the records in the current view into an Excel file:
If you click the drop-down arrow to the right of the “Export To Excel” button, you will get a few other options. You’ll want to use the “Static Worksheet” option if you are trying to export all records from all pages of the current view:
In this scenario, the column / field of data I want to update is not in any of the current views. So, I’m going to use Advanced Find to add the column and then export the data. We’ve added the Relationship Type field to the Contact Form and all existing Contact records reflect “Default Value” for the value of this field. I want to update the Contacts to have valid Relationship Types. So, I will need to add that field as a column in my exported Excel file:
The easiest and quickest way to add columns to views is to use Advanced Find. Click on the Advanced Find icon on the right side of the ribbon. The query dialog will default to the Entity and View you are currently using:
To Add columns: Click “Save As” to save your additions as a new Personal View before making changes. Or you can just click directly on the “Edit Columns” button to add columns for a one-time export of that version of that view. Highlight the column you’d like to add the new column to the right of, then select “Add Columns”:
Select the column you want to add and click “Ok”:
Relationship Type is now added to my view:
Click “Ok” in the Add Columns dialog box. Then click “Results” in Advanced Find:
The AF View now shows the Relationship Type column along with existing values, and I am ready to export this data:
Click on the “Data” icon. Then click the “Export Contacts” option. Note: Depending on your version and available functionality, this icon may be titled “Export To Excel” just as it does when exporting from the View:
Once the system has finished creating the file, it will populate at the bottom left corner of your view. Select it and save it locally:
You may have to enable editing first in the Excel file first before it will allow you to save. Make sure to save this as a .xlsx file type. Once you’ve opened the file for editing, you’ll see that columns A, B, and C are hidden. These contain pertinent data needed for the re-import process to locate the existing records that you intend to update. Make sure you do not modify these columns and make sure you do not inadvertently reorganize the data by sorting just the visible columns:
Update the needed columns of data and save the file:
Then navigate to Settings / Data Management / Imports:
Select Import Data in the ribbon:
Then choose the Excel file you are using to update data. Then click “Next” in this dialog:
Click “Next” on the Review File Upload Summary dialog page. If the file you are using doesn’t have any unknown fields that may need additional mapping, the below page will populate. You’ll see the message stating “this action will update existing records” if the system detects the file is set for updating, rather than creating new records, which is dependent on the file being in .xlsx format. You aren’t creating new records, but I always make sure I have “No” selected for allowing duplicates as well as a valid user selected for defaulting the owner of any newly created records.
Click Next in the Review Settings and Import Data dialog:
You should see this message populate next:
Your import will then begin processing and you will see the progress in the Imports View (press F5 to refresh for latest status).
Once the import is complete, the status reason will change to “Completed”. Errors will generate for any record that was not successfully updated via the import process:
To view the result details, open the import record by double-clicking. This will give you the details of all the records successfully Imported and also the details of any errors that were generated. Click on “Contacts Fully Imported” to see the records successfully updated / imported. You should see “Update” as the status for all the records processed successfully. This tells you the records were only updated and that the process didn’t create new records for your import:
To view the error details, click on “Failures”. The details as to why that row of data was not successfully updated will be in the Description.
In this case, this row wasn’t updated because there are two Contacts in the system with that same name, so the import process couldn’t find the correct record to update:
And I can see my updates have been made by looking at a view with Relationship Type field as a column:
Additional Notes:
- Always make sure the field / fields you are intending to update via Excel are included in the View you are exporting.
- You cannot edit data via Excel for fields that reside within related records to the entity you are updating. e.g. You cannot update the Business Phone value in the Parent Account for a Contact, if you have exported your data from the Contact entity.
- You also cannot change the status of a record using Excel. e.g. Changing the Contact from Active to Inactive status.
- Make sure you are using a freshly exported file for editing and reimporting. If the records you are trying to update have been modified AFTER your file was exported, those records will not be updated via importing the Excel file. You will see these listed under the errors in the import file.
By Jessica Smith, Dynamics 365 Application Consultant, Dyn365Pros, Microsoft Dynamics 365 Partner San Diego, Southern California
