tfilelist

Six Stars

tfilelist

Hello,

My Excel File name is PIGEON it has four worksheets

P

Q

R

S

1.For this I know I can do this job separately by creating different jobs but I want to do it at one go because the EXCEL file and the columns inside it will not change.

2.There will be a possible solution by using only one TFILELIST I can ITERATE to all the worksheets and the outputs of all can be attached to only one TFILECOPY.

Suggestions Please

Capture.PNG

 

 Worksheet 4-------->mssqlDB 4-------->save the file with ts

 

Capture2.PNG

                     

Nine Stars

Re: tfilelist

Hi

 

You can use one tFileInputExcel to read all the sheets

 

Regards
DGM
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: tfilelist

Can I do it for all the tables from single excel file,1 tmap,4db connections.If yes how can I if you show it with example highly appreciated

Nine Stars

Re: tfilelist

Hi,

In the case each sheet has his own structure, you can't do that. You will probably handle each sepperatly
Regards
DGM
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Eleven Stars

Re: tfilelist

Do you want to split different excel sheet in different file/DB Table?

 

- Are you tMap logic same for each sheet ? if not , you need to create a separate subjob.

- Are you using different table for different sheet ?

Regards
Abhishek KUMAR
Six Stars

Re: tfilelist

Hi,

Please Check, this is how I want and Please describe it clearly so that I can implement in the way which you say.

If I can use only one TMAP for all the worksheets can tell me how you can

Thank YoCapture.PNG

 

Nine Stars

Re: tfilelist

I think you may need to put tMap between each Worksheetn and DBn

..................... Worksheet(n) ----- tmap ----------- DN(n)

But, if there is no need to transform the data, then you can do without the tMap

I hope this will help.
Regards
DGM
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: tfilelist

I think my question is not getting a correct answer yet

 

4 worksheets in one excel

1.Can it be processed using tfilelist?

2.Each worksheet has different data and different datatypes.

3.Each worksheet should be loaded into each different table.

4.like worksheet1-->P

         worksheet2--->Q

5.Can we do this using 1tfilelist --->1tinputexcel(with 4 worksheets)---->tmap(with 4 worksheets)--->4output table P,Q,R,S

Is their anyway other than this please let me know

 

Eight Stars

Re: tfilelist

Hi @TharunJoshi ,

 

it is possible to use one tFileInputExcel and iterate over each sheet.

but before that can you let me know if there are business logics applied to the source data from excel before loading into db?

And do you have talend subscription?

 

 

Six Stars

Re: tfilelist

Yes I do have some business logic, I don't have subscription
Eight Stars

Re: tfilelist

@TharunJoshi 

 

if there are business logics, then you have to process each sheet separately. 

Six Stars

Re: tfilelist

You mean to say from four worksheets i have to download each worksheet separately and make four excel sheets and tmap

Six Stars
Eight Stars

Re: tfilelist

@TharunJoshi 

 

you don't need any custom components. 

the simplest would be to create a job like this. Screen Shot 2019-05-27 at 1.46.17 PM.png

 

 

the tFileInputExcel has option to specify the sheet name

Screen Shot 2019-05-27 at 1.41.23 PM.png

that is how it has to be configured for your 1st sheet. give the name of the sheet ("P" in your case) or 0 (automatically picks the first sheet) for the first tFileInputExcel input the corresponding schema for it and then do the business logic in tMap.

In the second subjob,screenshot-13.png

the sheet name has to be "Q" or 1, schema for the second sheet and map it to the corresponding table.Screen Shot 2019-05-27 at 1.49.08 PM.png

 

And so on for the subsequent sheets.

Let me know if you have any trouble

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog