Formatting excel data

[aditya_gupta41]

I have an Excel sheet with with below format:

In this Excel, column 1,2 and 3 remains common, and the data which i am suppose to send is in below output csv format:

Output:
QWE;1234;567890;1111;20212021 (Row 1: Column 1,2 and 3 is common with fields columns starting with ‘A’)
QWE;1234;567890;2020 (Row 1: Column 1,2 and 3 is common with fields columns starting with ‘B’)
QWE;1234;567890; ; ; (Row 1: Column 1,2 and 3 is common with fields columns starting with ‘C’)
QWE;1234;567890;1 (Row 1: Column 1,2 and 3 is common with fields columns starting with ‘D’)
QWE;1234;567890;2 (Row 1: Column 1,2 and 3 is common with fields columns starting with ‘E’)
OPI;5678;98765;2222;100100 (Row 2: Column 1,2 and 3 is common with fields columns starting with ‘A’)
OPI;5678;98765;456 (Row 2: Column 1,2 and 3 is common with fields columns starting with ‘B’)
OPI;5678;98765; ;65 (Row 2: Column 1,2 and 3 is common with fields columns starting with ‘C’)
OPI;5678;98765;3 (Row 2: Column 1,2 and 3 is common with fields columns starting with ‘D’)
OPI;5678;98765; ; (Row 2: Column 1,2 and 3 is common with fields columns starting with ‘E’)

Output SS:

Can anyone help me here to resolve the complexity?

[j_angelevski]

Hi @aditya.gupta41 ,

I created a solution for this. You can download and import the pipeline. This pipeline will work for more fields, it is not limited to only those shown on the image here.
Sample result:

Formatting_Excel_Data_SL_Community_2021_08_19.slp (9.0 KB)

Edit:
I forgot to change the csv delimiter, to do this, open the CSV Formatter snap and change the Delimiter setting to ;

You can also remove the quote characters if you want to. To do this just leave the Quote character setting empty.

And it will create a result like this

[aditya_gupta41]

Hello @j.angelevski,

Thank You for the help here. The code successfully worked on actual data. However, is it possible that these empty streams can be removed?

Thanks in advance

[j_angelevski]

@aditya.gupta41
Yes you can remove the empty streams. First you need to uncheck the Insert null columns setting in the excel parser. This will automatically exclude the empty fields.

Next, I updated the expression in the mapper.

$.keys()[0].split("|").map(val => val.contains("_") ? val.substring(0, val.indexOf("_")) : val).map(keyStart =>  $.filter((val, key) => key.contains(keyStart)))