One Star

Want to create multiple sheet in tFileOutputExcel

Hi,
I am new in Talend. I am stucked at a point.
I want multiple sheet in a single excel output . The number of sheet depend on contain.
let takes an example
main excel
id | category | description
1 | wiki | asdfkj
2 | youtube | asdfhjg
3 | picasa | asdfjkah
4 | wiki | asdfkj
5 | youtube | asdfhjg
6 | picasa | asdfjkah
now based on category i need the sheet should be created . and one more thing category can be more so cannot fixed on 3 or 4 filters
15 REPLIES
Community Manager

Re: Want to create multiple sheet in tFileOutputExcel

Hi
Sorry, I don't understand you very well, what does the result look like?
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Want to create multiple sheet in tFileOutputExcel

Let me clear it again
main excel input file
=================
id | category | description
1 | wiki | asdfkj
2 | youtube | asdfhjg
3 | picasa | asdfjkah
4 | wiki | asdfkj
5 | youtube | asdfhjg
6 | picasa | asdfjkah
Required output
===========
Wiki (sheet 1)
id| description
----------------
1 | fasdfasdf
4 | dfgsdfgsdfg
youtube (sheet 2)
id| description
----------------
2 | fasdfasdf
5 | dfgsdfgsdfg
picasa (sheet 3)
id| description
----------------
3 | fasdfasdf
6 | dfgsdfgsdfg
Community Manager

Re: Want to create multiple sheet in tFileOutputExcel

Hi
Ok, I understand what you need now, you need to iterate each row and set a dynamic sheet name. For example:
tFileInputExcel---row1--tFlowToIterate--iterate-->tFileOutputExcel
on tFileOutputExcel, set the 'Sheet name' field as: (String)gloalMap.get("row1.category")
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Want to create multiple sheet in tFileOutputExcel

Thanx for your reply , but I cannot connect
tFlowToIterate--iterate-->tFileOutputExcel
Apart from it I have tried to set sheet name using context variable .
In that case the sheet name takes only the initial value of the variable but not the updated values .
Community Manager

Re: Want to create multiple sheet in tFileOutputExcel

Hi
Sorry, it was a wrong job design, the correct job looks like:
tFileInputExcel---row1--tFlowToIterate--iterate-->tFixedFlowInput--main--tFileOutputExcel
on tFixedFlowInput, generate each row of row1, define three columns on schema,
column:value
id:row1.id
category:row1.category
description:row1.description
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Want to create multiple sheet in tFileOutputExcel

Hi,
I got some another issue.
It is creating the sheet with dynamic name , but at the end Excel file contain only one sheet and only one record
Community Manager

Re: Want to create multiple sheet in tFileOutputExcel

Hi
How do you set the sheet name in your case? Some screenshots of job will be more helpful for us understanding the problem.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Want to create multiple sheet in tFileOutputExcel

Hi,
I have used
((String)globalMap.get("row1.category"))
With Regards
Community Manager

Re: Want to create multiple sheet in tFileOutputExcel

Hi
Check the option 'Append existing file' and 'Append existing sheet', it should be ok now.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Want to create multiple sheet in tFileOutputExcel

Hi,
When I , Check the option 'Append existing file' and 'Append existing sheet' it is creating a file with 0 records (0KB)
Community Manager

Re: Want to create multiple sheet in tFileOutputExcel

Hi
You must do something wrong, I tested it and it works on v4.2.2. Please upload some screenshot of job, specially the setting of tFixedFlowInput component.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Want to create multiple sheet in tFileOutputExcel

Hi,
I have uploade the images.
Apart from it my excel format
ID | COUNTRY | CITY |WIKI | YTUBE | TITLE1 |YTUBE1 | TITLE1
and i m creating dynamic sheet based on country
with regards
One Star

Re: Want to create multiple sheet in tFileOutputExcel

Any one has any solution
Community Manager

Re: Want to create multiple sheet in tFileOutputExcel

Hi
Not sure it was a bug on v4.2.0, I am working on v4.2.2 and it works fine.
Do you install Excel 2003 or 2007/2010 on your machine? Because I install excel 2007 on my machine, so I change the output file name to out.xlsx and it works.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Want to create multiple sheet in tFileOutputExcel

I checked that option and followed what the article said. Now at the output I have an excel with the desired sheets, but the problem is that the sheets are "empthy". I am using Talend Enterprise 5.4.2. No data is passed through the components tFlowtoIterate and tFixedFlowInput. I am not expecting them to catch values, but they don't let previous outputs from components such as tFileInputDelimited or tFileInputExcel or any other output mappings to be used within further mappings.
Real Solution:
You have to define a key (local global variable) in the tFlowToIterate for each column that you want at output. After tFixedFlowInput you add a tMap where you map each output desired column with one of the local defined global variable in the tFlowToIterate.
In the attachment you can find some pictures with detailed information on how this works.
Pictures to solution LINK from my dropbox account to a zip file containing 6 small screenshots.