Browse DevX
Sign up for e-mail newsletters from DevX


JDBC-ODBC Bridge Driver Enables Spreadsheet-as-database Interaction

Java APIs such as Apache Jakarta POI allow applications to programmatically modify Microsoft Excel spreadsheets but only on a cell-by-cell basis. Using JDBC in conjunction with ODBC, Java programmers can interact with Excel spreadsheets as if they were databases.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

ava programmers who use JDBC may presume that the technology is restricted to interaction with database products such as IBM DB2 Universal Database, Informix, Oracle, or Microsoft SQL Server. This article illustrates how JDBC can be used in conjunction with ODBC to interact with Microsoft Excel spreadsheets as if they were databases.

Programming interfaces such as Apache Jakarta POI allow a Java application to programmatically modify Excel spreadsheets (see "Learn to Read and Write Microsoft Excel Documents with Jakarta's POI"). Unfortunately, such APIs allow only cell-by-cell interaction with spreadsheets. They do not enable interacting with a spreadsheet as if it were a database, which utilizes the power of SQL. By tapping into the JDBC-ODBC bridge driver, you can work with spreadsheets as databases.

Setting Up Your Excel Spreadsheet as an ODBC Source
Start off by creating a simple Excel spreadsheet with which you will interact. Table 1 shows the structure you want to create. It contains population estimate data for the four most populous states in the US as estimated by the U.S. Census Bureau as of July 1, 2002.

Table 1. Population Estimates for Most Populous States
State Population
California 35,116,033
Texas 21,779,893
New York 19,157,532
Florida 16,713,149

Figure 1 shows a screenshot of the spreadsheet you will need to create. I named the spreadsheet "statepopulation.xls." You can also download the spreadsheet.

Click to enlarge
Figure 1: The statepopulation.xls Spreadsheet

Next, you need to register your spreadsheet as an ODBC Data Source. These instructions assume that you are using Microsoft Windows 2000, but registering ODBC Data Sources in other Microsoft Windows operating systems should be quite similar.

Open the Windows Control Panel. Next, open up Administrative Tools. Double click on the Data Sources (ODBC) icon (see Figure 2).

Click to enlarge
Figure 2: The Data Sources Administrative Tool

In the User DSN tab, choose the Excel files option and click Add (see Figure 3).

Click to enlarge
Figure 3: Add an Excel File Data Source

In the subsequent driver selection page, choose the "Microsoft Excel Driver" option and click Finish (see Figure 4).

Click to enlarge
Figure 4: Choose the Microsoft Excel Driver

Next, you will be presented with a window in which you will select the Excel file that you want to setup as an ODBC source. Choose the Select Workbook button (see Figure 5) and choose the spreadsheet you created (statepopulation.xls).

Click to enlarge
Figure 5: Select the Workbook You Want to Setup as a Data Source

If you plan on using JDBC to write to the Excel file, you must deselect the Read Only option.

You should be returned to the ODBC Microsoft Excel Setup window. When you click the Options>> button, you see a Rows to Scan option. Since you have only a few records, you don't need to increase the number.

Go ahead and name your Data Source Name as ExcelJDBCTest. You can include a description (see Figure 6).

Click to enlarge
Figure 6: Specifying a Name for the Data Source

In the ODBC Data Source Administrator screen shown in Figure 7, you should see the ODBC Data Source you just created.

Click to enlarge
Figure 7: Confirm the Data Source Was Established

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date