In this guide, we will create a short Process that reads CSV data and converts it into Flatfile data. We will use a sample dataset as an example of CSV data. The example CSV dataset can be found below.
1,Addie Henderson,19
2,Lilly Reeves,21
3,Rebecca Bradley,47
4,Joseph Bryan,46
5,Juan Gibson,58
6,Melvin Armstrong,52
7,Elsie Curtis,63
8,Mable Salazar,18
9,Mittie Parsons,18
10,Andrew Cooper,31
There are a few possibilities to create flatfiles in Frends. With flatfiles, we mean files that have no delimiter characters or other characters conveying the meaning of data. The position of the character tells what it means. Therefore, it is usually necessary to add padding characters to get data to the right place in a file. If your input data is XML, it is usually best to use XSLT transforms to produce flatfiles. If the input is JSON, handlebars and C#, or LINQ are good options. Here we have CSV data that is easy to transform to a C# object and then use rudimentary C# operations. It is not the only way but should be easily understood with only a little understanding of programming.
Steps for creating the Process
Create a process and give it a name.
Add a new Task Element and attach it to the Process Trigger.
Within the new Task, select "Parse" from the "Select a Task" dropdown menu found in the "FRENDS.CSV" section.
Insert the example CSV dataset into the "CSV" field within the Task. In the delimiter field, enter the delimiter used in the CSV data you are transforming. In the case of the example dataset, the delimiter is a comma.
Click the "Add item" button in the "Column specifications" field. For each column of data, add an item to specify the contents of the column. In the case of the example dataset, you need three items as there are three columns.
Within the "Name" field, enter the name that suits the data of the specific column. The dropdown menu named "Type" can be left as "String".
The following pictures show how the "Column specifications" field should look for the example dataset. If you want, you can leave the whole "Column specifications" empty, but in that case, it is up to Frends to guess column names and data types, and because our data won't include headers, all column names would not have names in that case.
Change the tab from "Input" to "Option". If your CSV dataset includes headers, then ensure that the "Contains header row" field is on. If the dataset does not include headers, such as the example dataset, ensure that the field is off.
Next, attach a C# Statement Element to the Porcess. Name the C# Statement Element in any way you please, maybe along the lines of "Transform to Flatfile" as that is the function of this C# Statement Element. Ensure that the "Assign variable" field is enabled. Name the variable accordingly.
Within the "Statement" field, paste the following code:
{
int numOfCols = #result.Headers.Count;
int numOfRows = #result.Data.Count;
List<List<object>> csvList = new List<List<object>>();
csvList = #result.Data;
object [][] arr = csvList.Select(list => list.ToArray()).ToArray();
string [] data = new string [numOfRows];
List<string> arrList = new List<string>();
string flatfileOutput = "";
{
int num = 0;
int col = 0;
while (num < numOfCols)
{
{
int index = 0;
for (int row = 0; row < numOfRows; row++)
{
if (index < data.Length)
{
data[index] = arr[row][col].ToString();
}
index++;
}
index = 0;
}
{
int largest = 0;
for (int i = 0; i < data.Length; i++)
{
object value = data[i];
string obj = (string)value;
if(obj.Length > largest)
{
largest = obj.Length;
}
}
for (int i = 0; i < data.Length; i++)
{
object value = data[i];
string obj = (string)value;
obj = obj.PadRight(largest + 1);
data[i] = obj;
}
largest = 0;
}
arrList.AddRange(data);
num++;
col++;
}
}
{
for (int times = 0; times < numOfRows; times++)
{
for (int i = 0 + times; i < arrList.Count; i += numOfRows)
{
flatfileOutput = flatfileOutput + arrList[i];
}
flatfileOutput = flatfileOutput + Environment.NewLine;
}
}
return flatfileOutput;
}This code will lose its formatting once pasted into the C# Statement Element in your Process. However, it should be functional regardless of the formatting and will look similar to this:
Finally, attach a Return Element to the C# Statement Element and within the "Expression" field, paste #var."Name of variable within C# Statement Element"
The transformed Flatfile data returned from the example CSV dataset should look like this:
