Easysoft Blog

Tue, 30 May 2017

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 via 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 via the normal ODBC driver.

The Easysoft JDBC-Access Driver 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 Driver to connect Java applications such as SQuirreL SQL to Microsoft Excel.

Installing the Easysoft JDBC-Access Driver

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

  1. Download the JDBC-Access Driver. (Registration required.)
  2. Install and license the JDBC-Access Driver on the machine where the SQuirreL SQL is installed.

    Install the JDBC-Access Driver into the default folder.

    For installation instructions, see the JDBC-Access Driver documentation.

  3. If you are using a 64-bit version of Windows, 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-Access Gateway\32-Bits\Libs\

    Make sure that this folder appears before C:\Program Files\Easysoft Limited\Easysoft JDBC-Access 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 Driver JAR file, drive:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway\32-Bits\Libs\esmdb.jar.
  5. Complete the dialog box fields:
    FieldValue
    NameEaysoftExcelDriver
    Example URLjdbc: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 Nameeasysoft.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.