[resolved] Transposing input file with dynamic number of columns

[Anonymous]

I have a very tricky situation and have yet to come up with my own solution. I have an input csv file that has a dynamic number of columns and need to transpose this file to create a desired output file. The input file looks like this:

ID1 | Units1 | Count | Val1a | Val2a | Val3a | Val1b | Val2b | Val3b | Val1c | Val2c | Val3c
ID2 | Units2 | Count | Val1d | Val2d | Val3d | Val1e | Val2e | Val3e

As you can see, the Val's are in triplets on each row and I need the triplets to stay together. The Count column holds a number of how many triplets are found on that row as each row is dynamic. The final output I need is:

ID1 | Units1 | Val1a | Val2a | Val3a
ID1 | Units1 | Val1b | Val2b | Val3b
ID1 | Units1 | Val1c | Val2c | Val3c
ID2 | Units2 | Val1d | Val2d | Val3d 
ID2 | Units2 | Val1e | Val2e | Val3e

I have tried pivoting and unpivoting the rows but this does not allow me to keep the triplets together. Is it possible for me to send the input file through a Java script where I parse the file and output to a csv file in this format?
Any help is much appreciated.

[Anonymous]

Hi,
I tried similar in another post
https://community.talend.com/t5/Design-and-Development/How-to-normalize-a-row-into-multiple-rows-fro...
Pl check if you can get some idea from this.
vaibhav

[Anonymous · Accepted solution]

I solved this by using the tJavaFlex component. My final component architecture was:
tFullInputRow --> tFlowToIterate --> tJavaFlex --> tOutputFileDelimited
My Java code was along the lines of

// First we split the data on the delimiter of the file, assuming comma separated for now
String[] splitData = ((String)globalMap.get("input")).split("|");
// Initialise the three value strings
String value1 = ""
String value2 = ""
String value3 = ""
for (int i = 0; i < splitData.length; i++) {
    if (i == 1) {
        // id field is the first field
        String id = splitData;
    } else if (i == 2) {
        unit field is the second field
        String unit = splitData;
    } else if (i == 3) {
        // Don't need to do anything with the count data
    } else if (i % 3 == 1) {
        // value1 fields are 4, 7, 10 etc so modulo 3 == 1
        if (value1.length == 0) {
            value1 + splitData;
        } else {
            // if the value field isn't empty (ie. for val1b) then we need to add a delimiter for further processing
            value1 + "|" + splitData;
        }
    } else if (i % 3 == 1) {
        if (value2.length == 0) {
            value2 + splitData;
        } else {
            value2 + "|" + splitData;
 }
 } else if (i % 3 == 3) {
 if (value3.length == 0) {
 value3 + splitData;
 } else {
 value3 + "|" + splitData;
 }
 }
}
for(int i = 0; i < splitData.length; ++i)
/////// MAIN CODE ///////////////////////
output_row.id = id;
output_row.unit = unit;
output_row.value1 = value1;
output_row.value2 = value2;
output_row.value3 = value3;

[Anonymous]

That's great...
thanks for posting up here.
Thanks
vaibhav

[Anonymous]

I solved this by using the tJavaFlex component. My final component architecture was:
tFullInputRow --> tFlowToIterate --> tJavaFlex --> tOutputFileDelimited
My Java code was along the lines of

// First we split the data on the delimiter of the file, assuming comma separated for now
String[] splitData = ((String)globalMap.get("input")).split("|");
// Initialise the three value strings
String value1 = ""
String value2 = ""
String value3 = ""
for (int i = 0; i < splitData.length; i++) {
    if (i == 1) {
        // id field is the first field
        String id = splitData;
    } else if (i == 2) {
        unit field is the second field
        String unit = splitData;
    } else if (i == 3) {
        // Don't need to do anything with the count data
    } else if (i % 3 == 1) {
        // value1 fields are 4, 7, 10 etc so modulo 3 == 1
        if (value1.length == 0) {
            value1 + splitData;
        } else {
            // if the value field isn't empty (ie. for val1b) then we need to add a delimiter for further processing
            value1 + "|" + splitData;
        }
    } else if (i % 3 == 1) {
        if (value2.length == 0) {
            value2 + splitData;
        } else {
            value2 + "|" + splitData;
 }
 } else if (i % 3 == 3) {
 if (value3.length == 0) {
 value3 + splitData;
 } else {
 value3 + "|" + splitData;
 }
 }
}
for(int i = 0; i < splitData.length; ++i)
/////// MAIN CODE ///////////////////////
output_row.id = id;
output_row.unit = unit;
output_row.value1 = value1;
output_row.value2 = value2;
output_row.value3 = value3;