How to Iterate Values from Multiple columns in Excel and updating the values to DB columns based on expression

[AV2022]

Hello Team,

I am trying to Iterate the values from my Input CSV file which i have used as my lookup file and is connected to tMap. My main source table is Access DB which is also connected to tMap. The final output from tMap is pushed into Oracle DB.

Below is the screenshot of my component connections

I need to update the data in my Output table based on the values from the Lookup table. Please see the below screenshot which gives the correct output for One of the value from the lookup file.

Please find the screenshot of Lookup file too

Iam able to update the value for oppose which is 1750, similar way i need to iterate and write the values for the corresponding mappings into the output table.

Kindly help me to find a solution for this issue.

Thanks

Anand

[Anonymous]

Hi

I don't have an overview of your source data and target data, you can try to use a tFlowToIterate component to iterate each row of tFileInputExcel, the job looks like:

tDBConnection

|onsubjobok

tFileInputExcel--main(row1)--tFlowToIterate--tDBInput--main-tUnique--tJavaRow--out1--tDBOutput

on tJavaRow: define the output columns, eg:

output_row.Action_user=4;

if(input_row.OPOSICAO=="true"){

output_row.ACTION_TYPE=(String)globalMap.get("row1.Column1);

}else{

output_row.ACTION_TYPE="false";

]

// other other output columns.

Regards

Shong

[AV2022]

Hi Shong,

As always thanks for your quick help.

My source DB is Access DB and target DB is Oracle. Data from excel i am using as a lookup.

I have made the changes like you have mentioned, but when i run the job i am getting the output for Action_Type as false only. Do i need to add tMap after tJavarow to get the desired out.

My requirement is whenever the data from DB input columns OPASICO,RENOVAÇÕES, PEDIDO DE REGISTO,PEDIDO_DE_CONCESSÃO,PEDIDO DE RENOVAÇÃO are TRUE it should be replaced with the corresponding mapping values from the Excel look up.

The DB output Column Action_Type should be updated with the values from IPAS_ACTION_TYPE column when the input condition is TRUE.

Please help me with this situation

Thanks

Anand

[Anonymous]

what is the data type of OPOSICAO column you defined on the schema?

if boolean type, the code on tJavaRow

output_row.Action_user=4;

if(input_row.OPOSICAO==true){

output_row.ACTION_TYPE=(String)globalMap.get("row1.Column1);

}else{

output_row.ACTION_TYPE="false";

}

if string type, the code on tJavaRow:

output_row.Action_user=4;

if(input_row.OPOSICAO.equals("true")){

output_row.ACTION_TYPE=(String)globalMap.get("row1.Column1);

}else{

output_row.ACTION_TYPE="false";

]

Maybe I don't understand your requirement well, it is better if you could provide some example data to explain it.

Data in tDBInput

Data in Excel

Expected result on Oracle table.

Regards

Shong

[AV2022]

Hello Shong

Below is the screenshot of how i have done my connection.

My tDBInput (Source is Access DB)

Columns to be considered from tDBInput are:

OPOSICAO

RENOVACOES

PEDIDO_DE_REGISTO

PEDIDO DE CONCESSÃO

PEDIDO DE RENOVAÇÃO

This columns are all in Boolean type in source DB but i changed the Data type to string, since my output is Oracle DB which don't support boolean values, please see the below screenshot.

tFileInputExcel

Now i have connected both tDbinput and tfileinput excel into a tmap. My requirement is whenever the values for above mentioned 5 columns from the source DB are TRUE, the output should be updated with the values that are mapped in the tinput excel.

eg: If OPOSICAO is TRUE the output column that is ACTION_TYPE should be updated with value 1750.

Similarly for RENOVACOES and other columns too.

My Tmap screenshot

 

I am able to update one record at a time with the expression

row2.OPOSICAO=="TRUE"?"1750":"FALSE"

I want the data to be updated in a loop, Once the data is updated for OPOSICAO then next it should update for RENOVACOES and like wise for the next columns too.

tDBOutput

 

I understood we need to add java component also into my present job like you have mentioned previously. Please let me know how i need to setup my connection as per my requirement and the Java code also.

Thanks

Anand

[AV2022]

@Shicong Hong​ 

Hello Shong,

Could you please help me to resolve this issue.

Thanks

Anand