14 Replies Latest reply: Aug 6, 2013 9:16 AM by VincenzoGrimaldi RSS

    Excel problem in ODI

    805690
      Hi All,
      I am trying to get data from excel sheet through an open source driver *"XlSQL-Y8"* in ODI 10g .I dont want to use SUN jdbc-odbc driver .But I am getting the following error.

      *[error]*

      See com.borland.dx.dataset.DataSetException error code: BASE+66

      com.borland.dx.dataset.DataSetException: Table not found: EmpDetails1 in statement [select * from "EmpDetails"."EmpDetails1"]

           at com.borland.dx.dataset.DataSetException.a(Unknown Source)

           at com.borland.dx.dataset.DataSetException.throwException(Unknown Source)

           at com.borland.dx.dataset.DataSetException.SQLException(Unknown Source)

           at com.borland.dx.sql.dataset.Database.createPreparedStatement(Unknown Source)

           at com.borland.dx.sql.dataset.o.a(Unknown Source)

           at com.borland.dx.sql.dataset.o.d(Unknown Source)

           at com.borland.dx.sql.dataset.o.f(Unknown Source)

           at com.borland.dx.sql.dataset.QueryProvider.e(Unknown Source)

           at com.borland.dx.sql.dataset.JdbcProvider.provideData(Unknown Source)

           at com.borland.dx.dataset.StorageDataSet.refresh(Unknown Source)

           at com.borland.dx.sql.dataset.QueryDataSet.refresh(Unknown Source)

           at com.sunopsis.graphical.frame.b.ja.dj(ja.java)

           at com.sunopsis.graphical.frame.b.ja.<init>(ja.java)

           at com.sunopsis.graphical.frame.b.jc.<init>(jc.java)

           at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

           at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

           at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

           at java.lang.reflect.Constructor.newInstance(Unknown Source)

           at com.sunopsis.graphical.frame.bb.b(bb.java)

           at com.sunopsis.graphical.tools.utils.swingworker.v.call(v.java)

           at edu.emory.mathcs.backport.java.util.concurrent.FutureTask.run(FutureTask.java:176)

           at com.sunopsis.graphical.tools.utils.swingworker.l.run(l.java)

           at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:665)

           at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:690)

           at java.lang.Thread.run(Unknown Source)

      Chained exception:

      java.sql.SQLException: Table not found: EmpDetails1 in statement [select * from "EmpDetails"."EmpDetails1"]

           at org.hsqldb.jdbc.jdbcUtil.throwError(jdbcUtil.java:62)

           at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(jdbcPreparedStatement.java:1804)

           at org.hsqldb.jdbc.jdbcConnection.prepareStatement(jdbcConnection.java:547)

           at com.nilostep.xlsql.jdbc.xlConnection.prepareStatement(xlConnection.java:396)

           at com.borland.dx.sql.dataset.Database.createPreparedStatement(Unknown Source)

           at com.borland.dx.sql.dataset.o.a(Unknown Source)

           at com.borland.dx.sql.dataset.o.d(Unknown Source)

           at com.borland.dx.sql.dataset.o.f(Unknown Source)

           at com.borland.dx.sql.dataset.QueryProvider.e(Unknown Source)

           at com.borland.dx.sql.dataset.JdbcProvider.provideData(Unknown Source)

           at com.borland.dx.dataset.StorageDataSet.refresh(Unknown Source)

           at com.borland.dx.sql.dataset.QueryDataSet.refresh(Unknown Source)

           at com.sunopsis.graphical.frame.b.ja.dj(ja.java)

           at com.sunopsis.graphical.frame.b.ja.<init>(ja.java)

           at com.sunopsis.graphical.frame.b.jc.<init>(jc.java)

           at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

           at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

           at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

           at java.lang.reflect.Constructor.newInstance(Unknown Source)

           at com.sunopsis.graphical.frame.bb.b(bb.java)

           at com.sunopsis.graphical.tools.utils.swingworker.v.call(v.java)

           at edu.emory.mathcs.backport.java.util.concurrent.FutureTask.run(FutureTask.java:176)

           at com.sunopsis.graphical.tools.utils.swingworker.l.run(l.java)

           at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:665)

           at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:690)

           at java.lang.Thread.run(Unknown Source)


      *[\error]*
      can anyone help me out.Thanks in advance.
      Regards,
      Gnanaprakasam.

      Edited by: user13028132 on Oct 19, 2010 3:46 AM
        • 1. Re: Excel problem in ODI
          774570
          what is the open source driver you are using .?
          How are you making the connection in Topology ?
          where is the Excel located and from where are you trying reverse or read. ?

          Looks like the driver is not able to read the file.
          • 2. Re: Excel problem in ODI
            ODIDEV
            Hi man, I was taking a look on this kind of issue and trying to execute here in my job enviroment and I was not able to read the excel file but on the other hand the solution that a found to solve this issue was the following.....I just copied all the data from the excel pasted it on a textpad and checked out all the positions then a I saved as a txt file in a diretory on my c drive, you then should create the file data server in the topology module, also I created the stg-tables that will receive this first load of data from excel, and just right after I aplied the oracle substr internal function in order to read the positions , it worked just fine....try out this procedure, even though it looks like a lot of work you can solve this issue easily.

            Any doubt , let me know....

            Best regards

            Edited by: user11154396 on 21/10/2010 12:54
            • 3. Re: Excel problem in ODI
              ODIDEV
              Just to remind you that the substr function should be applied on each attribute on the target datastore on your interface in order to catch the right position .
              • 4. Re: Excel problem in ODI
                ODIDEV
                Well man , I was reading an article about this issue and I found someh=thing really interesting, take a look , you will have to create an odbc connection for the excel file.
                Hope this weblink helps you:

                http://blogs.oracle.com/dataintegration/2010/03/connecting_to_microsoft_excel.html

                best regards!!
                • 5. Re: Excel problem in ODI
                  805690
                  Hi Dev,
                  It is there in sourceforge.net in this following link http://sourceforge.net/projects/xlsql/* .The jar files are copied into drivers folder and restart the ODI.In reository give the following details
                  com.nilostep.xlsql.jdbc.xlDriver for driver
                  jdbc:nilostep:excel:D:\ODI (location of excel file) for details
                  -> test connection.Test OK.
                  It is showing the files in model and eversing is alo done .But couldnt retrieve the values.Sorry for the late reply.
                  Regards,
                  Gnanaprakasam.
                  • 6. Re: Excel problem in ODI
                    805690
                    Hi Yves,
                    Ya i have read that http://blogs.oracle.com/dataintegration/2010/03/connecting_to_microsoft_excel.html before .the problem is that i have to use in linux.In linux it is difficult to use DSN jdbc-odbc.So i have tried the driver in source forge.Thanks man.Thanks for ypur reply.
                    Regards,
                    Gnanaprakasam R
                    • 7. Re: Excel problem in ODI
                      ODIDEV
                      You´re Welcome.
                      • 8. Re: Excel problem in ODI
                        DecaXD
                        Hi Gnanam, sorry to reopen this thread but i've the same issue,
                        how did you solve it?

                        Thanks,
                        Francesco
                        • 9. Re: Excel problem in ODI
                          805690
                          Hi Francesco,
                          Please try the following
                          Step 1:
                          The Following list of Jar files found in XlSQL-Y8 should be copied to <User home>/.odi/oracledi/userlib and <ODI_HOME>/oracledi/agent/drivers
                          xlSQL_Y8.jar
                          commons-cli-1.0.jar
                          commons-logging.jar
                          hsqldb.jar
                          crimson.jar
                          jaxp.jar
                          jconfig.jar
                          jmxri.jar
                          jxl.jar
                          Restart the ODI client if they are already running.

                          Step 2:
                          Create a new Data source in "Microsoft Excel" technology use the following driver and jdbc url.
                          Driver :com.nilostep.xlsql.jdbc.xlDriver
                          JDBC : jdbc:nilostep:excel:<Dir where the excel files are place>
                          Add the a new Physical schema and map it to a Logical schema.

                          Step 3:
                          Create a new Model for the excel tables, use the same logical connect ion which you used in previous step. Now you can reverse engineer the Tables.
                          The reverse engineered table would get the name like <File Name>.<Sheet Name> and First row in the sheet would be treated as the column names.
                          Now these reverse engineered tables can be used for mappings.

                          Try it and if there are any issues let me know.
                          Sorry for the late reply.
                          Regards,
                          Gnanaprakasam
                          • 10. Re: Excel problem in ODI
                            M
                            Hi,

                            I've created the Physical and Logical Schemas and tested the Connection.......everything seems Ok.
                            But after creating the Model,I wasnt able to get the files . I mean imnt able to do the reverse Engineering......

                            Please help


                            Thanks,
                            Moses.V
                            • 11. Re: Excel problem in ODI
                              Iwan Toma
                              I have used XlSQL-Y8.
                              But when i reverse it, i got this error.
                              "Unsupported major.minor version 49.0"

                              What is the problem?
                              Do i need to upgrade my JDK/JRE??

                              Thanks.


                              Edit:
                              Switched environment variable ODI_JAVA_HOME to 1.6.
                              Instead got the following error: java.lang.NoClassDefFoundError: org/jconfig/event/ConfigurationListener

                              Edited by: Iwan Toma on Jan 17, 2011 12:05 AM
                              • 12. Re: Excel problem in ODI
                                NagarajY
                                Reffer the following thread . Hope will helps you to resolve your issue.

                                http://kr.forums.oracle.com/forums/thread.jspa?threadID=973359

                                Thanks,
                                Yellanki
                                • 13. Re: Excel problem in ODI
                                  1004730
                                  Hi Gnanaprakasam,
                                  I have seen your posts related to the issues with EXCEL with ODI. I followed the approach you provided in this form. I am able to establish a connection, but when i went into model to do reverse engineering, i encountered following error msg. Please let me know if you have any idea on this error message.

                                  java.util.concurrent.ExecutionException: java.sql.SQLException: jxlReader cannot read from \u01\app\odi\app\TASK\
                                       at java.util.concurrent.FutureTask$Sync.innerGet(FutureTask.java:222)
                                       at java.util.concurrent.FutureTask.get(FutureTask.java:83)
                                       at com.sunopsis.graphical.tools.utils.swingworker.SwingWorker.get(SwingWorker.java:476)
                                       at com.sunopsis.graphical.frame.edit.EditFrameSnpModel$8.done(EditFrameSnpModel.java:2622)
                                       at com.sunopsis.graphical.tools.utils.swingworker.SwingWorker$4.run(SwingWorker.java:615)
                                       at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:209)
                                       at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:672)
                                       at java.awt.EventQueue.access$400(EventQueue.java:81)
                                       at java.awt.EventQueue$2.run(EventQueue.java:633)
                                       at java.awt.EventQueue$2.run(EventQueue.java:631)
                                       at java.security.AccessController.doPrivileged(Native Method)
                                       at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
                                       at java.awt.EventQueue.dispatchEvent(EventQueue.java:642)
                                       at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
                                       at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
                                       at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:178)
                                       at java.awt.Dialog$1.run(Dialog.java:1052)
                                       at java.awt.Dialog$3.run(Dialog.java:1104)
                                       at java.security.AccessController.doPrivileged(Native Method)
                                       at java.awt.Dialog.show(Dialog.java:1102)
                                       at java.awt.Component.show(Component.java:1591)
                                       at java.awt.Component.setVisible(Component.java:1543)
                                       at java.awt.Window.setVisible(Window.java:843)
                                       at java.awt.Dialog.setVisible(Dialog.java:987)
                                       at oracle.bali.ewt.dialog.JEWTDialog.runDialog(JEWTDialog.java:395)
                                       at oracle.bali.ewt.dialog.JEWTDialog.runDialog(JEWTDialog.java:356)
                                       at oracle.ide.dialogs.ProgressBar.start(ProgressBar.java:352)
                                       at oracle.ide.dialogs.ProgressBar.start(ProgressBar.java:243)
                                       at oracle.ide.dialogs.ProgressBar.start(ProgressBar.java:215)
                                       at com.sunopsis.graphical.tools.utils.SnpsSwingUtilities.executeAndBlockGUI(SnpsSwingUtilities.java:181)
                                       at com.sunopsis.graphical.frame.edit.EditFrameSnpModel.jButtonReverse_ActionEvents(EditFrameSnpModel.java:2650)
                                       at com.sunopsis.graphical.frame.SnpsEditFrame$IvjEventHandler.actionPerformed(SnpsEditFrame.java:206)
                                       at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
                                       at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
                                       at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
                                       at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
                                       at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
                                       at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:273)
                                       at java.awt.Component.processMouseEvent(Component.java:6297)
                                       at javax.swing.JComponent.processMouseEvent(JComponent.java:3275)
                                       at java.awt.Component.processEvent(Component.java:6062)
                                       at java.awt.Container.processEvent(Container.java:2039)
                                       at java.awt.Component.dispatchEventImpl(Component.java:4660)
                                       at java.awt.Container.dispatchEventImpl(Container.java:2097)
                                       at java.awt.Component.dispatchEvent(Component.java:4488)
                                       at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4575)
                                       at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4236)
                                       at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4166)
                                       at java.awt.Container.dispatchEventImpl(Container.java:2083)
                                       at java.awt.Window.dispatchEventImpl(Window.java:2489)
                                       at java.awt.Component.dispatchEvent(Component.java:4488)
                                       at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:674)
                                       at java.awt.EventQueue.access$400(EventQueue.java:81)
                                       at java.awt.EventQueue$2.run(EventQueue.java:633)
                                       at java.awt.EventQueue$2.run(EventQueue.java:631)
                                       at java.security.AccessController.doPrivileged(Native Method)
                                       at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
                                       at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
                                       at java.awt.EventQueue$3.run(EventQueue.java:647)
                                       at java.awt.EventQueue$3.run(EventQueue.java:645)
                                       at java.security.AccessController.doPrivileged(Native Method)
                                       at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
                                       at java.awt.EventQueue.dispatchEvent(EventQueue.java:644)
                                       at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
                                       at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
                                       at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
                                       at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
                                       at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
                                       at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
                                  Caused by: java.sql.SQLException: jxlReader cannot read from \u01\app\odi\app\TASK\
                                       at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:133)
                                       at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.getConnection(LoginTimeoutDatasourceAdapter.java:62)
                                       at oracle.odi.core.datasource.dwgobject.support.OnConnectOnDisconnectDataSourceAdapter.getConnection(OnConnectOnDisconnectDataSourceAdapter.java:74)
                                       at com.sunopsis.sql.SnpsConnection.testConnection(SnpsConnection.java:1128)
                                       at com.sunopsis.dwg.reverse.Reverse.getLocalConnection(Reverse.java:1287)
                                       at com.sunopsis.dwg.reverse.Reverse.getLocalConnection(Reverse.java:1336)
                                       at com.sunopsis.dwg.reverse.Reverse.getLocalConnection(Reverse.java:1301)
                                       at com.sunopsis.dwg.reverse.Reverse.getMetaData(Reverse.java:1357)
                                       at com.sunopsis.graphical.frame.edit.EditFrameSnpModel$8.doInBackground(EditFrameSnpModel.java:2587)
                                       at com.sunopsis.graphical.tools.utils.swingworker.SwingWorker$1.call(SwingWorker.java:240)
                                       at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
                                       at java.util.concurrent.FutureTask.run(FutureTask.java:138)
                                       at com.sunopsis.graphical.tools.utils.swingworker.SwingWorker.run(SwingWorker.java:278)
                                       at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:655)
                                       at java.lang.Thread.run(Thread.java:662)
                                  Caused by: java.sql.SQLException: jxlReader cannot read from \u01\app\odi\app\TASK\
                                       at com.nilostep.xlsql.jdbc.xlConnection.startup(xlConnection.java:575)
                                       at com.nilostep.xlsql.jdbc.xlConnectionHSQLDB.<init>(xlConnectionHSQLDB.java:46)
                                       at com.nilostep.xlsql.jdbc.xlConnection.factory(xlConnection.java:84)
                                       at com.nilostep.xlsql.jdbc.xlDriver.connect(xlDriver.java:146)
                                       at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:410)
                                       at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:386)
                                       at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:353)
                                       at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:332)
                                       at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter$ConnectionProcessor.run(LoginTimeoutDatasourceAdapter.java:217)
                                       at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:439)
                                       at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
                                       at java.util.concurrent.FutureTask.run(FutureTask.java:138)
                                       at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)
                                       at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)
                                       ... 1 more
                                  • 14. Re: Excel problem in ODI
                                    VincenzoGrimaldi

                                    Hi Guys,

                                     

                                    there is no needs for open source drivers, the problem is with the odiRef.getFrom method that is not adding brackets to the table name (that for an excel file is the name of the spreadsheet itself). To avoid this error and continue using standards KM is simply necessary to assign the following phisical name to the datastore:

                                     

                                    [XXXXXXXXX$]

                                     

                                    Regards,

                                     

                                    VGrim