Pivot data

This example shows how to use the RowMultiplication to pivot data in a csv file.

Preqrequisites #

This example can work stand-alone - the only dependency is that you add the ETLBox and ETLBox.Csv packages as dependency.

Input file #

Our csv input file contains account informations. We have an account number, and we have a value for each month of the first quarter.

Account;JAN;FEB;MAR
4711;10;11;12
4712;20;21;22
4713;30;31;32

We can define a CsvSource to read the data from this csv file using a classic object. The parsing of the csv file is based on the CsvHelper by JoshClose. We can use attributes from the CsvHelper to control the parsing. In this case, we want to use better names for the months.

public class InputData
{
    public int Account { get; set; }
    [Name("JAN")]
    public int January { get; set; }
    [Name("FEB")]
    public int February { get; set; }
    [Name("MAR")]
    public int March { get; set; }
}

Next we define our output type, which is a pivoted view of our input. So for each account number, we want to have the month name together with the monthly value.

public class PivotedOutput
{
    public int Account { get; set; }
    public string Month { get; set; }
    public int MonthlyValue { get; set; }
}

Pivoting the input #

Let`s create a source and destination componet for our data flow. In this example, we will sent the pivoted data into another csv file.

var source = new CsvSource<InputData>("Accounts_Quartal1.csv");
source.Configuration.Delimiter = ";";

var dest = new CsvDestination<PivotedOutput>("AccountNumbers_Pivoted.csv");
dest.Configuration.HasHeaderRecord = false;

Now we introduce a RowManipulation that does the magic.

var trans = new RowMultiplication<InputData, PivotedOutput>();
trans.MultiplicationFunc = row =>
{
    List<PivotedOutput> result = new List<PivotedOutput>();
    result.Add(new PivotedOutput()
    {
        Account = row.Account,
        Month = nameof(InputData.January),
        MonthlyValue = row.January
    });
    result.Add(new PivotedOutput()
    {
        Account = row.Account,
        Month = nameof(InputData.February),
        MonthlyValue = row.February
    });
    result.Add(new PivotedOutput()
    {
        Account = row.Account,
        Month = nameof(InputData.March),
        MonthlyValue = row.March
    });
    return result;
};

Executing the flow #

Finally, we can link and execute our data flow.

source.LinkTo(trans);
trans.LinkTo(dest);

Network.Execute(source);

This is the content of the newly created output file. As we set the property HasHeaderReader of the CsvConfiguration to false, the header row is omitted.

4711,January,10
4711,February,11
4711,March,12
4712,January,20
4712,February,21
4712,March,22
4713,January,30
4713,February,31
4713,March,32

Code on Github #