This discussion is archived
14 Replies Latest reply: Aug 6, 2013 7:16 AM by VincenzoGrimaldi RSS

Excel problem in ODI

805690 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    You´re Welcome.
  • 8. Re: Excel problem in ODI
    DecaXD Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    IwanToma Newbie
    Currently Being Moderated
    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
    Yellanki Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points