How to write rows that transcend Excel's row limit into a new sheet? (tfileInputDelimited ->tFileOutputExcel/tFileExcelSheetOutput )

[Anonymous]

Hi everyone, 

I work with Talend Open Studio and have a simple job like

tfileInputDelimited --> tMap -->tFileOutputExcel (Excel 2007 xlsx)

The problem is that I have more than 1.200.000 rows, so I reach the maximum number of rows in one Excel sheet. (The number of rows will increase over time.)

So I thought it would be perfect if when the limit is reached a new Excel sheet is generated and continued to be written there. 

So I added the Component tFileExcelWorkbookOpen, tFileExcelSheetOutput and tFileExcelWorkbookSave. 

The new job looks as follow: 

tGlobalVar Variable named "excel_output_sheet" 

I

[SubOk]

I

tFileExcelWorkbookOpen -[Comp OK]-> tfileInputDelimited -[Main]-> tMap  -[Main]-> tJavaRow -[Main]-> tFileExcelSheetOutput [Comp OK]-> tFileExcelWorkbookSave

In tMap I added a new column "row_number"  with the function   Numeric.sequence("s1", 1, 1). 

In tJavaRow i wrote:  globalMap.put("excel_output_sheet", Math.round(Math.ceil(input_row.rownum /1000000.0)));

(for the other columns output=input)

In tFileExcelSheetOutput I set sheet name to: "part_"+globalMap.get("excel_output_sheet")

The problem is that the query for the sheetname seems to happen before the variable is calculated, so Talend still tries to write everything into a single sheet and doesn't change it.

Can someone tell me what I'm doing wrong here, or if I need a different approach?

I have also thought about making a loop over the input, but here I only know how to get to the first n lines and not how to get to the lines n+1 to 2n. Perhaps someone here has a suitable suggestion?

My third thought was to generate multiple outputs in tmap, but since the number of lines will continue to increase, I don't think this is a long-term solution.

I hope my request is understandable.  Thank you in advance for any advice!

[Anonymous · Accepted solution]

Hi,

   One quick solution will be to write as delimited csv file by splitting the output rows to multiple files as shown below.

You can still read the output from csv file using excel. If needed, you can move it to Excel later from csv file.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[TRF]

Double query https://community.talend.com/t5/Design-and-Development/Split-rows-into-multple-sheets/m-p/207694#M11...

[Anonymous]

First of all, thank you very much for your answer.

So there is no other solution to this problem than the approach you have described?

I had hoped that there would be a way to do this without creating the additional csv files.

[TRF]

Maybe but keep it stupid simple is often a good solution!