Split rows into multple sheets

[rchanda]

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

[TRF]

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.

[rchanda]

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

[TRF]

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

[rchanda]

I found a solution here
https://community.talend.com/t5/Design-and-Development/resolved-Iterate-rows-into-multiple-Excel-she...

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

[TRF]

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

[rchanda · Accepted solution]

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

[TRF]

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.

[rchanda]

Thank you TRF. This solution is working.

Changed the sheet name to dynamic.

[TRF]

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