Transferring data from multiple sheets excel file to multiple tables

One Star

Transferring data from multiple sheets excel file to multiple tables

I want to transfer data from Excel to SQL. My Excel worksheet has 20 different sheets having data to be copied in 20 different tables.
I want to create a single job to perform this task in a step by step manner.
My tables have dependencies so it is very important to copy data to these tables in a specific order.
I want to have 20 steps in a single job.
Any help (online source / video) regarding how to go about this task would be appreciated.
Seventeen Stars

Re: Transferring data from multiple sheets excel file to multiple tables

This is indeed a very simple task.
You have to create as much as you have sheets these kind of subjobs:
tFileInputExcel --> tOracleOutput
You question sounds like you do not have invested not so much time to learn the tool yet.
I have never seen a tool what does not depend on an amount of time to learn it.
I suggest you start with a single sheet and a single table if you got it you could reproduce it for all of your sheets and tables.
One Star

Re: Transferring data from multiple sheets excel file to multiple tables

May be my question was missing the proper details.
I have started with the single sheet. But with the multiple sheets I needed to maintain the order in which I copy the data as tables were related.
I have managed to do that. I have used trigger -> OnComponentOk from the first table's output to the second table's input. And so on.
Thanks.
Four Stars

Re: Transferring data from multiple sheets excel file to multiple tables

I would recommend to use OnSubJobOk link from input component of your first table data transfer. This will ensure that table data load is executed after completion of first table data load.
Thanks
Vaibhav
Moderator

Re: Transferring data from multiple sheets excel file to multiple tables

Hi Yuvraj Solanki,
Please have a look at KB article about TalendHelpCenter:What is the difference between OnSubjobOK and OnComponentOK??
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Five Stars

Re: Transferring data from multiple sheets excel file to multiple tables

you have to design job like below to solve this issue.
openDbconnection---oncomponentOk----tFileInputExcel(sheet 1)------tSQlOuput
and add rest of sub jobs using subjobok link. refer image for more details.
One Star

Re: Transferring data from multiple sheets excel file to multiple tables

Thanks for replies.
I got the difference between OnComponentOk and OnSubJobOk.
I think any approach will work in my case but with your views I learned the better approach to design my job.
Thanks,
- Yuvraj.
Moderator

Re: Transferring data from multiple sheets excel file to multiple tables

Hi Yuvraj Solanki,
Great you can get more from this use case. Shall we mark this topic as "resolved" so that other community users will be informed that this thread has been resolved.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.