limit no of records in excel sheet

hi..
i want to know that is it possible to load the data into excel sheets?
If so then how to load 1-50 records into 1st sheet,51-100 records into 2nd excel sheet and so on up to 5 sheets of a single excel file.
Please help me how to do this..
Thanks..Smiley Happy
12 REPLIES
Community Manager

Re: limit no of records in excel sheet

Hi
You need to set a dynamic sheet name and append the records into the existing file and sheet. I want to know what does the sheet name looks like? "Sheet0", "Sheet1", "Sheet2"?
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: limit no of records in excel sheet

Thanks...How to set dynamic sheet name..? my sheet names are like "Sheet1", "Sheet2", "Sheet3" etc..
Community Manager

Re: limit no of records in excel sheet

Hi
Here is an example job showing you how to split the records and set a dynamic name, please see my screenshots.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: limit no of records in excel sheet

Hi Shong,
Am able to write to multiple sheets using the above job design. But i need to write data into next sheet once the first sheet gets filled up with data in maximum no of rows(65K).. My input will incrementally increase..
Manoj
Community Manager

Re: limit no of records in excel sheet

Hi
Change the no of records to 65k ,
context.sheetName="Sheet"+(input_row.id/65000+1);
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: limit no of records in excel sheet

Hi
Change the no of records to 65k ,
context.sheetName="Sheet"+(input_row.id/65000+1);
Best regards
Shong

During above idea implementation , am getting 2 sheets in excel as my number of rows is 90K. But in those sheets there are only one record in each sheet. I mean ,in the first sheet the 65th thousand record is there in first row and similarly the same happens in second sheet. I thin the data is over written. Plz guide me further.
Manoj
Community Manager

Re: limit no of records in excel sheet

Hi
You only get one records in each sheet?
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: limit no of records in excel sheet

S only one record in each sheet
One Star mpa
One Star

Re: limit no of records in excel sheet

Manoj88,
Isn't this happening because you haven't enabled "Append existing sheet" in the tFileOutputExcel component?
Regards
One Star

Re: limit no of records in excel sheet

Manoj88,
Isn't this happening because you haven't enabled "Append existing sheet" in the tFileOutputExcel component?
Regards

Actually i've selected the option which u have told.But when I tried after removing ,the last value which came once is repeated fully in all rows for both sheets.
One Star

Re: limit no of records in excel sheet

Hi Manoj /Shong/Sabrina,
I am unable to see job design 
i have some 10 million records which i want to write in excel sheets , sheet name can be sheet0, sheet1 or sheet2 as it use to be default
i am unable to write in multiple sheets 
can you please show me job design  , i want to implement same as Manoj requirement is "But i need to write data into next sheet once the first sheet gets filled up with data in maximum no of rows(65K).. My input will incrementally increase.."


Thanks
Shivi
Community Manager

Re: limit no of records in excel sheet

Hi Shivi  
This job was rather old, I don't have job design any more. Can you please report a new topic for your problem? 
Thanks!
Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business