java.lang.OutOfMemoryError and tFileInputExcel

Four Stars

java.lang.OutOfMemoryError and tFileInputExcel

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

Re: java.lang.OutOfMemoryError and tFileInputExcel

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
--
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: java.lang.OutOfMemoryError and tFileInputExcel

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
Moderator

Re: java.lang.OutOfMemoryError and tFileInputExcel

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
--
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: java.lang.OutOfMemoryError and tFileInputExcel

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

Re: java.lang.OutOfMemoryError and tFileInputExcel

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

Re: java.lang.OutOfMemoryError and tFileInputExcel

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

Re: java.lang.OutOfMemoryError and tFileInputExcel

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

Re: java.lang.OutOfMemoryError and tFileInputExcel

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
--
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: java.lang.OutOfMemoryError and tFileInputExcel

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

Re: java.lang.OutOfMemoryError and tFileInputExcel

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

Re: java.lang.OutOfMemoryError and tFileInputExcel

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

regards
laurent
Seventeen Stars

Re: java.lang.OutOfMemoryError and tFileInputExcel

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

Re: java.lang.OutOfMemoryError and tFileInputExcel

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

Re: java.lang.OutOfMemoryError and tFileInputExcel

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

Re: java.lang.OutOfMemoryError and tFileInputExcel

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

Re: java.lang.OutOfMemoryError and tFileInputExcel

You should close this thread as resolved.
Four Stars

Re: java.lang.OutOfMemoryError and tFileInputExcel

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

Re: java.lang.OutOfMemoryError and tFileInputExcel

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

Re: java.lang.OutOfMemoryError and tFileInputExcel

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.