In this guide we will be creating a Process that reads a CSV file and converts it to an XML file. The XML is also edited a bit. In this example, actual files are not created, but the content of each file is presented as strings in Frends.
The CSV data is stored in the file: oscar_age_male.csv. It’s content will be copy-pasted into Frends Tasks. The CSV lists male Oscar winners and is obtained from https://people.sc.fsu.edu/~jburkardt/data/csv/csv.html and they are licensed under the GNU LGPL license (Version 3, 29 June 2007) https://people.sc.fsu.edu/~jburkardt/txt/gnu_lgpl.txt
The first couple of rows of CSV are:
"Index", "Year", "Age", "Name", "Movie"
1, 1928, 44, "Emil Jannings", "The Last Command, The Way of All Flesh"
2, 1929, 41, "Warner Baxter", "In Old Arizona"
Setup
Import Tasks:
Frends.CSV.Parse,
Frends.Regex.Replace
Create a Process
Create a Process
Create a new Process and give it a name.
Create a new Task, give it a name and attach it to the Trigger. From the Task list, select CSV Parsing
Copy the content of oscar_age_male.csv to the CSV field. The type of the field can be left as Text, which is default.
Delimiter field is "," without the quotation marks (“”)
Column specifications can be left empty.
Under the option tab, everything can be left to the default values. It is important that ‘Contains header row’ has been set to true as it tells the Task that the first row contains column names.
Next, create two Replace Tasks:
The "CSV.Parse" Task has a result object ".Xml" that can convert the result of CSV parsing automatically to XML. This and many other methods can be found in the Task documentation.
As the ‘Contains header row’ option was set as true, most of the XML elements have proper names. However, the root element has the name "Root", and each row is inside a Row element. They can be easily replaced by two Replace Tasks. First, we will replace "Root" with "OscarWinners", and second, we will replace "Row" with "Person".
The Frends.Regex.Replace Task has return object called ReplacedText which holds the result output object. By referencing that we can use it in the next Replace Task.
The transformed XML that is returned will look like this:
<?xml version="1.0" encoding="utf-8"?>
<OscarWinners>
<Person>
<Index>1</Index>
<Year>1928</Year>
<Age>44</Age>
<Name>Emil Jannings</Name>
<Movie>The Last Command, The Way of All Flesh</Movie>
</Person>
<Person>
<Index>2</Index>
<Year>1929</Year>
<Age>41</Age>
<Name>Warner Baxter</Name>
<Movie>In Old Arizona</Movie>
</Person>
...
</OscarWinners>
Epilogue
For more complex XML transformations, it is better to use XSLT, a language and tool designed for XML transformations. They, and other XML related operations, have their own Task in Frends: Frends.XSLT.Transform
Next, learn how to Create a JSON Array and store it in a Variable.