XML to CSV

XML to CSV

InterFormNG2 cannot directly create CSV files, but it is actually possible to build a workflow that does that. To illustrate how this can be done we can consider the demo invoice xml file, which is included in a new InterFormNG2 installation.

The demo invoice xml file has multiple detail lines like below:


The idea here is to extract the values of the nodes: Line_Description, Line_Description2 and Line_Amount into an output CSV file with a semicolon (;) as the delimter like below:
 
GLC Screw;Internal drive;2350.00
GLC Washer;Internal drive;1550.00
GLC Fasteners;V-shape;1650.00
GLC utility service;Full platinum cover program / XL case;3575.00

The path in the XML file to the "Sales_Invoice_Line" node is: /IF_Type/Sales_Invoice/Sales_Invoice_Header/Sales_Invoice_Line, which we will later use for a repeat loop.

The steps to convert the XML file into CSV are listed below:

Create a transformation which transforms into a simple XML file with one node per output line
The first step of the conversion is to use the transformation designer in InterFormNG2 in order to produce a simple, temporary XML file, which looks like this:


That is done by creating this transformation template:


Each of the elements are described below:

1. Variable, Delimiter
Here a variable, Delimiter is defined to specify which character which should be used as field delimiter in the output CSV file. In this case we select semicolon (;) as below:


2. Match
For the match line we just use the default contents:



3. XML element root
Here we insert the root node of the output XML file:

4. Repeat
We use the repeat function to add one output line for each detail line in the input XML file. The repeat looks like below:


This is the path to the detail node, which was selected with the magnifying glass (after referring to a simple input XML file).

5. XML element: line
For each repeat we add a line element in the output XML file as below:

The special sequence || (two vertical pipes) is the same as a concat. That means that the string before the || is concatenated with the string after.
So with the expression above we put all the data elements together - separated by the delimiter.

Implementation in a workflow
Now we can use the transformation above when we build a workflow, which converts the XML file into a CSV file. 

The workflow has these components:

Each of the elements are described below from the left to the right:

Here we setup a workflow to monitor a folder for new, incoming XML files:

To make it easier to define and maintain the workflow a sample file is referenced. This is a sample file, that has been created by the transformation template, which was created in the previous section.

Now we execute the transformation template we defined in the previous section:

This change the payload from the original input Invoice xml file into the simple XML file shown in the transformation section above, which contains only a root node and multiple line nodes inside.

Now we setup multiple variable to prepare for the processing:

Here these variables are defined:

NbrOfLines
The value is the number of line nodes in the transformed XML file. You can see the simple XML file referenced in the transformation section above.

Index
We intend to select each line node one by one and for that we need this index variable, which will have the initial value of 1 and is to be increased with 1 for each repetition.

CSV_data
This is the output variable into which we will build the CSV data. This starts with an empty string.

The choice and join element has a dummy condition, which is always true:

The purpose of the choice and join is simply to execute all the repeats, which will collect output data into the CSV_data variable and then continue with the join branch to output the result.

The repeat is executed as many times as the number of line nodes in the transformed XML file as seen below. The option, Keep variable values and payload between repeats is activated and that is very important to remember (if not activated the value of the Index and CSV_data variables cannot be updated for each iteration).

It is also important to use the number() function to convert the variable into a number as workflow variables are handled as strings.

Inside the repeat we update the variables, CSV_data and Index as below:



CSV_data is defined as the current value of CSV_data concatenated with a string which contains a line break (This was added a two single quotes and then pressing Enter inside to add the line break). It is also concatenated with the value (contents) of the current line node. The current line node is selected with [number($Index)], so we use the Index variable to select the current line node.

In this manner the CSV_data will contain a line break and then contents of all line nodes separated by another line break. Later we need to remove the initial line break.

After the update of the CSV_data we increase the value of the Index variable with 1, so that it increase with one for each repetition in the repeat.

Notice the number() function is used for both variable updates.

Now we consider the join branch, which is executed after the repeats above. Here we remove the first character (the initial line break) from the CSV_data with this expression:
The substring function simply extracts the contents of the CSV_data variable - from position 2.

The last component outputs the CSV file to the file system. That is done like below:














    • Related Articles

    • CSV

      File types of the type CSV (Comma Separated Values) is one of the file types/payloads, that can be processed in the InterFormNG2 workflow. The csv files normally the file extension .csv. If you want to process CSV input files in InterFormNG2, then ...

    • CSV to XML

      One of the valid input formats of InterFormNG2 is CSV files. If you e.g. want to data in an input CSV file in either an email or in a template, then you first need to convert the CSV file into XML. You can use the basic, converter workflow component, ...

    • XML Node Selection & Referencing

      XPath provides multiple ways to reference and navigate through an XML structure. Learn how to select specific nodes using direct references, index numbers, conditions, and relationships with other nodes. Connecting preceding or following node sets to ...

    • CSV file into Excel

      InterFormNG2 is able to convert generic CSV files into Excel. This section describes how that can be done. As an example we consider a CSV file with this content: Seq,Product No,Description 1,4234,aaaaa 2,23423,bbbbbb 3,3423,ccccccc 4,345,dddd ...

    • Node Existence & Conditions

      XPath allows you to verify the existence of nodes, check if they contain data, count occurrences, and ignore namespaces. This section covers essential techniques for validating and filtering XML elements. Calculating the sum of nodes Counting ...