[resolved] tFileExcelWorkbookOpen - Error while in memory saving mode

One Star

[resolved] tFileExcelWorkbookOpen - Error while in memory saving mode

Using jlolling components I try to dump in an excel more than 300K rows with 10 colums of data
It work well on my own PC but I receive a Java Heap error in production with 4gig of ram.
So I tried to use the Memory saving mode option on the tFileExcelWorkbookOpen  (Version 6.4) without success
"Exception in component tFileExcelSheetOutput_1
java.lang.IllegalArgumentException: Attempting to write a row in the range that is already written to disk.
at org.apache.poi.xssf.streaming.SXSSFSheet.createRow(SXSSFSheet.java:118)
I understand the issue but can't seems to figure the proper way to set the excel components.
I isolate as much as I can the operation. See the screenshots.
Tests I've made : 
- Append to existing rows"with the same outcome.
-"Include Header" with row start at 1
-Without thash components (DB to excel in one step)
- The thashInput with AND without the "clear cache after reading"
I'm not sure what to do next
Any ideas?
One Star

Re: [resolved] tFileExcelWorkbookOpen - Error while in memory saving mode

Trying to load screenshots 

One Star

Re: [resolved] tFileExcelWorkbookOpen - Error while in memory saving mode

 I feel like an idiot but can't seems to see the images that I uploaded...
... finaly, FYI png don't work for me while jpeg does
Seventeen Stars

Re: [resolved] tFileExcelWorkbookOpen - Error while in memory saving mode

The memory saving mode keeps not all rows in the memory and does not allow the access to the already processed rows.
This mode is designed to process a huge amount a rows with more simple transfer jobs which reads and writes without the need to have all rows available.
For your use case (Extend cell range for tables is ON) you have to switch off the memory saving mode.
If you get memory problems I suggest you split the job in multiple jobs. I hope reading all the rows will work.
I guess it could be a good idea to have an component which simply combines various sheets from different files into a new one without the need to read them all in the memory. I will check if this is possible.
You actually read all rows into the memory and than use this to create/fill a sheet. I would try to write it without using the tHashIn/Output.
Seventeen Stars

Re: [resolved] tFileExcelWorkbookOpen - Error while in memory saving mode

Probably it would work if you split the job in parts. E.g. write the first sheet and save the workbook into a temporary excel file. Next job read that file and creates the next sheet and so on. My hope is the memory footprint is lower because it is caused only by the read / write of the excel file and the actually data gathering is out of view in the scenario. 
But if a file is to big ... at the moment there is not real solution in Java and Excel. 

Does the memory saving mode helps you if you switch off the post processing (Extend cell range for tables) ?

Perhaps it could be a solution to allow this post processing in a separate step (at the moment this is not enabled by the component).

Your clear cache after reading option works when all is read and this is after finishing writing all rows. For sure too late!
One Star

Re: [resolved] tFileExcelWorkbookOpen - Error while in memory saving mode

I'll have to process less rows then
I have 3 pivots base on the data so I need to extend cells range.
thank you for your input.

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey


Talend named a Leader.

Get your copy


Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables


How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration


Downloads and Trials

Test drive Talend's enterprise products.