[resolved] TlistFile--> Out of memory error (edit. & Access too slow)

[Anonymous]

This is what I have to do https://community.talend.com/t5/Design-and-Development/resolved-Excel-transformation/td-p/62988. In few words, transform 10 excel file and merge into one, and my job work... But only for the first 4 file, then when output file is reaching about 3 MB and the process use about 1GB of memory, the job crash.
-I tried to allocate more memory, setting -Xmx2048M in the advanced setting, but it says--> Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.
Error occurred during initialization of VM
Could not reserve enough space for object heap
-I Set a temporary folder on the Tmap component, but it dont write nothing on it(maybe i have to set another input with a Lookup?)...
-I tried also to use TfileList-> TFileExcelWorkBookOpen->TFileExcelSheetInput->Tmap->TfileOutputExcel, but if I use the "Save on disk option" it writes an empty file ...
Where I am wrong? Some suggestion?
Thanks!
 

[Anonymous]

Hi,
Could you please have a look at KB articles about TalendHelpCenter:Exception Could not reserve enough space for object heapand TalendHelpCenter:ExceptionoutOfMemory?
Best regards
Sabrina

[Anonymous]

Hi thanks for the answer, I've checked the articles and tried to allocate more memory/ decrease the xmx parameter on the ini.file , export the Job as script and run it outside the studio,  also change the JAVA_HOME/bin to the first position, but nothing ... The Job work  only with the first 4 file...
I think the only way is the " save on disk option", but I don't know how to use it in my case, because there's only one table in input and many "joined output"... Maybe I should use the Workbook  component, but don't know how to dispose and configure it.
I would be very thankfull if someone could help me! 😃 

[Anonymous]

Hi,
Have you stored data on disk in tMap component? How did you set your JVM setting for your current job?
Best regards
Sabrina

[Anonymous]

Yes for the first question.
-vmargs
-Xms256m
-Xmx1024m
-XX:MaxPermSize=128m
-Dfile.encoding=UTF-8
By the way I have partly resolved the problem using a TAccessOutput instead of Excel , but the problem now is that the process is too slow... Only 2 row/s  versus the 2000 row/s of ExcelOutput...
There's a way to resolve that? Thanks!!
Marco

[Anonymous · Accepted solution]

The current build-in excel components lacks on a streaming support.
You could use the components from Talend Exchange: http://www.talendforge.org/exchange
(search for excel)
tFileExcelWorkbookOpen (to open or create the file, her set the memory saving mode)
tFileExcelSheetOutput (to create a sheet and write the rows in a very enhanced way)

[Anonymous]

Thanks Mr.Lolling
So this is my Job, but I think the WorkbookSave is in the wrong place, dont know where I have to put it.
In this way, every iteration overwrite the ouput file even if I use " Append to existing rows"...  and if I use the " memory saving" option it doesn't write nothing(0 rows/s)

I am also yet interested to know why the AccessOutput is so slow 
Thanks again
Marco

[Anonymous]

Your design is the problem.
Connect the tFileExcelWorkbook with the tFileExcelSheetInput per OnSubjobOk.
Connect the tFileExcelSheetInput with OnSubjobOk with the tFileExcelWorkbookSave.
Actually I would do a complete different design.
I would create a dedicated job to process on file and I would call this "worker" job with the iteration from the tFileList. 

[Anonymous]

I can't connect  tFileExcelWorkbook and  tFileExcelSheetInput with SubjobOk an error says that " A component that is not a sub process start can not have any link on sub job ok".
The possible combination are:
1. Tfilelist -- iterate-->tFileExcelWorkbook-- OnComponentOk-->tFileExcelSheetInput-- OnSubjobOk-->tFileExcelWorkbookSave
1.Loop--> Tfilelist -- OncomponentOK-->tFileExcelWorkbook-- OnSubjobOk-->tFileExcelSheetInput-- OnSubjobOk-->tFileExcelWorkbookSave

Both case, same problems: The Output is overwritten for every iteration, instead of adding the new rows under the existing rows, and if i use "save on disk" the output is empty...
Thanks for the patience.
Other information:
- To call every file from tfilelist I used "((String)globalMap.get("tFileList_1_CURRENT_FILEPATH"))" as Filename in tFileExcelWorkbook

[Anonymous]

Ok you wantto add tge content of the files in the existing sheet.
you have to ooen the tFileExcleWorkbookOpen first and than iterate through the files. I mean if you open for every input  file ooen the same excel file new it can obviously not work. And also the save component... I would connect trigger it with Onsubjobok.