In this guide we will be creating short process that reads CSV data and converts it into Flatfile data. This guide 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 flat files, we mean files that have now delimiter characters, or other characters conveying the meaning of data, the position of 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 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. Create a new Task and attach it to the process trigger.

  • Within the new Task, assign "Parse" from the "Select a task" dropdown menu found in the "FRENDS.CSV" section as seen below.

  • Next, insert the example CSV dataset into the "Csv" field within the Task. In the delimiter field, enter the delimiter that is 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, you must add an item to specify the contents of the column. In the case of the example dataset, you need 3 items as there are 3 columns. Within the "Name" field, you can enter the name that you believe suits the data of the specific column. The dropdown menu named "Type" can be left to String. The following pictures show how the "Column specifications" field should look for the example dataset.

  • Now, change the tab from "Input" to "Option" found towards the top of the Task. 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 to the Parse Task. Name the C# statement in any way you please, maybe along the lines of "Transform to Flatfile" as that is the function of this C# statement block. 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 be unformatted once pasted into the C# statement in your process, however it should be functional regardless of the formatting. It should look similar to this

  • Finally, attach a Return to the C# statement. Within the "Expression" field, paste

    #var."Name of variable within C# statement"

  • The final process should look similar to this

  • The returned Flatfile data transformed from the example CSV dataset should look similar to this

    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

Did this answer your question?