3 Replies Latest reply on Aug 12, 2014 11:11 AM by rareddy

    Not able to get excel document into relational table.

    onkar.dhuri
    onkar.dhuri Aug 6, 2014 12:12 PM

      Hi,

       

      I want to export an excel document as relational tables through vdb. I referred the corresponding article - https://community.jboss.org/wiki/MicrosoftExcelDocumentIntoRelationalTable?_sscc=t

      and I followed the same steps as mentioned there. However I am not able to get the vdb active. I am getting NullPointerException as below -

       

      16:53:05,938 WARN  [org.teiid.RUNTIME] (teiid-async-threads - 1)  TEIID50036 VDB exceldemo.1 model "excel" metadata failed to load. Reason:java.lang.NullPointerException: java.lang.NullPointerException

              at org.teiid.translator.excel.ExcelMetadataProcessor.addTable(ExcelMetadataProcessor.java:106)

              at org.teiid.translator.excel.ExcelMetadataProcessor.process(ExcelMetadataProcessor.java:87)

              at org.teiid.translator.excel.ExcelMetadataProcessor.process(ExcelMetadataProcessor.java:45)

              at org.teiid.translator.ExecutionFactory.getMetadata(ExecutionFactory.java:915) [teiid-api-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(NativeMetadataRepository.java:73) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.jboss.VDBService$6.run(VDBService.java:395) [teiid-jboss-integration-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.jboss.VDBService$7.run(VDBService.java:442) [teiid-jboss-integration-8.7.0.Final.jar:8.7.0.Final]

              at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895) [rt.jar:1.6.0_45]

              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918) [rt.jar:1.6.0_45]

              at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_45]

              at org.jboss.threads.JBossThread.run(JBossThread.java:122)


      I even tried to use the static vdb as provided in the article. The vdb gets active but when I query it through squirrel, I get similar exception in different class -

       

      16:27:50,232 ERROR [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue20) ghb0TEpBOLQj Connector worker process failed for atomic-request=ghb0TEpBOLQj.10.0.0: java.lang.NullPointerException

              at org.teiid.translator.excel.ExcelExecution.execute(ExcelExecution.java:105)

              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:325) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) [rt.jar:1.6.0_45]

              at java.util.concurrent.FutureTask.run(FutureTask.java:138) [rt.jar:1.6.0_45]

              at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895) [rt.jar:1.6.0_45]

              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918) [rt.jar:1.6.0_45]

              at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_45]

       

      16:27:50,261 ERROR [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue21) ghb0TEpBOLQj TEIID30019 Unexpected exception for request ghb0TEpBOLQj.10: java.lang.NullPointerException

              at org.teiid.translator.excel.ExcelExecution.execute(ExcelExecution.java:105)

              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:325) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) [rt.jar:1.6.0_45]

              at java.util.concurrent.FutureTask.run(FutureTask.java:138) [rt.jar:1.6.0_45]

              at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895) [rt.jar:1.6.0_45]

              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918) [rt.jar:1.6.0_45]

              at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_45]

       

      16:27:50,288 ERROR [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue21) ghb0TEpBOLQj null: java.lang.NullPointerException

              at org.teiid.translator.excel.ExcelExecution.close(ExcelExecution.java:339)

              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.close(ConnectorWorkItem.java:220) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.DataTierTupleSource.fullyCloseSource(DataTierTupleSource.java:323) [teiid-engine-8.7.0.Final.jar:8.7.0.Final

      ]

              at org.teiid.dqp.internal.process.DataTierTupleSource.closeSource(DataTierTupleSource.java:353) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.query.processor.relational.AccessNode.closeSources(AccessNode.java:506) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.query.processor.relational.AccessNode.closeDirect(AccessNode.java:501) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.query.processor.relational.RelationalNode.close(RelationalNode.java:332) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.query.processor.relational.RelationalPlan.close(RelationalPlan.java:150) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.query.processor.QueryProcessor.closeProcessing(QueryProcessor.java:252) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.RequestWorkItem.close(RequestWorkItem.java:488) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:352) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:254) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

              at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895) [rt.jar:1.6.0_45]

              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918) [rt.jar:1.6.0_45]

              at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_45]


      Can you please let me know is there anything that I have missed ?

       

      I am using Teiid 8.7 Final with Jboss 6.1.0Alpha. Please find the attached xls, vdb and resource-adapter code snippet which I used.

       

      Thanks,

      Onkar

        • 1. Re: Not able to get excel document into relational table.
          rareddy
          rareddy Aug 7, 2014 12:01 PM (in response to onkar.dhuri)

          Onkar,

           

          I fixed this bug in Teiid 8.8, so try that. See https://issues.jboss.org/browse/TEIID-3026 for more details

           

          Ramesh..

            • Actions
          • 2. Re: Re: Not able to get excel document into relational table.
            onkar.dhuri
            onkar.dhuri Aug 12, 2014 7:53 AM (in response to rareddy)

            Hi Ramesh,

             

            Thanks for pointing out to Teiid 8.8. In our application we are using Teiid 8.7 and we do not have any plans to upgrade to 8.8 at this moment.

            So, I downloaded Teiid 8.8 and just replaced excel translator module with 8.8 version and I was able to get the example running. It is being exposed correctly through our odata endpoint however when I issue queries against it using Squirrel, I found that the header row is also being considered as part of data.

            For e.g. When I execute, "SELECT * FROM excel.Sheet1" I get following exception -

            Error: TEIID10078 Invalid double format in String: Age

            SQLState:  50000

            ErrorCode: 10078f


            Similarly when I execute following query, the result I received did not match with expected -

             

            Moreover, the same behavior has been observed with EAP6.1 + Teiid 8.8 instance.

            Can you please let me know if these are known issues and already/being addressed ? When can we expect the patch/fix for them ?

             

            Also, does excel translator support Create/Update operations ? Because when I tried it threw exception - "Metadata does not allow updates on the group: excel.Sheet1"

             

            Thanks,

            Onkar

              • Actions
            • 3. Re: Not able to get excel document into relational table.
              rareddy
              rareddy Aug 12, 2014 11:11 AM (in response to onkar.dhuri)

              Onkar,

               

              See Microsoft Excel Translator  for documentation, especially look for property "FIRST_DATA_ROW_NUMBER" that tells Teiid where the data row starts. We do not have any plans for supporting create/update/delete operations on this translator. If you want you can submit a code patch and we will consider it. Otherwise it will be a low priority item.


              Ramesh..

                • Actions