topic Re: [resolved] Is it possible to load multiple context variables in a single pass? in Talend Studio

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

talendtester — Sat, 16 Nov 2024 12:20:14 GMT

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?

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

Anonymous — Thu, 08 Mar 2012 02:25:59 GMT

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

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

talendtester — Thu, 08 Mar 2012 14:24:48 GMT

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.

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

Anonymous — Fri, 09 Mar 2012 08:59:20 GMT

Hi
You can create subjob as follows.
Regards,
Pedro

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

talendtester — Fri, 09 Mar 2012 15:15:49 GMT

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:

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

Anonymous — Fri, 09 Mar 2012 16:07:20 GMT

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:

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

talendtester — Sat, 10 Mar 2012 00:05:01 GMT

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.

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

Anonymous — Mon, 12 Mar 2012 13:06:21 GMT

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.

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

talendtester — Wed, 14 Mar 2012 17:54:42 GMT

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

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

Anonymous — Tue, 20 Mar 2012 12:16:06 GMT

Hi Talenders,

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

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

Anonymous — Tue, 20 Mar 2012 12:16:46 GMT

Hi Talenders,

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