topic Re: tfilelist in Talend Studio

tfilelist

TharunJoshi — Tue, 21 May 2019 14:34:04 GMT

Hello,

My Excel File name is PIGEON it has four worksheets

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

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

Re: tfilelist

Anonymous — Tue, 21 May 2019 15:58:31 GMT

You can use one tFileInputExcel to read all the sheets

read all sheets in one component.PNG

Re: tfilelist

TharunJoshi — Tue, 21 May 2019 18:19:55 GMT

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

Re: tfilelist

Anonymous — Wed, 22 May 2019 08:04:13 GMT

Hi,

In the case each sheet has his own structure, you can't do that. You will probably handle each sepperatly

Re: tfilelist

akumar2301 — Wed, 22 May 2019 13:09:18 GMT

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 ?

Re: tfilelist

TharunJoshi — Mon, 27 May 2019 13:16:42 GMT

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 Yo

Capture.PNG

Re: tfilelist

Anonymous — Mon, 27 May 2019 14:01:57 GMT

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.

Re: tfilelist

TharunJoshi — Mon, 27 May 2019 14:52:36 GMT

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

Re: tfilelist

nivedhitha — Mon, 27 May 2019 18:10:57 GMT

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?

Re: tfilelist

TharunJoshi — Mon, 27 May 2019 18:13:09 GMT

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

Re: tfilelist

nivedhitha — Mon, 27 May 2019 18:18:31 GMT

@TharunJoshi

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

Re: tfilelist

TharunJoshi — Mon, 27 May 2019 18:20:42 GMT

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

Re: tfilelist

TharunJoshi — Mon, 27 May 2019 18:24:10 GMT

https://exchange.talend.com/?_ga=2.147780144.629817436.1558953759-691553473.1558680704#marketplaceproductoverview:marketplace=marketplace%252F1&p=marketplace%252F1%252Fproducts%252F542&pi=marketplace%252F1%252Fproducts%252F542%252Fitems%252F2739

can you check this once

Re: tfilelist

nivedhitha — Mon, 27 May 2019 18:49:56 GMT

@TharunJoshi

you don't need any custom components.

the simplest would be to create a job like this.

the tFileInputExcel has option to specify the sheet name

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,

the sheet name has to be "Q" or 1, schema for the second sheet and map it to the corresponding table.

And so on for the subsequent sheets.

Let me know if you have any trouble