How to Connect to an Excel Spreadsheet Using JDBC-ODBC Bridge Driver?

JDBC-ODBC can be used to access MS Excel spreadsheets as if they were databases, and thus we could utilize the power of SQL.

There are two ways to connect to a spreadsheet file using jdbc-odbc: Using DSN connection and Using DSN-less connection. The main difference is the construction of JDBC URL.

1. Using DSN connection

To use DSN connection, we firstly need to set up the Excel Spreadsheet as an ODBC source by using Windows Administrative Tools. The details of creating a User DSN can be found from here. Once the DSN is defined, we can interact the target spreadsheet file using jdbc-odbc. The db connection string is (please refer the complete sample for details ):

java.sql.Connection c = java.sql.DriverManager.getConnection( "jdbc:odbc:qa", "", "" );

“qa” is the name of DSN which points at a spreadsheet file which is going to be processed.

2. Using DSN-less connection

It is also possible to connect to a spreadsheet without using DSN, which provides a more flexible way within code to point JDBC at an Excel file of interest without the accesses to a client registry to define the required DSN. Without DSN, the db connection is created as following, please not the difference of constructed JDBC URL:

java.sql.DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/Documents and Settings/myPath/Desktop/qa.xls");

Here DBQ defines the path to the target spreadsheet file (qa.xls). Both backslash and forward slash work well.

With using DSN, we need easy access to a client registry to define the required DSN, while jdbc-odbc driver provide a more flexible method to connect to spreadsheet files without DSN.

3. A complete example of connecting to a spreadsheet using JDBC-ODBC

Here is a complete sample of connecting to a Spreadsheet file using JDBC

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;

public class ExcelReader
{
public static void main( String [] args )
{
Connection c = null;
Statement stmnt = null;

try
{
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );

//using DSN connection. Here qa is the name of DSN
//c = DriverManager.getConnection( "jdbc:odbc:qa", "", "" );

//using DSN-less connection
c = DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/Documents and Settings/tangk1.FIC/Desktop/qa.xls");

stmnt = c.createStatement();
String query = "select * from [qas test$] where Month='March' and Year=2001;";
ResultSet rs = stmnt.executeQuery( query );

System.out.println( "Found the following URLs:" );
while( rs.next() )
{
System.out.println( rs.getString( "URL" ) + " " + rs.getInt("Year"));
}
}
catch( Exception e )
{
System.err.println( e );
}
finally
{
try
{
stmnt.close();
c.close();
}
catch( Exception e )
{
System.err.println( e );
}
}
}
}

Reference:

JDBC-ODBC Bridge Driver Enables Spreadsheet-as-database Interaction ( http://www.devx.com/Java/Article/17848)