Update Data From Excel In D365

In the last tutorial you learned how to export data to excel. Now, learn how to update data from Excel in D365. There are some changes that can be made much faster in Excel compared to within D365. For instance, you can do a ‘find and replace‘ within Microsoft Excel. Specifically, push ctrl-H for the shortcut. Type in the value you are looking to remove in the ‘Find what‘ field. Next, type in the value you want to be there instead in the ‘Replace with‘ field. After making the changes, publish back the changes to Microsoft Dynamics 365 for Finance and Operations. Let us walk through the stes.

Ways To Export

In other articles, I have covered several other ways data can be updated in D365 from outside sources. You can use the data management framework, data entities, Odata, Excel, or X++.

In some scenarios, the ability to update data from Excel in D365 is the quickest and easiest way.

Open In Excel

Before updating data data from Excel in D365, we first need to export it to excel.

To clarify, we will start to follow a similar process to my last article, but then the steps will change.

First, navigate to the D365 form that contains the grid you are interested in exporting. In this example, navigate to Accounts Receivable>Customers>All customers. Or, type ‘all customers’ in the top search bar, and hit enter.

Note, you will see a grid that shows a list of customers.

Second, click on the ‘Open in Microsoft Office‘ button in the top right corner of your browser.

Then, select the ‘Customers‘ from under ‘Open in Excel‘. Note, yours may look slightly different if you are in a legal entity with a different name.

Third, click on the Download button to save the Excel file.

Notice, the system will download the excel file.

Fourth, open the Excel file.

Update Data In Excel

If this is your first time opening an excel file this way, you will need to allow the Microsoft Dynamics add on, in the dialog on the right side.

After that, the system will ask you to logon using the same username and password you used to connect to Microsoft Dynamics. To explain, this allows Excel access to the system.

Notice, the add-in will refresh Excel with the data from D365. If not, click the ‘Refresh’ button to synchronize Excel with the D365 data.

Now that Excel has data that matches D365, you can make changes directly within Excel.

For example, change the value of a record in the ‘Organization name‘ column. To demonstrate, change ‘Football Stadium‘ to ‘Football Stadium 2‘. Save the Excel file.

Next, we need to send the data back to D365. Press the ‘Publish’ button in the add-in dialog box.

After a moment, Excel will show that the publish was successful.

Refresh Data In D365

Now that you have update data from excel in D365, there is still one last step.

Go back to your browser showing D365. Click on the ‘refresh‘ button on the form. This will tell the system to re-read the information from the database, and refresh the form.

Note, you can click the ‘reload‘ button on your browser. However, this will lose the place in the grid you currently have scrolled to.

Now, you can see that the ‘Football Stadium‘ name value has changed to ‘Football Stadium 2‘.

Conclusion

Certainly, in most cases, where you need to just make a few changes to a single record, it is quickest to use the D365 forms. That said, there are some scenarios where you need to make a large number of the same updates, where using Excel can be extremely useful. By being comfortable using all the tools available, it allows you to use the best tool for your specific situation.

Peter Ramer

Peter Ramer is a part of the Managed Application Services team at RSM working on Microsoft Dynamics 365. He focuses on the Retail and Commerce industries. When he is not solving problems and finding ways to accelerate his clients' business, he enjoys time with his three kids and amazing wife.