OutOf memory when "append to existing file" option checked

[Anonymous]

Hi,

I created a Job that reads a. csv file line by line and reorganizes data using a tMap. Then I save data to a new Excel file (created by talend) using a tFileOutputExcel. The job works perfectly.
The problem appears when I want to add data to an existing excel file (even empty the problem is present). So in tFileOutputExcel, I check the options "Append to existing file" and "add to the existing sheet." The first 100,000 lines will be processed and gradually the flow will decrease and then stagnate and finally will cause me a memory error.
I have already tested the increase of memory with Xms arguments in the "Advanced Settings" and in the preferences of Talend ... It does not solve the problem because the number of lines is too important. And this solution is not suitable since this number is likely to increase further ...
I also try to divide the data into several temporary files that I loaded one after the other but it does not work eather ...
What I do not understand (and if you have an explanation I am all ears) is that when I uncheck both options and I save these 350,000 lines in a file created by Talend, it works very well and it is extremely fast!
Do you have a solution or an explanation? Thx

[Anonymous]

First use tJava,
java.io.FileOutputStream fos = new java.io.FileOutputStream(
"F:/Talend/5.3/Talend-Studio-r101418-V5.3.0NB/workspace/out.xlsx",
false);

globalMap.put("key", fos);
and then tick "Use Output Stream" the value is (java.io.FileOutputStream)globalMap.get("key") in tFileOutputExcel component.

[Anonymous]

this is job design?

[Anonymous]

Hi,
Thanks for the reply. It works fine but this solution overwrites the existing file and all tabs in the file are deleted.
I want to insert the data in one specific tab and after the data already present in this tab.

[Anonymous]

Hi

What I do not understand (and if you have an explanation I am all ears) is that when I uncheck both options and I save these 350,000 lines in a file created by Talend, it works very well and it is extremely fast!

Due to "Append" is written to cache, so if your inserted data is very large, the job will occur out Of memory exception.
Best regards
Sabrina

[Anonymous]

java.io.FileOutputStream fos = new java.io.FileOutputStream(
                                            "F:/Talend/5.3/Talend-Studio-r101418-V5.3.0NB/workspace/out.xlsx",
                                            true);
                                            
                                            globalMap.put("key", fos);

By using outputstream, I am unable to open the generated excel file. I am getting following error:

Excel found unreadable content in 'test.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

Please help me!

[Anonymous]

With your code snippet there no way to help you because you does not show us what content you write into the output stream. 
By the way, you cannot append this way to an excel file. The excel file contains a complex structure and it is not a plain file like text files which you can simply append, the excel file must be read completely and than you can extend its content.

[Anonymous]

Thank you for your reply!
What is the best way to add a sheet to the existing excel file. If I don't use output stream, it is throwing OutOfMemoryError.
Please help me!

[Anonymous]

Jepp, if the over all document grows to large, it will cause an OutOfMemoryError, you can in this case only increase the available memory for the job.
Another approach is using the components from talend Exchange tFileExcelWorkbookOpen, tFileExcelSheetOutput and tFileExcelWorkbookSave because the component tFileExcelWorkbookOpen has an memory saving mode for writing.
Search for Excel in https://exchange.talend.com/

[Anonymous]

Thank you!
tFileExcelWorkbookOpen component helped me to little extent. But I am getting same OutOfMemoryError if the editing excel file size is greater than 7MB.  tFileExcelWorkbookOpen component is throwing error!