Re: Error while accesing excel file - Qlik Community

Anonymous · ‎2016-12-05

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

Anonymous · ‎2016-12-05

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

Anonymous · ‎2016-12-05

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

hami1 · ‎2016-12-05

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

Anonymous · ‎2016-12-06

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.

lennelei · ‎2021-12-23

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

Anonymous · ‎2021-12-23

Hello,

Here is a related KB article about:

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

Best regards

Sabrina

gjeremy1617088143 · ‎2021-12-23

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

Error while accesing excel file

Java

Talend Data Integration

v6.x

XML