topic Re: Create Multiple Tabs with single excel file based on the column in Talend Studio

Create Multiple Tabs with single excel file based on the column

Chaitanyas — Thu, 27 Apr 2017 15:55:40 GMT

Hi Team,

I have requirement that I need to populate multiple sheets in single excel file based on Column.

Let me illustrate with an example I have members belongs to separate states and I need create a excel with the State name as tab and its associated members in each tab as a output.

Could you please help me in providing the solution, that how do we achieve in Talend. Right now we are using SAP Crystal Report to populate such type of files.

Thanks In-advance.

Regards,

Chaitanya

Re: Create Multiple Tabs with single excel file based on the column

Anonymous — Fri, 28 Apr 2017 06:04:05 GMT

Hi
You need to iterate each record and append it to the same file. eg:
tFileInputDelimited--main(row1)--tFlowToIterate--iterate--tFixedFlowInput--main--tFileOutputExcel
Assuming there are three columns: id, name and state on the schema of tFileInputDelimited, define the same schema on tFixedFlowInput to generate the current record, set the values like:
for id column: (Integer)globalMap.get("row1.id")
for name column: (String)globalMap.get("row1.name")
for state column: (String)globalMap.get("row1.state")

on tFileOutputExcel, check the 'Append existing file' and 'Append existing sheet' box, define the Sheet Name parameter as:
(String)globalMap.get("row1.state")

Hope it helps you!

Regards
Shong

Re: Create Multiple Tabs with single excel file based on the column

bhagyarekha — Fri, 28 Apr 2017 06:39:13 GMT

Hi All

Thank for posting question and answer.i learned new thing today.i did that one i got it

Re: Create Multiple Tabs with single excel file based on the column

Anonymous — Fri, 28 Apr 2017 10:08:27 GMT

@bhagyarekha wrote:

Hi All

Thank for posting question and answer.i learned new thing today.i did that one i got it

Glad that it helps you, thanks for your kudo and positive feedback.

Re: Create Multiple Tabs with single excel file based on the column

Chaitanyas — Sat, 13 May 2017 01:46:39 GMT

Thank you very much shong! I will try to implement today and post you the questions if I face any issues on implementing your solution.

Thanks much again for your response.

Regards,
Chaitanya

Re: Create Multiple Tabs with single excel file based on the column

Chaitanyas — Sat, 13 May 2017 02:28:23 GMT

If my source is from TOracle Input, then do I need to follow the same steps. I'm new to use the tfixedFlowInput component. Could you please provide me the few screenshots in implementing the job.

Thanks
Chaitanya

Re: Create Multiple Tabs with single excel file based on the column

Chaitanyas — Sat, 13 May 2017 03:01:31 GMT

I got the solution and successfully the file is created with the desired output.

Really its awesome Shong!

I always appreciate your patience in providing the solution to every issue.

Thanks once again!!!!!!

Thanks,

Chaitanya

Re: Create Multiple Tabs with single excel file based on the column

Anonymous — Tue, 01 May 2018 03:15:04 GMT

Unfortunately I can't find component tFlowToIterate

Do you know where I can find it?

Using Talend v 6.5.0 Build M2

Thanks in advance

Mick Arundell

Re: Create Multiple Tabs with single excel file based on the column

cterenzi — Tue, 01 May 2018 13:32:53 GMT

tFlowToIterate is in the Orchestration folder in the palette.

Re: Create Multiple Tabs with single excel file based on the column

Anonymous — Tue, 01 May 2018 22:53:50 GMT

Thankj you, I've found it now.

Re: Create Multiple Tabs with single excel file based on the column

Anonymous — Mon, 07 May 2018 13:08:07 GMT

i have a similar situation here. I am trying to pull the data from Database and load it in Excel file which has many sheets. I tried to do it by pulling DB tables right into the graph and connected with tMap and then to tFIleOutputExcel. It failed. After doing random changes in properties, the execution runs successfully but i lost the other sheets. Also, the sheet which got the data lost top-4 rows which has some grammatical information about the business. Please me to achieve the output (with diagram if possible). Thanks in advance. Let me know if you want to see the excel file. I will attach it in my next message

Re: Create Multiple Tabs with single excel file based on the column

Anonymous — Mon, 07 May 2018 23:31:18 GMT

I have mine working like this

In tFlowToIterate I set

and in tFileOutputExcel_1 I set Sheet name to (String)globalMap.get("TabName")

I have been careful using capitalisation and procedure/function names.

Good luck

Mick

Re: Create Multiple Tabs with single excel file based on the column

Anonymous — Tue, 08 May 2018 04:56:24 GMT

I am not creating a new excel sheets. The excel is already present, i just need to load the data into multiple sheets which is not happening. Please suggest

Re: Create Multiple Tabs with single excel file based on the column

Anonymous — Tue, 08 May 2018 05:00:30 GMT

I've found that if I set a new worksheet name a new sheet is created, if I set name to an existing worksheet then that existing worksheet is selected. I did have to set "Append existing file" and "Append existing sheet" to true otherwise process would keep overwriting the same line.

Mick

Re: Create Multiple Tabs with single excel file based on the column

Anonymous — Tue, 08 May 2018 05:02:04 GMT

Thanks Mick, i will try this.

Re: Create Multiple Tabs with single excel file based on the column

Anonymous — Tue, 08 May 2018 05:05:06 GMT

How to keep existing information in the sheet as it is. My sheets are having some business information in the top 3 rows. Also, i don't want to lose other sheets in that excel. Data can be flown from Database to multiple sheets in excel all the time. Please suggest

Re: Create Multiple Tabs with single excel file based on the column

Anonymous — Tue, 08 May 2018 05:30:02 GMT

Append will keep existing data and add new data underneath

Re: Create Multiple Tabs with single excel file based on the column

Anonymous — Tue, 08 May 2018 06:05:08 GMT

When i use a new xlsx file then the graph is working and data is loading. If i use the excel file given by my client which has some formulas in different sheets then the normal DM-->Main-->tOutputFileExcel flow is getting hanged. Is there any other component which can handle output excel file which has formulas?

Re: Create Multiple Tabs with single excel file based on the column

Anonymous — Tue, 08 May 2018 06:14:00 GMT

Can you try using tExcelSheetOutput with Open and Save components too?

Re: Create Multiple Tabs with single excel file based on the column

Anonymous — Tue, 08 May 2018 06:34:28 GMT

tExcelSheetOutput is not available. Exchange is also not working. Yesterday i tried to download tFileExcel thru exchange but its not happening. After 40% of download the download screen disappeared.