topic Error while accesing excel file in Talend Studio

Error while accesing excel file

Anonymous — Mon, 05 Dec 2016 09:10:33 GMT

Hi,
I am getting below error while reading some excel files sized 15-50 MBs using tFileInputExcel components

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
	at org.apache.xmlbeans.impl.store.Cur.createElementXobj(Cur.java:260)
	at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.startElement(Cur.java:2997)
	at org.apache.xmlbeans.impl.store.Locale$SaxHandler.startElement(Locale.java:3211)
	at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.reportStartTag(Piccolo.java:1082)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseOpenTagNS(PiccoloLexer.java:1473)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseTagNS(PiccoloLexer.java:1362)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXMLNS(PiccoloLexer.java:1293)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXML(PiccoloLexer.java:1261)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.yylex(PiccoloLexer.java:4812)
	at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yylex(Piccolo.java:1290)
	at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yyparse(Piccolo.java:1400)
	at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.parse(Piccolo.java:714)
 disconnected
	at org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3479)
	at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1277)
	at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1264)
	at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
	at org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument$Factory.parse(Unknown Source)
	at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:194)
	at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:186)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:354)
	at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:166)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:240)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:296)
	at ifp.test_euro_alcohol_0_1.test_Euro_Alcohol.tFileInputExcel_1Process(test_Euro_Alcohol.java:746)
	at ifp.test_euro_alcohol_0_1.test_Euro_Alcohol.runJobInTOS(test_Euro_Alcohol.java:1710)
	at ifp.test_euro_alcohol_0_1.test_Euro_Alcohol.main(test_Euro_Alcohol.java:1567)
Job test_Euro_Alcohol ended at 14:31 05/12/2016.

Initially I thought size is issue so I tried "tFileExcelWorkbookOpen" and "tExcelfileSheetInput" free components but in that component also I am getting below error:

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
	at java.util.Arrays.copyOfRange(Unknown Source)
	at java.lang.String.<init>(Unknown Source)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseCdataLiteral(PiccoloLexer.java:3027)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseQuotedTagValue(PiccoloLexer.java:2936)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseAttributesNS(PiccoloLexer.java:1754)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseOpenTagNS(PiccoloLexer.java:1521)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseTagNS(PiccoloLexer.java:1362)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXMLNS(PiccoloLexer.java:1293)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXML(PiccoloLexer.java:1261)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.yylex(PiccoloLexer.java:4812)
	at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yylex(Piccolo.java:1290)
	at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yyparse(Piccolo.java:1400)
 disconnected
	at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.parse(Piccolo.java:714)
	at org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3479)
	at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1277)
	at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1264)
	at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
	at org.apache.poi.POIXMLTypeLoader.parse(POIXMLTypeLoader.java:92)
	at org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument$Factory.parse(Unknown Source)
	at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:173)
	at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:165)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.parseSheet(XSSFWorkbook.java:417)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:382)
	at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:178)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:279)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook2.<init>(XSSFWorkbook2.java:31)
	at de.cimt.talendcomp.tfileexcelpoi.SpreadsheetFile.initializeWorkbook(SpreadsheetFile.java:323)
	at ifp.test_euro_alcohol_0_1.test_Euro_Alcohol.tFileExcelWorkbookOpen_1Process(test_Euro_Alcohol.java:351)
	at ifp.test_euro_alcohol_0_1.test_Euro_Alcohol.runJobInTOS(test_Euro_Alcohol.java:1609)
	at ifp.test_euro_alcohol_0_1.test_Euro_Alcohol.main(test_Euro_Alcohol.java:1466)

Can any one please help me on this

Re: Error while accesing excel file

Anonymous — Mon, 05 Dec 2016 10:40:02 GMT

Hi,
What's OS and JDK version you are using? 64 bit OS? Does this issue " Java heap space" repro on your specific job or all jobs?
Have you already checked KB article about: TalendHelpCenter:Exception Could not reserve enough space for object heap to see if this solution is Ok with you?
Best regards
Sabrina

Re: Error while accesing excel file

Anonymous — Mon, 05 Dec 2016 12:12:03 GMT

Hi Xdshi,
I am using 64 bit Win 7 OS and JDK is 1.7. My other jobs are working fine which are fetching data from small size excel files but while developing job for excel file with big size I faced this issue
I was using "TOS_DI-win32-x86.exe" I also tryied using "TOS_DI-win-x86_64.exe" but I got below error 64bit exe

