Writing dynamic columns from a source file to a database

[AManohar]

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.

[Anonymous]

@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

[AManohar]

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.

[Anonymous · Accepted solution]

@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

[AManohar]

Thanks Shong.

This worked 😀 .

[Anonymous]

Great, thanks for your feedback!