topic Re: Writing dynamic columns from a source file to a database in Talend Studio

Writing dynamic columns from a source file to a database

AManohar — Fri, 15 Nov 2024 22:28:43 GMT

Hi,

How can we handle variable csv file in talend.

My input csv file will have 7 fixed columns and upto 5 variable columns.

eg:

file1:name;account;val1;val2

file2: name,account,val1,val3,val5

file3: name,account,val2,val5,val1

I need to update the database with these values and default values for the missing columns.

But my requirement is to update an existing table.

The variable fields may or may not be present in the input file, I need to look up on the header and load the data in the data.

I refered to following page with similar question https://community.talend.com/s/question/0D53p00007vClLzCAK/handling-dynamic-column-in-talend?language=en_US

But, couldnt find a working reponse in the post.

Could you please help.

Re: Writing dynamic columns from a source file to a database

Anonymous — Tue, 11 Oct 2022 07:09:58 GMT

@Anitha Manohar To read a source file using dynamic schema, there must exist header line in the file. The example file provided in your post does not contain header and the file has different field separators.

You can find an example about how to map dynamic columns from an input file in this topic.

Regards

Shong

Re: Writing dynamic columns from a source file to a database

AManohar — Wed, 12 Oct 2022 05:12:45 GMT

Hi Shong,

Thank you for your response.

I was able to read the dynamic columns using the tJavarow.

In my example I was referring to file1:name;account;val1;val2 as headers only.

Thank you.

Re: Writing dynamic columns from a source file to a database

Anonymous — Wed, 12 Oct 2022 07:30:03 GMT

@Anitha Manohar , you are able to get the column properties including column name, column value, type etc at runtime, for example, here is the Java code on tJavaRow used to get the column properties.

Dynamic columns = input_row.data;

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

{

DynamicMetadata columnMetadata = columns.getColumnMetadata(i);

String col_name=columnMetadata.getName();

String col_value=(String)row1.data.getColumnValue(col_name);

String col_type=columnMetadata.getType();

if(col_name.equals("name")){

columnMetadata.setName("new_name");

}

if(col_name.equals("age")){

columnMetadata.setType("id_Integer");

}

In your case, get the column value and check if it is null or other values, change the codes based on your specific needs.

Regards

Shong

Re: Writing dynamic columns from a source file to a database

AManohar — Thu, 13 Oct 2022 00:05:11 GMT

Thanks Shong.

This worked 😀 .

Re: Writing dynamic columns from a source file to a database

Anonymous — Thu, 13 Oct 2022 02:34:02 GMT

Great, thanks for your feedback!