topic Re: Extract data dynamically and associates column/value in Talend Studio

Extract data dynamically and associates column/value

Mr_B — Fri, 15 Nov 2024 23:35:38 GMT

Hi,

This is my first post and I am asking for your help.

I've been looking for a solution for several days by looking on the web or looking for similar posts but I can't find any solutions.

I have a csv file with a header consisting of 3 columns and 3 rows of associated data.

Column1; Column2; Column3

value_A; value_B; value_C

value_D; value_E; value_F

value_G; value_H; value_I

I am trying to have the following result in output in a file which groups together on two columns: the name of each column and its value

Column1; value_A

Column2; value_B

Column3; value_C

Column1; value_D

Column2; value_E

Column3; value_F

Column1; value_G

Column2; value_H

Column3; value_I

I cannot specify the name of the input columns so I dynamically read my input file and manage to extract the columns and values.

I used the following link to achieve this: Scenario 2: Extracting the contents of a dynamic column via tJavaRow

I am stuck building my output file with the expected result.

I adapted the code to achieve my ends but to no avail.

Dynamic columns = input_row.line;

String name_column = "";

String value_column = "";

for (int i = 0; i < columns.getColumnCount(); i++)

{

DynamicMetadata columnMetadata = columns.getColumnMetadata(i);

name_column += columnMetadata.getName().replace("_"," ");

value_column += columns.getColumnValue(i);

}

output_row.column_name = name_column;

output_row.column_value = value_column;

TFilterColumn

Tmap

The problem is that in output, I get my two columns, but I have the column names on one side and the values on the other:

I have tried several things but am lost.

Any help in resolving this issue would be appreciated.

Re: Extract data dynamically and associates column/value

Anonymous — Tue, 26 Oct 2021 04:38:14 GMT

You need further processing to get the expected result, see

tJavaRow1:

Dynamic columns = input_row.line;

String name_column = "";

String value_column = "";

for (int i = 0; i < columns.getColumnCount(); i++)

{

DynamicMetadata columnMetadata = columns.getColumnMetadata(i);

if(name_column.equals("")){

name_column=columnMetadata.getName();

}else{

name_column= name_column+";"+ columnMetadata.getName();

}

if(value_column.equals("")){

value_column=(String)columns.getColumnValue(i);

}else{

value_column= value_column+";"+ columns.getColumnValue(i);

}

output_row.column_name = name_column;

output_row.column_value = value_column;

tJavaRow2:

//Code generated according to input schema and output schema

output_row.sequence_id1=Numeric.sequence("s1", 1, 1);

output_row.column_name = input_row.column_name;

tJavaRow3:

//Code generated according to input schema and output schema

output_row.sequence_id2 = Numeric.sequence("s2", 1, 1);

output_row.column_value = input_row.column_value;

For detailed information, please import the job items into your studio.

Hope it helps!

Regards

Shong

Re: Extract data dynamically and associates column/value

Mr_B — Tue, 26 Oct 2021 07:43:38 GMT

Hi Shong,

Thank you very much for this feedback and for these explanations.

I better understand the logic of reasoning to arrive at my final result.

I should have tried to separate the spots a little more, like you associating a sequence to each content to bring them together in a tMap.

Thank you again for helping.