Reading and Writing to a File via XMLPorts in Dynamics NAV

XML Ports are special types of objects in Dynamics NAV which are used to import or export data from the system into either XML or .CSV (.TXT) files. The output files can be later used in other third party applications, which is very useful during the data migration process between the two systems.

To import the data, the XMLPort has to read what’s in the file and distribute the data into appropriate tables according to the file structure. To export the data, the XMLPort will read the data from the NAV tables, do some manipulations with it if needed, and create an output file.

In this demo, we are going to have a look at how we can utilize the import/export features of XMLPorts. We are going to use Dynamics NAV 2013 R2 for this purpose.

Reading a .CSV file

    1. Open Dynamics the NAV 2013 R2 Development Environment and in the Object Designer, select Tables. Click New and create the following new table:

 

    1. Save the table with ID 50000 and name it Flights.
    2. Click View > Keys and modify the Primary Key to have 2 fields: Flight No. and Flight Date.
    3. Next, let’s assume that we need to import a text file for an airport flight schedule. Create a new text file in Notepad with the following lines:

Let’s note the following about the source file:

      • The fields are delimited by the ” sign
      • The fields are separated by the comma sign
      • Each new line will be entered into the NAV table as a new line
    1. In the Dynamics NAV Development Environment, select XMLPort object type and click New to create a new XML Port. Enter the following :

 

    1. Select the Flights element and click SHIFT+F4 to open its properties. Ensure the SourceTableView property is set to the following:

 

  • Select an empty line on the XMLPort and modify its properties as shown:

 

  • Close the Properties window and click View, C/AL Globals and add the following variable:

 

This variable will be needed to accept the values coming from the file when it is read by the XML Port.

Close the variables window and hit F9 to open the code designer. Enter the following code In the OnBeforeInsertRecord and OnPostXMLPort triggers:

The code checks for the next record and assigns the values from the file to the appropriate table field.

The message is just an indicator for us to see that the file is read and the data is imported into the NAV table, as there is no progress bar to see the progress.

Close and save the XMLPort. Now we can run and test the work. Select our XMLPort in the Object Designer and click Run. Select the Import option on the Request Page and pick up the Flights.txt file to import. The result of the routine is the data imported into our table that we created in Step 1:

Writing to a .CSV file

Now let’s assume that we need to create a text file as an output from our Flights table. However, we need to adjust the data that we have in our table, specifically to change the following:

  • We’ve imported even flight numbers, now we would need to export odd flight numbers which are used for return flights. To simplify the example, the Odd flight numbers are just Even Flight No. +1
  • Same for the dates – we would need to export the return flight dates as the next day

We will use the same airlines for this demo.

    1. Design our XMLPort that we created earlier and hot F9 to open the code. In the OnAfterGetRecord trigger, enter the following code (this code performs the necessary processing of the table data before the file is written. It sets the return flight number and the return date.):

  1. Run the XMLPort with the Export option:

We can see that the file was created according to the requirements, with the modified flight numbers and dates.

As we see, XMLPorts are a powerful tool which can bridge your data between Dynamics NAV and other systems via XML or TXT format. It also allows processing the data and updating it at various stages of file processing; for example, when the record is read for the first time, or when the record is about to be placed in the table, or after processing the whole file, etc.
XMLPorts support UTF, MS-DOS and WINDOWS encodings and allow for both Fixed and Variable text formats. Specifying custom field delimiters and separators allow files to be accepted and read when exported from other applications.

August 21, 2014