Error while accesing excel file

Four Stars

Error while accesing excel file

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 
Moderator

Re: Error while accesing excel file

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Highlighted
Four Stars

Re: Error while accesing excel file

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 
Seven Stars skh
Seven Stars

Re: Error while accesing excel file

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

Re: Error while accesing excel file

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.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog