Forum Discussion

M_McDonald's avatar
M_McDonald
Super Contributor
14 years ago

JDBC DataSource to Excel

Hi -

I can connect to a spreadsheet with JDBC like this:

Connection c = DriverManager.getConnection( 'jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/test.xls');
Statement stmnt = c.createStatement();
String query = 'select * from [Sheet1$];'
ResultSet rs = stmnt.executeQuery( query );
rs.next()  
log.info ( rs.getString( "Name" ) )


How can I configure a JDBC DataSource to access an Excel file I specify dynamically?

Thanks.

7 Replies

Sort By
  • SmartBear_Suppo's avatar
    SmartBear_Suppo
    SmartBear Alumni (Retired)
    14 years ago
    Hello,

    Hm...You could do this by creating a property ( on test case level for example ) in which you'll store a path to your excel file. Than you'll use a property expansion for File when configuring DataSource. To make it easier just do right click on File text box, you'll get popup menu , choose "Get Data ..." and follow to your property which keeps excel file path.

    Hope this helps, let me know,
    robert
  • M_McDonald's avatar
    M_McDonald
    Super Contributor
    14 years ago
    Hi Robert-

    Actually I was trying to get help on configuring the DataSource itself, the standard JDBC-ODBC driver setup doesn't seem to lend itself to this.

    Thanks.

    - Michael
  • SmartBear_Suppo's avatar
    SmartBear_Suppo
    SmartBear Alumni (Retired)
    14 years ago
    Hello,

    Use DataSource test step, for DataSource use Excel and than you can do as I explained.

    Let me know does this helps,
    robert
  • M_McDonald's avatar
    M_McDonald
    Super Contributor
    14 years ago
    Ahh, there's the misunderstanding. I want to use JDBC type not Excel, with the JDBC-ODBC connector so I can get random access to the rows in the spreadsheet. In other words, I want to be able to use the spreadsheet like a hashmap instead of having to move through it sequentially.
  • SmartBear_Suppo's avatar
    SmartBear_Suppo
    SmartBear Alumni (Retired)
    14 years ago
    Hello,

    Ah...well same thing. You specify file path in connection string. Like you have in your example:
    jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/test.xls

    Just for path put property expansion. Like:

    jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=${#TestCase#files}

    where files is property on test case where i keep path to excel file.

    Does this works for you,
    robert
    • Teena's avatar
      Teena
      Frequent Visitor
      8 years ago

      Hello,

       

      I am using ReadyAPI version 1.7.0.

      For one of the mock responses, I am trying to configure a JDBC connection with an excel as the database (ServiceV->DataSources).

      In the DataBase configuration wizard, 

      Driver is set to sun.jdbc.odbc.JdbcOdbcDriver and Database is set to - Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\test\datasource.xls.

      When  I test this connection using the Test connection option in the same wizard, it throws the error:

      Failed to init connection for driver [sun.jdbc.odbc.JdbcOdbcDriver], connectionString [jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\test\datasource.xls]

      Can you please help?

       

      Thanks.

       

  • M_McDonald's avatar
    M_McDonald
    Super Contributor
    14 years ago
    OK, got it now. I was trying to configure from the DataSource step instead of creating a new entry in the global JDBC Drivers screen.

    Thanks!