Microsoft Dynamics 365 for Operations: Data Management and Excel

This article is a continuation of the discussion about the new workspace called Data Management in Microsoft Dynamics 365 for Operations (MsDyn365).  Data Management replaces the Data Import and Export Framework (DIXF) Module that was available in AX 2012 R2 and above. The emphasis of this piece is to introduce you to process of exporting Excel data, changing that data, and then importing it back in. Many of these operations function in much the same way, only now they are using Data Entities that both speed up the process and ensure data integrity.  We will first create a customer data set, export it to Excel, manipulate it by adding customers and then import it back to the MsDyn365 Fleet Management Customer Table to complete the process.

Exporting

Create the Data Set

For the first operation, we will need to create the data set within the Demo image. For our purposes, we used the MyDyn365 for Operation November 2016 release (keep in mind that previous versions will work as well).

To create the data set:

1. Open Dynamics 365 for Operations.
2. Click Show navigation pane.
3. Under Modules, open Fleet management > Setup > Fleet Setup.
4. Under the Data setup tab, click Create.

Export It

In this part of the data set creation, we export the data set to Excel. This uses the data entity that will connect AX and Excel.

To export the data set:

1. Open Fleet management > Customers > Customer.
2. Select the Open in Microsoft Office button on the menu bar.
3. Click Export to Excel > Customers.
4. Click Download.
5. Click Open.
6. Click Allow.
7. Save the Excel file as CustomerImport and place it on the Desktop, or in a directory where you can find it.)

Changing

Update the Data Set

For the second operation, you will update the information of the Excel spreadsheet so that you can reimport. Make sure that you do not change any of the columns. These are in the exact format for the import. If you were to change columns, you would need to use a different data entity or remap that entity to work.

To update the data set:

1. Open the file CustomerImport.xlsx from your desktop, if it is not already displayed.
2. On the first available row (likely row 8), enter a new customer name using the column headings as guides to the required information.
3. Repeat step 2 and create new customers 2–3 times. This will result in at least 10–12 names.
4. Save the spreadsheet.

Importing

Import Process

For the third operation, we will import the data back in to AX. If you followed the steps in the previous article on Data management, you would have already configured this process area.

There are many validation steps that are possible. At this point, we will simply using the “happy path” where the field maps joyfully to the correct location.  In future articles we will delve in to exceptions.

To import the modified data set:

1. Open Dynamics 365 for Operations.
2. Click Show navigation pane.
3. Under Workspaces, open Data Management.
4. Enter FleetCustomer in the Name
5. Select EXCEL in the Source data format
6. Select Fleet Management Customers in the Entity name
7. Click Upload.
8. Select CustomerImport and click Open.
9. Select Sheet1$ in the Sheet lookup
10. Click View map.
11. Map the Source to the Staging.

12. Click Save.
13. Click Generate source mapping.
14. Close the form.
15. Click Import.
16. Click View staging data to see imported items.
17. Click Copy data to target.
18. Click OK.

To check the import of the data set:

1. Open Fleet management > Customers > Customer,
2. Verify that the data was imported.

Conclusion

This article reviewed the Data Management Excel import.  We developed a data set and exported it in to Excel for use, changed the data set to simulate the update process, and we covered the import process.  We also discussed the complete lifecycle of Data Management and Excel to illustrate that these tools work well together. The potential for your use of these process in your operation expand the ability to automate data updates that range from very simple to complex.

For more information I would recommend “Data entities and packages framework” on ax.help.dynamics.com. You can also attend our AX trainings in person or online to learn more.

Keep an eye out for more articles that dive into the technical aspects of using the data management and SQL. Please visit academy.rsmus.com for more information and training materials that will cover this and many other topics for AX2012 and D3FO.

For more helpful information on getting the most out of your Microsoft Dynamics ERP solution, visit or subscribe to our Microsof Community News online publication

by Shaun McMikle for RSM

RSM Staff