CSV is the simplest way of presenting structured data. It is a text file where each line represents a table record, and record fields are separated with a comma—hence the name CSV. The data file is very compact: it does not carry any markup data except field delimiters, and the format is easily mapped to relational databases where tables are native data storage.

Although a comma is the default field separator in NAV XMLports, it is actually defined by object properties and can be changed, which turns the file into a DSV instead of a native CSV. Now, we will create an XMLport for importing customer payment data from an external text file with a semicolon as a field delimiter.

In the next example, we need to extend the customer payment table and add new fields to it, as follows:

Now, we have all the fields we need to create an XMLport and load data into the table. Create an XMLport in the object designer and save it with the ID and name 50500 Import Customer Payments CSV:

When designing the structure of the XMLport, pay attention to the line indentation in the Node Name column. The base element of the structure must be a Text element, and the table being exported comes as a child node for the root. The root element is not actually exported and does not appear in the resulting CSV file. The Table element must be indented under the root, and all Field elements come under the table with further indentation. To align data elements, use the arrow buttons at the bottom of the page. The XMLport Designer is shown in the following screenshot:

We want to import data from a variable-text CSV file with a semicolon as a field separator. The same applies for data export, as the XMLport we are developing for CSV format supports exporting as well. All export parameters are defined in the object properties. To access the XMLport properties, select the empty line under the bottom element in the editor and select the View | Properties menu action. When positioned on a data element line, the same action will open the properties of the selected element instead.

The first key property for us is Format. It defines the format of the output file: variable text, fixed text, or XML. By default, a new XMLport is created in XML format. Change this value to Variable Text to export data into a CSV file with a delimiter.

After selecting the variable text format, you can also choose delimiters that will separate values in a data stream:

• FieldDelimiter: The symbol that will mark the beginning and ending of each field. Leave the default quotation mark.
• FieldSeparator: A value placed between two fields in a record. Change the default comma to semicolon (;).
• RecordSeparator: Symbol separating records within one table. The default is a <<NewLine>>, which is fine for us. This means that every new line in the file begins a separate table record.
• TableSeparator: This is how you want to separate tables if data from several tables is combined in one file. The default value is a double new line (<<NewLine><NewLine>>), but we are not going to mix tables in this example, so it does not matter.

As you can see, the configuration created in the XMLport does not include the Entry No. field, which is the primary key for the Customer Payment table. This is an internal value specific to the NAV data structure and is not coded in the external data file. To assign a value to the entry number, we can use C/AL code (and we will do this a little later). But it is not mandatory to initialize the entry number in C/AL triggers; this task could be delegated to SQL Server. To make the server assign the primary key value automatically when the record is inserted, perform the following actions:

1. Open table 50502 Customer Payment in the table designer.
2. Select the Entry No. field and navigate to Properties (View | Properties).
3. Choose the AutoIncrement property and change its value to Yes.

With this setup, the value of the primary key field Entry No. will be incremented automatically when a new record is inserted.

A sample data structure for importing is shown in the next block. If you run a data export with the same XMLport, you will receive a file with exactly the same fields:

"LC0001";"10000";"10.01.18";"186,00";"ADM";"VW";"MEDIUM";"JR";"21245278"
"LC0001";"20000";"10.01.18";"258,00";"ADM";"TOYOTA";"INSTITUTION";"JR";"21245278"
"LC0002";"10000";"10.01.18";"1587,00";"PROD";"VW";"LARGE";"MD";"01905893"
"LC0003";"10000";"10.01.18";"2637,00";"SALES";"VW";"MEDIUM";"MD";"21245278"
"LC0003";"20000";"10.01.18";"235,00";"SALES";"MERCEDES";"PRIVATE";"MD";"21245278"
"LC0003";"30000";"10.01.18";"9764,00";"SALES";"TOYOTA";"LARGE";"MD";"21245278"

Now, the setup is in place to import payment information from a CSV file. Just run the XMLport from the object designer and select a file to import when requested.