XML Buffer and CSV Buffer Tables

Most of us that’s been programming in Dynamics NAV for a while are aware of the Excel Buffer table. It is a super useful table that can be used to create and read Excel files from C/AL code. This table have been around as far as I remember, and now it has some siblings, the XML Buffer and CSV Buffer tables.

The concept is the same, a table with functions to create or read files, this time XML/CSV files instead of Excel files. Just like with the Excel Buffer table, the XML buffer and CSV buffer table should be used as temporary tables.

This blog post describes how those tables can be used. With some examples where the task is to export/import items through XML/CSV files.

Create XML File using the XML Buffer Table

First the XML export; to do this I create a function in a codeunit that has the XML Buffer (table 1235) as a temporary variable. As a first step I use the ‘AddGroupElement’ function to add an ‘Items’ group element. Then to that element I add an attribute using the ‘AddAttribute’ function, the attribute contains the company name from which the data is exported. Below that I add the code to loop through the item records where I use the same functions to add an ‘Item’ group element with the item number as an attribute. To add child elements for the fields in the item table I use the ‘AddElement’ function and when I am done adding all the fields I use the ‘GetParent’ function to go back up one level in order to add the next item. Simple, right? If not, look at the code below and the screenshot of the file and it probably makes sense.

After the items have been looped through the ‘Save’ function will export the data and create an XML file with the given name and path (note that the XML Buffer table also have some functions to handle the upload and download of the file and to add namespace information, etc. I have skipped all that to keep it simple).

The file created then looks like below.

As easy as it gets to create an XML file with data from NAV. Almost too easy!

Import XML File using the XML Buffer Table

So, how does the import work using the XML Buffer table? It is as easy as the export. You create the table as a temporary variable and just use the function called ‘Load’ to load the XML file into the XML Buffer table. The records in this temporary table can then be processed as any other records. There are some predefined functions in the table to find the child notes, attributes, etc. but when I did this I found it easier just to go through the records in the table, something like below (which might not be the best written code but at least an example).

So that was quite easy as well.

Create CSV File using the CSV Buffer Table

For the CSV export I basically just create a new function that’s very similar to the one for the XML export; but instead of the XML Buffer as a temporary table variable I use the CSV Buffer table (table 1234, cool table number btw ). The syntax is slightly different, you use the ‘InsertEntry’ function to add data by specifying the line number, field number and value. And then the ‘SaveData’ function to create the file. The ‘SaveData’ function is called with a file name and field separator, in the below example I use a comma as a field separator, I think something like a TAB is much better, but it seems like you will have to modify the CSV buffer table itself to get that.

The file created then looks like below.

Nice!

Import CSV File using the CSV Buffer Table

Importing data from a CSV file using the CSV buffer table is also very easy. You use the ‘LoadData’ function to import the data from the file into the temporary table. Note that in addition to the file name you also specify the field separator. Once the data is in the table you can process it, the table only have three fields; Line No., Field No., and Value. The code to import the data and then process it could look something like below.

Also nice and simple.

Buffer Tables vs. XML Ports

So, the question now is when to use those two tables instead to creating an XML Port. To me if it is a simple structure of the data that should be exported or imported and the amount of data is fairly low then I would probably use the buffer tables, as you can see above they are very easy to work with. If the export or import are more of a complicated nature I would probably use an XML Port, you just have some more configurable option with that. An XML Port will most likely also have better performance if you are handling large amount of data, this since the buffer tables created one record per field in a temporary table which is not a very sufficient way of doing it.

An XML Port also have the advantage of being able to be passed through a codeunit that’s exposed as a web service. So if that’s the requirement, the an XML Port would be the obvious choice.

Personally I think I will use the two buffer tables quite frequently and I think it is a nice addition to Dynamics NAV.

Feel free to share this post if you like it. Cheers!

Olof Simren - Freelance Microsoft Dynamics NAV Expert

Naviona - Microsoft Dynamics NAV Partner