Create Multiple Tabs with single excel file based on the column

Five Stars

Create Multiple Tabs with single excel file based on the column

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


Accepted Solutions
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
Community Manager

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


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.Smiley Wink

 

----------------------------------------------------------
Talend | Data Agility for Modern Business

All Replies
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
Seven Stars

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

Hi All

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

Community Manager

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


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.Smiley Wink

 

----------------------------------------------------------
Talend | Data Agility for Modern Business
Five Stars

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

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
Five Stars

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

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
Five Stars

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

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

Six Stars

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

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

Ten Stars

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

tFlowToIterate is in the Orchestration folder in the palette.
Six Stars

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

Thankj you, I've found it now.

Eight Stars

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

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

Six Stars

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

I have mine working like this

image.pngIn tFlowToIterate I set

image.png

 

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

image.png

 

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

 

Good luck

Mick

 

Eight Stars

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

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

Six Stars

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

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

Eight Stars

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

Thanks Mick, i will try this.

Eight Stars

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

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

Six Stars

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

Append will keep existing data and add new data underneath

Eight Stars

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

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?

Six Stars

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

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

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

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.