topic Re: [resolved] Read using tfileinputexcel and load into Oracle db in Talend Studio

[resolved] Read using tfileinputexcel and load into Oracle db

Anonymous — Fri, 19 Dec 2014 14:21:16 GMT

Hi ,
I am a newbee to talend and not a java person.I want to perform a small task exploring talend components. I am using Talend Open Studio for data integration for the purpose.
I have an excel sheet with 2 records like this in below structure.From this sheet,i have to read each record and do one insert into one table and next 2 insert into second table . After this it should read second row and insert 1 record to 1st table and then next 2 inserts to second table.So for 3 loops,the output will be like below:
Input File
Taxt_Num Taxt_type   Sub_org taxt_status-flg State
------------------------------------------------------------------------
A_1000 commercial 100 Y we
A_1000 commercial 100 Y ga

Output Table1 :
Imtx_Num    imtx_type Sub_org
--------------------------------------------
A_1000_1 commercial   100
A_1000_2 commercial   100
A_1000_3 commercial   100

Output Table2:
Imtx_Num imtx_type taxt_status_flg state
-------------------------------------------------------------------
A_1000_1    commercial w Y   we
A_1000_1 commercial y Y   ga
A_1000_2 commercial K Y   we
A_1000_2 commercial w Y   ga
A_1000_3 commercial y Y   we
A_1000_3 commercial y Y   ga

how to add a sequence number (x+1) to 1st column in each loop.Should I have to write a java code to add sequence number to 1st column in every loop.Or can we achieve this in tmap ?

Hoping a help from the community.
Thanks in Advance,
Seena

Re: [resolved] Read using tfileinputexcel and load into Oracle db

Anonymous — Fri, 19 Dec 2014 21:31:39 GMT

hi, Seena..
you can create a string variable in tMap and it should look like this:

row1.Taxt_Num+"_"+Integer.toString(((Integer)globalMap.get("tLoop_1_CURRENT_VALUE")))

hope that'll helps!

Re: [resolved] Read using tfileinputexcel and load into Oracle db

Anonymous — Mon, 22 Dec 2014 06:20:16 GMT

Hi sky_angel,
Thanks for your response.
In this case so i should use a tloop component to call this variable inside tmap right?
I havent used a tloop component yet. I tried connecting a tloop to tmap.But its not possible. Can u help me how to use a tloop in my job?
Currently my job looks like this
tfielist-->ttFileinputexcel-->tmap-->toracleoutput
Iam reading files ,mapping it and loading it to oracle tables. 2 loops /iteration i should perform:The first loop is like this:
it reads 1 row from file and do 2 insert(1 insert in 1st output table,and next output table)
Then it read second row and do 2 nsert(1 insert in 1st output table,and next output table)
Second loop is :
When it reading second row, the first column taxt_num should be incremented sequentially.
input: A_1000 Output: A_1000_1
Can any1 help me in achieving this 2 loop?
It will be very helpfull.
Many Thanks,
Seena

Re: [resolved] Read using tfileinputexcel and load into Oracle db

Anonymous — Mon, 22 Dec 2014 06:22:33 GMT

Hi sky_angel- Thanks for your response.
In this case so i should use a tloop component to call this variable inside tmap right?
I havent used a tloop component yet. I tried connecting a tloop to tmap.But its not possible. Can u help me how to use a tloop in my job?
Currently my job looks like this.I am attaching a screen shot of my job.
tfielist-->ttFileinputexcel-->tmap-->toracleoutput
Iam reading files ,mapping it and loading it to oracle tables. 2 loops /iteration i should perform:The first loop is like this:
it reads 1 row from file and do 2 insert(1 insert in 1st output table,and next insert of same row in 2nd output table)
Then it read second row and do 2 nsert(1 insert in 1st output table,and next insert of same row in 2nd output table)
Second loop is :
When it reading second row, the first column taxt_num should be incremented sequentially.
input: A_1000 Output: A_1000_1

Can any1 help me in achieving this 2 loop?
It will be very helpfull.
Many Thanks,
Seena

Re: [resolved] Read using tfileinputexcel and load into Oracle db

Anonymous — Mon, 22 Dec 2014 12:28:38 GMT

Hi ,
Can any1 helps me on the above query?
Thanks and Regards,
Seena

Re: [resolved] Read using tfileinputexcel and load into Oracle db

Anonymous — Mon, 22 Dec 2014 12:56:08 GMT

Hi Seena,
Before we proceed, I would like to have confirmation on a few points:
1. Every time the processing takes place, is the input file(s) going to have exactly 2 rows?
2. 1st iteration reads both rows, & 2nd iteration reads only the 2nd row (i.e. last row)??
3. The output data in your first post is a bit misleading. You showed 3 entries in Table1 & 6 in Table2. Whereas, if I've understood your last post correctly, there would be 3 rows in Table1 & 3 in Table2, where the 3rd row in both the tables would have incremented 'taxt_num' column.
4. Also, please explain how the values in Table2 column 'taxt_status_flg' gets populated.
MathurM

