tFilInputExcel for large data file not working

One Star

tFilInputExcel for large data file not working

Hello Talend Team,
I have an excel sheet with 155 columns and 9k rows(not fixed) and I want the output csv file with first 3 column ,last 3 column and some extra added column.
I have design my job like
tFilInputExcel ----------------------->tMap----------------------------------------------> tofileutputdelimeter
(defined 155 schema) (map and added some extra column)
but it was giving me error
Code:
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
at x.y.z.tFileInputPositional_1Process(z.java:7532)
So I have increased JVM memory up-to maximum of my system memory Xms1024M,Xmx1536M after then it was working fine.
but when my data volume increased from 9k rows to 15k rows I am getting the same issue of outofmemory again.
please tell me how can I solve this issue. I can not increase the JVM memory now because as I have set it maximum for system.
can you please also suggest me the another job design.
Thank you in advance,
Nitin
One Star

Re: tFilInputExcel for large data file not working

Hi Nitin
It is because the input datas are over the memory limit of JVM.
You can use 'Store on disk' feature of tMap which can reduce the cost of memory.
Regards,
Pedro
One Star

Re: tFilInputExcel for large data file not working

Hi Pedro,
Thank you for reply.
I have already use the "Store on disk" feature of tMap but still I am getting the same error.
Thank you,
Nitin Munjani
One Star

Re: tFilInputExcel for large data file not working

Hi Nitin
In fact, because of the memory limit of your machine, there isn't a solution now.
But we can try some workarounds.
You will see two textfields on tFileInputExcel. One is "Header". The other is "Limit".
Extract 5000 rows from the Excel file each time.
Header: 0 Limit: 5000
Header: 5001 Limit: 5000
Header:10001 Limit: 5000
Regards,
Pedro
One Star

Re: tFilInputExcel for large data file not working

If you go into ADVANCE settings the OUTPUT component...the one receiving the data, you can define a batch size. If you define a low batch and commit size, like 1K, then it doesn't hold as much in memory and can get thru the wide data in small chunks.
One Star

Re: tFilInputExcel for large data file not working

You can simply change the settings like below
tFileInputExcel --> Advanced Settings : set Generation mode as Event Mode
Thanks,
Naageshwar