OutOf memory when "append to existing file" option checked

One Star

OutOf memory when "append to existing file" option checked

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
Highlighted
Employee

Re: OutOf memory when "append to existing file" option checked

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.
Employee

Re: OutOf memory when "append to existing file" option checked

this is job design?
One Star

Re: OutOf memory when "append to existing file" option checked

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.
Moderator

Re: OutOf memory when "append to existing file" option checked

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: OutOf memory when "append to existing file" option checked

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!
Seventeen Stars

Re: OutOf memory when "append to existing file" option checked

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.
Four Stars

Re: OutOf memory when "append to existing file" option checked

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!
Seventeen Stars

Re: OutOf memory when "append to existing file" option checked

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/
Four Stars

Re: OutOf memory when "append to existing file" option checked

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!
Seventeen Stars

Re: OutOf memory when "append to existing file" option checked

The memory saving mode works only if you create a file from scratch. If you read in, this mode does not work. I am working on a way to build files with sheets and combine them later together. Building very large excel files needs memory.
What error do you got? Please post the stack trace.