topic Insert Data from an ExcelInput with multiple sheet into multiple table in Talend Studio

Insert Data from an ExcelInput with multiple sheet into multiple table

Anonymous — Mon, 22 Aug 2016 05:57:42 GMT

Hi,

I want to load multiple tables with datas from one Excel File with multiple sheets, the structure of the tables are different:
What i am trying to do is having a mapping that would look like :
------------->Table1
tExcelFile input ------------> tMap------------->Table2
------------->Table3
The problem when im using tExcelFile input i cant see all the sheets and use them in the tMap.
Thanks for your help,

Regards,
Mouneer

Re: Insert Data from an ExcelInput with multiple sheet into multiple table

Anonymous — Mon, 22 Aug 2016 08:33:13 GMT

Hi,
Are your multiple sheets same schema structure? Could you please give us an example for your input excel file?
Best regards
Sabrina

Re: Insert Data from an ExcelInput with multiple sheet into multiple table

TRF — Mon, 22 Aug 2016 09:06:20 GMT

Hi,
I think that if you get data from all sheets at once and need to know from which one come the data when arriving in tMap, you need an extra column in each sheet representing the sheet number.
Else, you may iterate (loop) over all sheets one by one, the loop variable telling you what's the current sheet.
Regards,
TRF

Re: Insert Data from an ExcelInput with multiple sheet into multiple table

Anonymous — Tue, 23 Aug 2016 01:40:19 GMT

Hi Sabrina,
My multiple sheets schema are not with the same structure.
please find below an exemple of excel file i need to upload.

A B C
1 1 5
1 2 y
2 4 y
3 3 5

Sheet1

A B
1 1
2 5
3 4
6 3
Sheet2

A B C D E
1 1 x 5 4
1 3 y 3 2
2 4 y 2 2
Sheet3

Thanks for your help,

Kind Regards,
Mouneer

Re: Insert Data from an ExcelInput with multiple sheet into multiple table

Anonymous — Wed, 24 Aug 2016 09:39:04 GMT

Hi,
You need to set up multiple tfileinputexcel components for your multiple schemas in one file.
[font=Verdana, Helvetica, Arial, sans-serif]The workflow should be:[/font]
tFileinputExcel(sheet1) ------------> ------------->Table1
tFileinputExcel(sheet2) ------------> tMap------------->Table2
FileinputExcel(sheet3) ------------> ------------->Table3
Best regards
Sabrina

Re: Insert Data from an ExcelInput with multiple sheet into multiple table

Anonymous — Thu, 25 Aug 2016 06:37:13 GMT

Hi Sabrina,
Thanks for your reply i couldn't use one tfileInputexcel for all the sheets,
but i managed to do what i need to do by creating in the metadata/Excel File
an Excel file per sheets and then use it in the mapping.
can we select the sheets in the tfileInputexcel without going through the metadata?
Regards,
Mouneer

Re: Insert Data from an ExcelInput with multiple sheet into multiple table

Anonymous — Thu, 08 Sep 2016 09:47:43 GMT

Hi,
If you change "Property type" from "Built_in" to "Repository", the "All sheet" option will be gray.
You have to edit file excel in metadata.
Best regards
Sabrina