One Star

[resolved] Transposing input file with dynamic number of columns

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.
4 REPLIES
Four Stars

Re: [resolved] Transposing input file with dynamic number of columns

Hi,
I tried similar in another post
http://www.talendforge.org/forum/viewtopic.php?id=37710
Pl check if you can get some idea from this.
vaibhav
One Star

Re: [resolved] Transposing input file with dynamic number of columns

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;
Four Stars

Re: [resolved] Transposing input file with dynamic number of columns

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

Re: [resolved] Transposing input file with dynamic number of columns

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;