topic Re: Split big data into multiple worksheets [tFileOutputExcel] in Talend Studio

Split big data into multiple worksheets [tFileOutputExcel]

Anonymous — Mon, 14 Mar 2016 08:02:53 GMT

Hi!
I need help for this job I am current working on. I have result data from a query, output will be one Excel file (.xls) but data may exceed worksheet limit (65k). I am wondering if it is possible to split the data and put into worksheets.
Example: Result data count is 500K rows
Expected output:
Sheet1 = 65k
Sheet2 = 65k
.....and so on
It is my first time here. I appreciate any suggestion you may have.
Other info.
TOS version - 5.4
Database - MySQL
Current Job design:
tJava1 ---OnComponetOk--->tMySQLConnection ---OnComponetOk--->tMySQLInput---row1 (Main)-->tFileOutputExcel
Thank you in advance.

Re: Split big data into multiple worksheets [tFileOutputExcel]

Anonymous — Mon, 14 Mar 2016 22:44:19 GMT

I suggest you use the components tFileExcelWorkbookOpen + tFileExcelSheetOutput + tFileExcelWorkbookSave

This is the design I have in mind.
You will find all excel components in Talend Exchange.

Re: Split big data into multiple worksheets [tFileOutputExcel]

Anonymous — Tue, 15 Mar 2016 01:23:17 GMT

Hi jlolling,
I have downloaded the components and will explore. Thanks for your suggestion. Indeed your reply gives me hope.
I'll post again after.

Re: Split big data into multiple worksheets [tFileOutputExcel]

Anonymous — Wed, 16 Mar 2016 01:51:09 GMT

Hi jlolling,

I got this error using tFileExcelSheetOutput and not able to set the Sheet Name.
"The method getTargetSheetName() is undefined for the type SpreadsheetOutput".

Re: Split big data into multiple worksheets [tFileOutputExcel]

Anonymous — Sat, 28 May 2016 18:37:12 GMT

Hi got the same error message gettargetsheetname() is undefined.

regards,

Re: Split big data into multiple worksheets [tFileOutputExcel]

Anonymous — Sat, 28 May 2016 21:01:36 GMT

Hey guys,
I have just checked it. I cannot reproduce this issue. Are you sure you use the recent versions for both components: tFileExcelWorkbookOpen and tFileExcelSheetOutput ?

Re: Split big data into multiple worksheets [tFileOutputExcel]

Anonymous — Mon, 13 Mar 2017 15:43:48 GMT

Hi, got the same issue.
Below there is a structure of my test job and the error log.

Might it be I am missing some libraries to run the custom components?

Re: Split big data into multiple worksheets [tFileOutputExcel]

Anonymous — Mon, 13 Mar 2017 20:31:04 GMT

Wow, this looks like a really damaged component deployment.
I suggest you remove them completely and install them new from this source:
https://github.com/jlolling/talendcomp_tFileExcel/releases
Talend Exchange is sometimes a bit .... anyway.