Exception in thread "Thread-0" Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded
 disconnected
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseCdataLiteral(PiccoloLexer.java:3027)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseQuotedTagValue(PiccoloLexer.java:2936)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseAttributesNS(PiccoloLexer.java:1754)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseOpenTagNS(PiccoloLexer.java:1521)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseTagNS(PiccoloLexer.java:1362)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXMLNS(PiccoloLexer.java:1293)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXML(PiccoloLexer.java:1261)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.yylex(PiccoloLexer.java:4812)
	at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yylex(Piccolo.java:1290)
	at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yyparse(Piccolo.java:1400)
	at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.parse(Piccolo.java:714)
	at org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3479)
	at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1277)
	at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1264)
	at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
	at org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument$Factory.parse(Unknown Source)
	at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:194)
	at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:186)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:354)
	at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:166)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:240)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:296)
	at ifp.test_euro_alcohol_0_1.test_Euro_Alcohol.tFileInputExcel_1Process(test_Euro_Alcohol.java:746)
	at ifp.test_euro_alcohol_0_1.test_Euro_Alcohol.runJobInTOS(test_Euro_Alcohol.java:1710)
	at ifp.test_euro_alcohol_0_1.test_Euro_Alcohol.main(test_Euro_Alcohol.java:1567)
java.lang.OutOfMemoryError: GC overhead limit exceeded
	at java.util.LinkedList.listIterator(Unknown Source)
	at java.util.AbstractList.listIterator(Unknown Source)
	at java.util.AbstractSequentialList.iterator(Unknown Source)
	at routines.system.RunStat.sendMessages(RunStat.java:261)
	at routines.system.RunStat.run(RunStat.java:225)
	at java.lang.Thread.run(Unknown Source)

and as per sugested KB article below is the detail from 64bit ini file
-Xmx1536m

Re: Error while accesing excel file

hami1 — Mon, 05 Dec 2016 13:48:37 GMT

Hi,
Try to Increase the Memory and re-run the Job.

Re: Error while accesing excel file

Anonymous — Tue, 06 Dec 2016 12:31:26 GMT

Hi skhameed,
I incresed size first to -Xmx11536m but same below error again I incresed it to -Xmx111536m but same error I am getting
Below is the TOS_DI-win-x86_64.ini file content after last change
-vmargs
-Xms512m
-Xmx111536m
-XX:MaxPermSize=512m
-Dfile.encoding=UTF-8
and below is the error

Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded
	at org.apache.xmlbeans.impl.values.NamespaceContext.getNamespaceContextStack(NamespaceContext.java:98)
	at org.apache.xmlbeans.impl.values.NamespaceContext.push(NamespaceContext.java:106)
	at org.apache.xmlbeans.impl.values.XmlObjectBase.check_dated(XmlObjectBase.java:1318)
	at org.apache.xmlbeans.impl.values.XmlObjectBase.getStringValue(XmlObjectBase.java:1529)
	at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTCellImpl.getR(Unknown Source)
	at org.apache.poi.xssf.usermodel.XSSFCell.<init>(XSSFCell.java:105)
	at org.apache.poi.xssf.usermodel.XSSFRow.<init>(XSSFRow.java:69)
	at org.apache.poi.xssf.usermodel.XSSFSheet.initRows(XSSFSheet.java:236)
	at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:199)
	at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:186)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:354)
	at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:166)
 disconnected
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:240)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:296)
	at ifp.test_euro_alcohol_0_1.test_Euro_Alcohol.tFileInputExcel_1Process(test_Euro_Alcohol.java:746)
	at ifp.test_euro_alcohol_0_1.test_Euro_Alcohol.runJobInTOS(test_Euro_Alcohol.java:1710)
	at ifp.test_euro_alcohol_0_1.test_Euro_Alcohol.main(test_Euro_Alcohol.java:1567)
Job test_Euro_Alcohol ended at 17:58 06/12/2016.

Re: Error while accesing excel file

lennelei — Thu, 23 Dec 2021 08:41:50 GMT

Hi (it's an old post but Google brought me here),

By modifying TOS_DI-win-x86_64.ini, you're only upgrading Xmx for Talend Studio which won't help your job to run.

You have to change the Xmx settings in the job run panel under the advanced settings :

Regards

Re: Error while accesing excel file

Anonymous — Thu, 23 Dec 2021 09:49:59 GMT

Hello,

Here is a related KB article about:

https://community.talend.com/s/article/OutOfMemory-Exception-WmtmQ

Best regards

Sabrina

Re: Error while accesing excel file

gjeremy1617088143 — Thu, 23 Dec 2021 10:25:37 GMT

Hi , if excel file is xlsx you can change the Generation mode in advanced settings to Less memory consumed for large excel(Event mode).

Send me love and kudos