JDBC DataSource to Excel

[M_McDonald]

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.

[SmartBear_Suppo]

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]

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]

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]

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]

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

[M_McDonald]

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!

[Teena]

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.