[resolved] Read using tfileinputexcel and load into Oracle db

One Star

[resolved] Read using tfileinputexcel and load into Oracle db

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
One Star

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

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!
One Star

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

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
One Star

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

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
One Star

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

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

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

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
One Star

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

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
One Star

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

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





This should help you.
Let me know it it still doesn't. Smiley Happy
MathurM
One Star

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

tMap screen-shot

One Star

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

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