Configuring Connections: Microsoft Excel via Java's JDBC/ODBC Bridge

Versions: 8.0+

QuerySurge™ Setup for Microsoft Excel™

Versions:1.0+

 Note: QuerySurge 6.3 and below connect to Excel using Java's built-in JDBC/ODBC bridge along with the Microsoft's Excel ODBC driver. This article describes the setup for this JDBC/ODBC bridge-based approach. Since QuerySurge 6.3, QuerySurge has shipped with its own proprietary JDBC driver for Excel. Users are strongly urged to use this all-Java JDBC driver for Excel data, as Java's JDBC/ODBC bridge has been removed in Java 8 and above. Details for connecting with the QuerySurge JDBC Driver for Excel are available in this article.

 Note: Connection to Excel via Java's builtin ODBC/JDBC bridge feature (on which this connection depends) is being deprecated starting with the QuerySurge 6.4 release, since Java has removed the bridge feature. While QuerySurge 6.4 is backwards-compatible for your existing Excel queries, you should start planning to move your existing queries to the QuerySurge Excel JDBC Driver. The current date for sunset of backwards-compatibility is October 31, 2019. For more information, see our FAQ.

 Note: The option to create new Excel Connections via Java's builtin ODBC/JDBC is deprecated in QuerySurge version 9.0+. Existing Connections should still run. If you haven't already, you should convert all Excel Connections to use the QuerySurge Excel JDBC Driver. For more information, see our FAQ.

Prerequisite Setup:

1. The user login under which the QuerySurge Agent is running must have access to the directory path in which your Excel files are located, and minimally must have Read permissions for the directory. This is especially important to verify if the directory is a network share directory, however it may be important even for local directories.
   
   If you need to change the user login under which the QuerySurge Agent is running, see: Setting an Agent to Run Under a Specific Login
   
   
2. The Excel ODBC driver must be installed on each QuerySurge Agent box, because the Agent uses a JDBC-ODBC bridge to connect to the files through this driver. This is usually installed with a Microsoft Office installation. If it is not, an installer may be downloaded from Microsoft for the Excel ODBC driver. See below for download links.
   
   Note: If you have 32-bit Excel or the 32-bit Excel ODBC driver on an Agent box, you should install and use the 32-bit QuerySurge Agent with it. If you have 64-bit Excel or the 64-bit Excel ODBC driver on an Agent box, you should install and use the 64-bit QuerySurge Agent with it.

Download Links for Microsoft Office ODBC drivers

• Excel 2003: Microsoft ended support for Office 2003 on April 8, 2014. Later drivers may support older Excel file formats, so if you do not have a 2003 driver installed, a later driver may work.
• Excel 2007: http://www.microsoft.com/en-us/download/details.aspx?id=23734
• Excel 2010: http://www.microsoft.com/en-us/download/details.aspx?id=13255
• Excel 2016: https://www.microsoft.com/en-us/download/details.aspx?id=54920

Note: The 2010 package, called "Microsoft Access Database Engine 2010 Redistributable," contains the drivers for both Access and Excel. (See the link for Details.)

Setup in QuerySurge

1. Choose the ‘Add Connection’ option from the Administration menu to open the Connection Wizard.
   
   
   
   
2. In the Connection Wizard, enter a Connection Name and Select the 'Excel' from the Data Source list.
   
   
   
   
3. Choose the Connection Type based on whether your Excel (or Excel ODBC driver) is Excel 2003 or earlier, or 2007 or later.
   
   
   
   
4. For the path to the file, you must provide a full path including the Excel file name with the appropriate extension (e.g. "C:\myfolder\myexcel.xls" or "C:\myfolder\myexcel.xlsx"). The directory may be a network share directory (either mapped or UNC paths work).
   
   
   
   
5. Click Next. Review your settings. You can click the Test Connection button to make sure the Agent can 'see' the Excel directory. 
   
   
   
   
6. Click the Save button. Your Excel Connection is completed.