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

[resolved] tFileExcelWorkbookOpen - Error while in memory saving mode

manueld — Mon, 01 Dec 2014 15:42:51 GMT

Hello,
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?
Regards,
Manuel

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

manueld — Mon, 01 Dec 2014 15:48:04 GMT

Trying to load screenshots

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

manueld — Mon, 01 Dec 2014 15:52:45 GMT

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

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

Anonymous — Mon, 01 Dec 2014 17:01:47 GMT

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.

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

Anonymous — Mon, 01 Dec 2014 17:18:39 GMT

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!

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

manueld — Mon, 01 Dec 2014 17:32:09 GMT

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.