topic Re: Split rows into multple sheets in Talend Studio

Split rows into multple sheets

rchanda — Wed, 20 Nov 2019 21:15:22 GMT

Hello, Can someone help me with this requirement.

I have a requirement to load 2 million rows into an excel file. Since excel sheet limit is around 1 million, I would like to load first million into sheet 1 and next million rows into Sheet 2. And Sheet 3 if there are more than 2 million rows in future.

I am using Talend 6.5.1X version

Thank you in advance

Regards

Re: Split rows into multple sheets

TRF — Wed, 20 Nov 2019 21:24:04 GMT

I suggest you to load rows into separate csv files using "max rows per file" property (this not the exact name). Then iterate over these generated files with tFileList to load them into the corresponding Excel sheet.

Re: Split rows into multple sheets

rchanda — Wed, 20 Nov 2019 21:45:09 GMT

Thank you TRF for your quick reply. Is there any other better way to load the data directly into Excel sheet?

Re: Split rows into multple sheets

TRF — Wed, 20 Nov 2019 21:51:34 GMT

Don't think with standard components but you should try them first.
Maybe other possible solutions with components from Talend Exchange.

Re: Split rows into multple sheets

rchanda — Wed, 20 Nov 2019 21:53:45 GMT

I found a solution here
https://community.talend.com/t5/Design-and-Development/resolved-Iterate-rows-into-multiple-Excel-sheets/m-p/111901#M69574

But the screenshot is missing so do not know how they did.

Re: Split rows into multple sheets

TRF — Wed, 20 Nov 2019 22:08:02 GMT

Based on the expected number of lines it could be slower than the other solution

Re: Split rows into multple sheets

rchanda — Wed, 20 Nov 2019 22:28:22 GMT

Hello TRF,

I tried your solution. I am still getting error Invalid row number (1048576) outside allowable range (0..1048575)

Am I missing some setting? Please look into the attachment.

Can you provide me the solution to iterate multiple files into multiple sheets. or provide link to any existing solution.

Talend Error.png
Talend Error2.png

Re: Split rows into multple sheets

TRF — Wed, 20 Nov 2019 22:47:24 GMT

Probably because the sheetname is static. Should be based on a global variable associated to the tFileList component such as CURRENT_FILE or NB_FILE.

Re: Split rows into multple sheets

rchanda — Wed, 20 Nov 2019 22:57:53 GMT

Thank you TRF. This solution is working.

Changed the sheet name to dynamic.

Re: Split rows into multple sheets

TRF — Thu, 21 Nov 2019 06:43:37 GMT

Thanks to mark the answer as the solution, not the question