Ask a Question

JDBC DataSource to Excel

M_McDonald
Super Contributor

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.
"Ignorance more frequently begets confidence than does knowledge"
7 REPLIES 7

Re: JDBC DataSource to Excel

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

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
M_McDonald
Super Contributor

Re: JDBC DataSource to Excel

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
"Ignorance more frequently begets confidence than does knowledge"

Re: JDBC DataSource to Excel

Hello,

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

Let me know does this helps,
robert

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
M_McDonald
Super Contributor

Re: JDBC DataSource to Excel

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.
"Ignorance more frequently begets confidence than does knowledge"

Re: JDBC DataSource to Excel

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

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
M_McDonald
Super Contributor

Re: JDBC DataSource to Excel

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!
"Ignorance more frequently begets confidence than does knowledge"
Teena
Frequent Visitor

Re: JDBC DataSource to Excel

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.

 

cancel
Showing results for 
Search instead for 
Did you mean: