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

Tags (3)
2 ACCEPTED SOLUTIONS

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
6 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
Six 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