Problem In accessing Excel through SQL Connector.

Home Forums Convertigo developers Forums as moved to Stack Overflow. Problem In accessing Excel through SQL Connector.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #143477

    I am trying to read the content from Excel through SQL connector using the specified Excel driver.
    With this I am able to run the sample project for Excel SQL connector. but if I replaced the excel file with another one, I am not able to get data even after I change the filename
    It is Showing below Error,

    <?xml version=”1.0″ encoding=”UTF-8″?>

    <document connector=”” context=”studio_sql_sample:S:sampleSelection” contextId=”studio_sql_sample:S:sampleSelection” fromStub=”false” fromcache=”false” generated=”Thu Jun 30 11:03:39 IST 2016″ project=”sql_sample” sequence=”sampleSelection” signature=”1467264819091″ transaction=”” version=”7.1.0 (build 37181)”>
    <connectionString>jdbc:odbc:DRIVER={MICROSOFT EXCEL DRIVER (*.XLS)};FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=”D:\Renault\Renault_workspace\Renault_sqlconn_sample\sql_sample\xls\tt.xls”;DBQ=D:\Renault\Renault_workspace\Renault_sqlconn_sample\sql_sample\xls\tt.xls</connectionString>
    <error connector=”sql_sampleConnector” project=”sql_sample” transaction=”sampleSelection” type=”c8o”>
    -1
    <message>An unexpected error occured while executing transaction. Could not execute the SQL query.</message>
    <details>Unable to retrieve a valid connection from pool, Cannot create PoolableConnectionFactory ([Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object ‘People’. Make sure the object exists and that you spell its name and the path name correctly.), [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object ‘People’. Make sure the object exists and that you spell its name and the path name correctly.</details>
    <context/>
    <exception>com.twinsoft.convertigo.engine.EngineException</exception>
    <stacktrace>com.twinsoft.convertigo.engine.EngineException: An unexpected error occured while executing transaction. Could not execute the SQL query.
    at com.twinsoft.convertigo.beans.transactions.SqlTransaction.runCore(SqlTransaction.java:840)
    at com.twinsoft.convertigo.beans.core.RequestableObject$RequestableThread.run(RequestableObject.java:798)
    Caused by: com.twinsoft.convertigo.engine.EngineException: Unable to retrieve a valid connection from pool
    at com.twinsoft.convertigo.beans.connectors.SqlConnector.open(SqlConnector.java:224)
    at com.twinsoft.convertigo.beans.connectors.SqlConnector.prepareStatement(SqlConnector.java:284)
    at com.twinsoft.convertigo.beans.transactions.SqlTransaction.prepareQuery(SqlTransaction.java:386)
    at com.twinsoft.convertigo.beans.transactions.SqlTransaction.runCore(SqlTransaction.java:449)
    … 1 more
    Caused by: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory ([Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object ‘People’. Make sure the object exists and that you spell its name and the path name correctly.)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
    at com.twinsoft.convertigo.engine.JdbcConnectionManager.getConnection(JdbcConnectionManager.java:195)
    at com.twinsoft.convertigo.beans.connectors.SqlConnector.open(SqlConnector.java:222)
    … 4 more
    Caused by: java.sql.SQLException: [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object ‘People’. Make sure the object exists and that you spell its name and the path name correctly.
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6964)
    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7121)
    at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3117)
    at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:337)
    at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:252)
    at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
    at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:658)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1558)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
    … 8 more
    </stacktrace>
    </error>
    </document>

    I am suspecting problem lies here , The Microsoft Jet database engine could not find the object ‘People’
    Can you please tell me what is the problem here and how we can specify from which worksheet we need to load incase we have multiple sheets in single Excel file.

    Thanks,

    #143478

    admin
    Keymaster

    Hello

    Seems that your are accessing a spread sheet not containing a ‘people’ column. Convertigo uses the Excel ODBC Driver do access data on the XLS files.

    The Way this ODBC driver works is not supported by us but by Microsoft. You must look at Microsoft documentation about the EXCEL ODBC driver to know how you must build your SQL request to match Data on your spread sheets.

    Here is for example an article explaining how this works.

    https://www.simple-talk.com/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc–/

    Many other articles are available The web …

    Hope That Helps
    Convertigo Support team.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.