topic [resolved] Transposing input file with dynamic number of columns in Talend Studio

[resolved] Transposing input file with dynamic number of columns

Anonymous — Sat, 16 Nov 2024 11:32:35 GMT

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

Anonymous — Mon, 18 Aug 2014 12:25:07 GMT

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

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

Anonymous — Mon, 18 Aug 2014 14:52:51 GMT

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;

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

Anonymous — Mon, 18 Aug 2014 14:58:32 GMT

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

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

Anonymous — Mon, 18 Aug 2014 15:07:42 GMT

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;