Connecting SQuirreL SQL to Microsoft Excel

SQuirreL SQL is written in Java. To interact with a database, a Java application uses a JDBC driver. JDBC is a Java application programming interface (API) that connects Java to relational databases (and other tabular data, such as spreadsheets and flat files). A JDBC driver implements the JDBC API for a particular database.

Microsoft do not produce a JDBC driver for Jet (the underlying database engine for the Microsoft Office product suite) or Jet's successors the Office Access Connectivity Engine and the Access Database Engine. However, Microsoft do produce an ODBC driver for the Office suite. (ODBC is another data access technology, the Microsoft implementation of which is included with Windows.) To provide a JDBC interface to Office applications by using this native Microsoft interface, a JDBC driver must be able to convert JDBC calls to ODBC calls. As far as the Java application is concerned, it is using a normal JDBC driver. As far as the Office application is concerned, it is being accessed by using the normal ODBC driver.

The Easysoft JDBC-Access Gateway is a JDBC driver for Microsoft Excel that uses the Java Native Interface (JNI) to communicate with the Excel ODBC driver library.

You can use the Easysoft JDBC-Access Gateway to connect Java applications such as SQuirreL SQL to Microsoft Excel.

Installing the Easysoft JDBC-Access Gateway

If you have not already done so, please register with us to download a fully functional trial version of JDBC-Access Gateway.

  1. Download the JDBC-Access Gateway.
  2. Install and license the JDBC-Access Gateway on the machine where SQuirreL SQL is installed.

    Install the JDBC-Access Gateway into the default folder.

    For installation instructions, refer to the JDBC-Access Gateway documentation.

  3. In Control Panel > System > Advanced System Settings > Environment Variables, double-click PATH in the System variables list. Make sure that the PATH contains: 
    drive:\Program Files\Easysoft Limited\Easysoft JDBC-Excel Gateway\32-Bits\Libs\

    Make sure that this folder appears before C:\Program Files\Easysoft Limited\Easysoft JDBC-Excel Gateway\Libs\ in the PATH.

Connecting SQuirreL SQL to Microsoft Excel

  1. In SQuirreL SQL Client, choose the Drivers tab.
  2. Choose the Add a New Driver button.

    The Add Driver dialog box is displayed, choose the Extra Class Path tab.

  3. Choose the Extra Class Path tab. Choose the Add button.

    The Windows Open dialog box is displayed.

  4. Use the dialog box to browse for Easysoft JDBC-Access Gateway JAR file, drive:\Program Files\Easysoft Limited\Easysoft JDBC-Excel Gateway\32-Bits\Libs\esmdb.jar.
  5. Complete the dialog box fields:
    Field Value
    Name EaysoftExcelDriver
    Example URL jdbc:easysoft:xls?DBQ=spreadsheet

    where spreadsheet is the Excel file that you want to connect to (which must be a .xls Excel 97-2003 format file). Include the path, for example, C:/Users/Public/Sales.xls. Note that the forward slashes (/) in the path are deliberate, you need to use these instead of backslashes (\).
    Class Name easysoft.sql.esXlsDriver
  6. In the main SQuirreL SQL window, choose Aliases.
  7. Choose Create a New Alias button.

    The Add Alias dialog box is displayed.

  8. In the Driver list, choose EasysoftExcelDriver.
  9. In the Name box, enter EasysoftExcel.
  10. In the Aliases pane, select EasysoftExcel, and then choose the Connect to Selected Aliases button.

    The Connect to dialog box is displayed.

  11. Choose the Connect button.
  12. In the SQL pane, enter: 
    select * from  [sheet1$a1:a5]
  13. Choose the Run SQL button.