Import Flat File using an XML Port

Most of you already know that DataPorts are being discontinued, and don’t work on RTC anymore…

Just today I had to import a delimited file through RTC, but the actual import didn’t go so smooth. Here is a brief overview to import…

Since NAV 2009, XMLPort got a few new properties, which you might recognize from DataPorts:

Property name Allowed Values Usage
Format Xml, Variable Text, Fixed Text Tells the XMLPort which type of formatting to handle. When setting this to variable or fixed text, you do not longer have an XMLPort but a DataPort in disguise.
FileName The filename to use for the xmlport.
FieldDelimiter This property replaces the “FieldStartDelimiter” and “FieldEndDelimiter” properties of the DataPort. In here you can set the characters that specify the beginning and end of a field.
FieldSeparator This is the replacement for the old FieldSeparator property, for CSV files you can use ‘;’ here.
RecordSeparator This is the character that separates records when using a non-XML format. By default, this is “<NewLine>”.
TableSeparator This is the character that separates tables when using a non-XML format. By default, this is a double “<NewLine>”.
UseRequestForm This property sets if you use a requestformpage when running the XMLPort (!)

Also you can create your own RequestOptionsPage now.
Let’s say we have a text file that looks like the following:

Line1,Value1
Line2,Value2

We have 2 possibilities, either we import directly into a table, or we import into variables.

Whichever we choose, we must always start with a Root element of type Text and setting our options on the XMLPort (see above)
If we import into a table, select an element of type Table and choose the table you want to insert too.
After that select the fields and you are set.

It gets a little harder once we do not import into a table, but want to import only into text variables…
I started by adding a table of type integer, because it is necessary to select an element of type table.

Basically, we set up the following:
Element Layout

Next we want to do something with our values, so let’s just say it a message.
By the way, the name we use in the Data Source is the name of our variable, no need to separately create these, in the case above, the Node Name will be used, because no explicit Data Source is supplied.

We open C/AL Code and type a message:
Code snippet: Message

Here’s a screenshot of my XMLPort properties as well:
XMLPort Properties

You can see I have set Direction to Import and changed Format to Variable Text.

Our next step would be running it. You can easily create a new MenuSuite Item, just select type XMLPort and you are set. Running it will show us following screen:
XMLPOrt RequestForm

Any change you make to the RequestOptionsPage will be shown here, also setting a table view on our table will hide the sorting and filter. Only thing we seem to be missing is the option to select a file, but that is in the next step, so press OK and select your file. Setting UseRequestForm to false will directly run the “Select File” Dialog.

At first everything looks ok, we get our message:
FLD1: Line1\FLD2: Value1

But after clicking OK, we are greeted with a nice error:
The Integer table is read-only.

And this just now that we thought we are going fast…
Wait, don’t we remember some setting in a DataPort that disables automatic update/insert?
Actually, this is nowhere to be found within an XMLPort…

There does exist a solution, but let’s say that I’m not so excited by how to do it…
We set our DataItem Integer to Temporary. So after running it again instead of the read-only message, we get to following error:
Integer Number '0' already exists.

To get by this error, we have multiple solutions, one would be to use a counter and update the number. But if you ever use another table as DataItem, it might not be that easy. Another option is adding the following line of code:
Integer.deleteall added

So, after saving, and running again, we see that all runs well now.

As I mentioned, I do not like how we need to do this, and I still need to check in NAV2013 if this is still the same.

You can also see an example of an export here:
http://dynamicsuser.net/blogs/marcos/archive/2011/04/18/the-dataport-is-dead-long-live-the-xmlport.aspx

2 thoughts on “Import Flat File using an XML Port

  1. Pingback: Import Flat File using an XML Port | Pardaan.com

Leave a Reply