[resolved] Is it possible to load multiple context variables in a single pass?

[talendtester]

Is it possible to load multiple values from a Excel spreadsheet into multiple context variables in a single pass?
My goal is to have 3 context variables I pass data from a spreadsheet to and then use the 3 context variables in SQL against the Oracle database.
For example if I have a spreadsheet with three columns and three rows:
Col1|Col2|Col3
1|abc|123
2|def|456
3|ghi|789

My job looks like:
tFileInputExcel > tIterateToFlow > tContextLoad > tOracleInput
So far I am able to load the first context variable fine. Anyone know if how I can get the 2nd and 3rd context variable loaded with values?

[Anonymous]

Hi
Why don't you put tFileInputExcel->tContextLoad as a subjob?
Regards,
Pedro

[talendtester]

Am I doing something wrong with the tContextLoad?
When I tried tFileInputExcel->tContextLoad, my schema looked like:
tFileInputExcel (Input -Main):
---------
Col1
Col2
Col3
tContextLoad (Output):
---------
key1
value1
key2
value2
key3
value3
I was only able to get key and value to work successfully for the first one, the 2nd and 3rd were not being loaded with the values in the corresponding columns of the Excel spreadsheet.

[Anonymous]

Hi
You can create subjob as follows.
Regards,
Pedro

[talendtester]

Hi pedro,
That looks like only 1 column, how do I go from 3 columns to 3 context variables?
In the tLogRow I am expecting the following output:
.--------+-------+---------.
| tLogRow_2 |
|=-------+-------+--------=|
|Col1|Col2|Col3|
|=-------+-------+--------=|
| 1| abc| 123|
'--------+-------+---------'

.--------+-------+---------.
| tLogRow_2 |
|=-------+-------+--------=|
|Col1|Col2|Col3|
|=-------+-------+--------=|
| 2| def| 456|
'--------+-------+---------'

.--------+-------+---------.
| tLogRow_2 |
|=-------+-------+--------=|
|Col1|Col2|Col3|
|=-------+-------+--------=|
| 3| ghi| 789|
'--------+-------+---------'

Currently my output is:
Warning: Parameter "1" is a new parameter of tContextLoad_1
Warning: Parameter "2" is a new parameter of tContextLoad_1
Warning: Parameter "3" is a new parameter of tContextLoad_1
Warning: Parameter "DB3" has not been set by tContextLoad_1
Warning: Parameter "DB2" has not been set by tContextLoad_1
Warning: Parameter "DB1" has not been set by tContextLoad_1

Attached are the screen shots of my job changed based on your feedback:

[Anonymous]

Just to clarify, you are storing the names of your DB columns in the excel file.
is this correct?
in each column of the excel file you have a DB name:
COL1; COL2; COL3
column_name_1; column_name_2; column_name_3;

if this is the case, take a look at the "tSplitRow" component. Please see screenshots below:

[talendtester]

John,
You are correct the titles are in my data source file.
I changed my job to be tFileInputDelimited > tSplitRow > tLogRow the values of the context variables look good.
When I change my job to be tFileInputDelimited > tSplitRow > tFlowIterate Iterate tOracleInput > tLogRow the row counts look good till the tOracleInput.
How do I use the context variables in the tOracleInput?
This doesn't seem to be finding and using the values of the context variables:
"SELECT col1, col2, col3
FROM excelToSQL
WHERE col1='"+context.DB1+"'
AND col2='"+context.DB2+"'
AND col3='"+context.DB3+"'
"
In the tSplitRow I have tried making the columns mapping be both Col1, Col2, Col3 and
Col1,
Col2,
Col3
it doesn't seem to matter either way.

[Anonymous · Accepted solution]

you simply have to set them
try something like this:
tfileInput--row-->tFlowToIterate--iterate-->tFixedFlowInput--row-->tSplitRow--row-->tContextLoad------onComponentOK----->tOraceInput
the trick here is that you have 3 vars in a single line of the input file, so you want to iterate for each line of the file.
use the tFlowToIterate to accomplish the iteration, then get the row back out of the global map with a tFixedFlowInput.

[talendtester]

Awesome! Thanks for all the help, finally got it working 100%.

[Anonymous]

Hi Talenders,

i am new to talend ,can any one explain that how to create contexts , how to use contexts in a job