Using Microsoft Dynamics AX Data Import Export Framework (DIXF) module to upload vendor data

Uploading large amounts of data into Microsoft Dynamics AX is not always an easy task. For example, when you are creating additional subsidiaries in your existing Dynamics AX environment or you have to create thousands of vendor records from a prior system, adding all of this data through the user interface will result in wasted man hours and sore hands – from the enormous amount of clicking and typing needed to get this done.

Dynamics AX now features a module built around importing and exporting data called Data Import Export Framework (DIXF). At a first glance, using this framework can be a little overwhelming. However, this blog will demonstrate how to upload a list of vendors from start to finish using this module.

From a high level this module works in a three step process:

1. First, the data from the file you are planning to upload (whether it be excel, CSV, etc.) is uploaded into a staging environment.
2. The staging environment allows you to validate your data and make any necessary changes.
3. Lastly, the data in the staging environment is then uploaded to Dynamics AX.

In this example, my data is in an Excel spreadsheet. However, it is saved as a CSV file and I chose to use headers in my file as well.

Note: I am going to place an error in my data to show additional features of this module. Specifically, selecting the rows of data that caused an error when migrating the data from the staging environment into Dynamics AX and correcting those errors without having to restart the entire process. The list of vendors and fields I am uploading are below:

Data Type

With the data file containing the list of vendors you want to upload, the first step in this process is to setup the Source data formats. This is the where you determine which file format you will use. The supported file formats are: delimited, fixed width, XML, and Excel. In my example, using my CSV file, the setup for my Source data formats page is as follows:

1. Click new> define a source name and description for your reference
   • File format: Delimited
   • First row header: check
   • Row delimiter: {CR}{LF}
   • Column Delimiter: Comma {,}
   • Lastly, define the Multiple value separator, I will choose an underscore (_)
   • Screen should look as follows:
   • Close screen
     

Processing Group

The next step is to setup the processing group, found in the common section of the module.

1. Give the group a name of your choice.
2. Next click Entities
3. From the Entities dropdown we will choose Vendor
4. Source data format: CSV (defined earlier)
5. Be sure to click both checkboxes on this screen, Run business logic in insert or update method and Run business validations. Failure to check these boxes, you are able to upload data into Dynamics AX that has not been validated. Down the road this could cause major issues.
6. The next step is to click Generate source file> click next
   • Here you will select which fields you are uploading from your template to the staging environment
   • It is important to have the order of the fields align with the headers from your upload file
     • Utilize the up and down buttons from this page to do so
     • The generating your source mapping should look as follows:

     

   • Click finish.
   1. From the Entities screen> Click the folder next to Sample file path
      • Here you will choose your CSV file you are wishing to upload.
   2. Once you chose your respective file, click Generate Source Mapping
      • An info log should appear indicating that the entity mapping is done successfully. If not, ensure you saved your file in the correct file format and double check the above step in Generating source file.
      • It is important to note: you cannot have the file open during this process.

      

   3. Next, click on the tab Modify source mapping. This screen displays the fields from the source (your document) and how they relate to the staging environment, which is where the data is first imported. From the staging environment, you are able to clean and verify the data prior to pushing it into your Dynamics AX environment.
      • In this case – the account number will be auto-generated. From the modify source mapping form, click mapping details and select the checkbox Auto-generated for the source field AccountNum

      

      • Click Close

 Upload

1. From the Process group form you will now click Get Staging Data
   • Leave the defaulted Job ID and enter a description if you please
   • Click OK
     
2. Ensure the file path is correctly pointing at your CSV file
   • Make sure that this file is not open
3. Click Run > OK
4. An info log appears that your data has been inserted into staging.
   • Your data is now ready to be validated and you can make changes if needed

Verify Data

1. From the processing group screen – select Execution History
2. To view the data that is now inserted into the staging environment, click View staging data
   • From here you are able to validate the data you are planning to load into Dynamics AX and make changes if necessary.
   • From my data, I chose my TaxIDType to = Employer Identification Number. The data type for this field is an Enum. The values are stored as single digit number (0, 1, 2, etc.). In this case I will want to change this field = 1
   • After the necessary changes have been made you are ready to push the data into Dynamics AX
   • Close the view staging data form
3. Click Copy data to target
   • Select the appropriate job ID
   • Click OK > Run > OK
     
4. Your data has now been loaded into Dynamics AX.

Error

Earlier in this blog I stated I was going to insert an error into my data. The error I input was the CountryCodeID for the vendor: City of London. The correct country code is GBR and I chose UK. The validation in the above step caught this error and it could’ve been changed in the last step. My info log now reads:

The row of data for my vendor, City of London, is still in the staging environment. There is no need to go through this entire process to upload the records that I did not get validated to make it from the staging environment to Dynamics AX.

1. To fix this error, select execution history. Next you will want to view the staging data. You will see that the Transfer status reads Error for the rows of data that were not imported.
   
2. Now correct the field that is causing the error (change the CountryCodeID to GBR instead of UK)> Validate to ensure everything is correct.
   • Close the staging data form. Select Copy Data to target, however, change the criteria for run for from all to criteria and select the checkbox for Rows with pervious errors
   • Click OK> Run> OK
     
3. All the data has now been uploaded successfully.
   
   

RSM is a national partner for Microsoft Dynamics AX offering comprehensive professional services for Dynamics AX. We bring a breadth of experience and deep industry knowledge to integrate and streamline all aspects of your corporate operations. For more information on how we can help you with your Dynamics AX implementation, contact our professionals at erp@rsmus.com.

Written by: Michael Lang – Great Lakes Microsoft Dynamics AX partner

Michael Lang