topic Re: java.lang.OutOfMemoryError and tFileInputExcel in Talend Studio

java.lang.OutOfMemoryError and tFileInputExcel

Rosanero4Ever — Wed, 12 Dec 2012 22:51:44 GMT

Hi all,
I'm using a tFileInputExcel in order to import an Excel file (about 25k rows and 10 columns) in my DB.
I'm using TOS 5.1.2 on Windows 2008 R2 in a computer provided with 8GB RAM
My memory configuration is the following:
-vmargs
-Xms64m
-Xmx1536m
-XX:MaxPermSize=512m
-Dfile.encoding=UTF-8
Despite of this I have the following exception:
Exception in thread "main" java.lang.Error: java.lang.Error: java.lang.Error: java.lang.Error: java.lang.Error: java.lang.Error: java.lang.OutOfMemoryError: Java heap space at
I use store data functionality of tMap and commit my data every 1000 rows but I yet have this problem.
I read some posts about this issue and I applied all the advices.
I also edited the memory configuration with Xmx=3096m but...nothing 😞
I wouldn't convert my excel in csv.
So, can anybody help me to find a solution, please?

Re: java.lang.OutOfMemoryError and tFileInputExcel

Anonymous — Thu, 13 Dec 2012 06:31:48 GMT

Hi,
There are two points which is related to the java.lang.OutOfMemory Error
1. The memory
2. The job you have designed
I have seen that you make a lot of efforts on that, If the job exists tsortrow, tmap, tuniqRo w, consume large amounts of memory, and other components, the data is saved to disk. Would you mind sending your job screenshot to us so that we can see whether there is optimized.
Best regards
Sabrina

Re: java.lang.OutOfMemoryError and tFileInputExcel

Rosanero4Ever — Thu, 13 Dec 2012 08:36:37 GMT

Hi Sabrina,
as you can see, my job is very simple. tFileList component considers only a file in this moment but I would like consider, in the future, more than one file.
I attached 3 pictures. I hope these are useful to my scope.
Thanks very much for your time.
R

Re: java.lang.OutOfMemoryError and tFileInputExcel

Anonymous — Thu, 13 Dec 2012 09:22:30 GMT

Hi,

My memory configuration is the following:
-vmargs
-Xms64m
-Xmx1536m
-XX:MaxPermSize=512m
-Dfile.encoding=UTF-8

The memory configuration is the whole Talend job not your current job, isn't it?
You should set your current Job in Talend Studio
In the Run viewer, open the Advanced Settings tab and check Use specific JVM arguments.
Allocate more memory by modifying the JVM parameter as pic shown
Try it and tell us your result please
Best regards
Sabrina

Re: java.lang.OutOfMemoryError and tFileInputExcel

Rosanero4Ever — Thu, 13 Dec 2012 09:46:17 GMT

Hi,
before all I must correct my previous post: the excel file isn't made up by 25k rows, but 400k rows!
Then, I setup my job with 4096M as you can see in the attached picture.
Despite of this i yet have the out of memory error (after about 270k rows)
Have you other solution?

Re: java.lang.OutOfMemoryError and tFileInputExcel

Anonymous — Thu, 13 Dec 2012 21:06:33 GMT

hi all,
how about the tMap ?
do you make "a lot of thing" in it ?
try to just read data and write it as it in your table, to check if tMap is the bottleneck .. or not.
hope it help
regards
laurent

Re: java.lang.OutOfMemoryError and tFileInputExcel

Rosanero4Ever — Thu, 13 Dec 2012 21:09:20 GMT

Hi,
tMap only map data into the DB. No transformations are done.
I'll try tour advice even if, as I wrote before, tMap is very simple

Re: java.lang.OutOfMemoryError and tFileInputExcel

Rosanero4Ever — Thu, 13 Dec 2012 22:43:22 GMT

I removed the tMap component and I setup the job as show in the attached picture, but the problem still exists.
Should I give up on the tFileInputExcel component?

Re: java.lang.OutOfMemoryError and tFileInputExcel

Anonymous — Fri, 14 Dec 2012 03:28:58 GMT

Hi,
I didn't find any issue in your job, how about restarting your studio, renewing a job and copying the job into it? I have no better idea, sorry for that.
Best regards
Sabrina

Re: java.lang.OutOfMemoryError and tFileInputExcel

Rosanero4Ever — Fri, 14 Dec 2012 08:22:46 GMT

Hi Sabrina,
I perfomed all tasks you adviced (restart TOS, create a new job, etc.) but the issue yet exists.
So, I believe tFileInputExcel can be used only for small excel file (it works with an excel file made up of 10k rows).
or an excel file with a version <2007 (with 65535 rows max).
I resign myself to the use of a csv file
Thanks all for helping me.

Re: java.lang.OutOfMemoryError and tFileInputExcel

Anonymous — Sat, 15 Dec 2012 22:07:08 GMT

The built-in Excel components from Talend use a very - let me say - poor library for the xls format. It would be much better if they moved to the Apache POI library as they do already for the xlsx format.
You can try the combination of tFileExcelWorkbookOpen and tFileExcelSheetInput (both working together and use the latest Apache POI lib V3.8 and available over Talend Exchange) to read out your sheet. This has also the advantage, you can exactly specify which column you want to read.
If have read such huge files with the help of this components.

Re: java.lang.OutOfMemoryError and tFileInputExcel

Anonymous — Sat, 15 Dec 2012 22:42:22 GMT

hi all,
I think that Talend 5.1.2 use POI lib to read Excel file .

regards
laurent

Re: java.lang.OutOfMemoryError and tFileInputExcel

Anonymous — Sat, 15 Dec 2012 22:58:39 GMT

Yes but it looks like only for the new xlsx format (the only good news is that we have v3.8 now). For xls they use the classes from the jxl lib.

Re: java.lang.OutOfMemoryError and tFileInputExcel

Rosanero4Ever — Sun, 16 Dec 2012 09:31:27 GMT

Hi all,
my Excel file has xlsx extension, so new library shoud be used. Isn't it?

Re: java.lang.OutOfMemoryError and tFileInputExcel

Anonymous — Sun, 16 Dec 2012 13:34:31 GMT

Yes, it is. It would be interesting to know how large your file was. It have tested all components with a very large file of nearly 15MB file size with 20 sheets and in sum more than 65535 rows.

Re: java.lang.OutOfMemoryError and tFileInputExcel

Rosanero4Ever — Sun, 16 Dec 2012 13:46:57 GMT

my file is about 400k rows in a single sheet (100MB)
Thanks a lot

Re: java.lang.OutOfMemoryError and tFileInputExcel

Anonymous — Sun, 16 Dec 2012 14:30:41 GMT

You should close this thread as resolved.

Re: java.lang.OutOfMemoryError and tFileInputExcel

Rosanero4Ever — Sun, 16 Dec 2012 14:52:39 GMT

Sorry..
I can close this thread as resolved,
but I don't understand how the issue I wrote was resolved
...It was resolved?

Re: java.lang.OutOfMemoryError and tFileInputExcel

Anonymous — Mon, 17 Dec 2012 09:06:18 GMT

No it isn't. But to be onest an Excel file with 100MB is a bit .... I think there will be hardly any solution for it.
Your solution to use csv format instead of the excel format is the best way from my point of view.

Re: java.lang.OutOfMemoryError and tFileInputExcel

Rosanero4Ever — Mon, 17 Dec 2012 09:09:34 GMT

You're right 🙂
So, in your opinion, what order of size would I consider to use this component without using csv?
Thank a lot for your replies.