Re: [resolved] Read using tfileinputexcel and load into Oracle db

Anonymous — Tue, 23 Dec 2014 06:13:26 GMT

Hi Mathur,
Many Thanks for your response.I will clarify the points.
1.The input file not has 2 rows. I gave 2 rows as an example. Each input excel file has 100s of records.
2. 1st iteration should read only 1st record. And it is inserted in 1st output table and 2nd output table(two insertion only in 2nd output table).
3. 2nd iteration should read 2nd row and inserts in 1st output table and also in 2nd output table(two insertion only in 2nd output table).
4. Hence if i have 100 rows in sheet, table1 will have 100 rows. And table2 will have 200 rows.
5. 'taxt_status_flag' column ,'state','type','state_tx_flg' columns data is not coming from sheet. I have to hardcoded this values in tmap for table2.
Sorry I have provided input file wrongly in my earlier post. I am reposting the input file and expected output. Also i am posting the sql scripts which iam using to do this insert.
1st iteration
------------------
insert into table1 (imtx_num, imtx_type, sub_org,weight) values (row1.imtx_num+_1,row1.imtx_type,row1.sub_org,row1.weight)
insert into table2 (imtx_num, imtx_type, taxt_status_flg,state,type) values (row1.imtx_num+_1,row1.imtx_type,'Y','Tulsa','PTNR_IMPORT')
insert into table2 (imtx_num, imtx_type, taxt_status_flg,state,type,state_tx_flg) values (row1.imtx_num+_1,row1.imtx_type,'N','Warwick','PTNR_EXPORT','Y')
2nd iteration
---------------------
insert into table1 (imtx_num, imtx_type, sub_org,weight) values (row1.imtx_num+_2,row1.imtx_type,row1.sub_org,row1.weight)
insert into table2 (imtx_num, imtx_type, taxt_status_flg,state,type) values (row1.imtx_num+_2,row1.imtx_type,'Y','Tulsa','PTNR_IMPORT')
insert into table2 (imtx_num, imtx_type, taxt_status_flg,state,type,state_tx_flg) values (row1.imtx_num+_2,row1.imtx_type,'N','Warwick','PTNR_EXPORT','Y')
3rd iteration
----------------
insert into table1 (imtx_num, imtx_type, sub_org,weight) values (row1.imtx_num+_3,row1.imtx_type,row1.sub_org,row1.weight)
insert into table2 (imtx_num, imtx_type, taxt_status_flg,state,type) values (row1.imtx_num+_3,row1.imtx_type,'Y','Tulsa','PTNR_IMPORT')
insert into table2 (imtx_num, imtx_type, taxt_status_flg,state,type,state_tx_flg) values (row1.imtx_num+_3,row1.imtx_type,'N','Warwick','PTNR_EXPORT','Y')
Input File
Taxt_Num Taxt_type Sub_org weight
-----------------------------------------------------------
A_1000 commercial 100 3.3
A_1000 commercial 100 2.2
A_100 commercial 100 7.8
Output Table1 :
Imtx_Num imtx_type Sub_org weight
-----------------------------------------------------
A_1000_1 commercial 100 3.3
A_1000_2 commercial 100 2.2
A_1000_3 commercial 100 7.8
Output Table2:
Imtx_Num imtx_type taxt_status_flg state Type State_tx_flg
-------------------------------------------------------------------------------------------------------------
A_1000_1 commercial Y Tulsa PTNR_IMPORT
A_1000_1 commercial N Warwick PTNR_EXPORT Y
A_1000_2 commercial Y Tulsa PTNR_IMPORT
A_1000_2 commercial N Warwick PTNR_EXPORT Y
A_1000_3 commercial Y Tulsa PTNR_IMPORT
A_1000_3 commercial N Warwick PTNR_EXPORT Y
Thanks and Regards,
Seena

Re: [resolved] Read using tfileinputexcel and load into Oracle db

Anonymous — Tue, 23 Dec 2014 09:43:03 GMT

Hi Seena,
Thanks for the clarification.
Try the following approach:

This should help you.
Let me know it it still doesn't.
MathurM

Re: [resolved] Read using tfileinputexcel and load into Oracle db

Anonymous — Tue, 23 Dec 2014 09:52:30 GMT

tMap screen-shot

Re: [resolved] Read using tfileinputexcel and load into Oracle db

Anonymous — Tue, 23 Dec 2014 10:24:09 GMT

Hi Mathur,
Thanks for guiding me.It perfectly fixes my issue.I am loving Talend for these beautiful components.tjava,tfixedflowinput all these components are amazing.Even though I am not a java resource,I am able to work using this components.
Thankyou Mathur.
Best Regards,
Seena