QuerySurge™ Setup for Microsoft Excel™
Note: QuerySurge 6.2 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 the QuerySurge JDBC Driver for Excel are available in this article.
- 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
- 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.
Important 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
- Choose the ‘Add Connection’ option from the Administration menu to open the Connection Wizard.
- In the Connection Wizard, enter a Connection Name and Select the 'Excel' from the Data Source list.
- Choose the Connection Type based on whether your Excel (or Excel ODBC driver) is Excel 2003 or earlier, or 2007 or later.
- 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).
- Click Next. Review your settings. You can click the Test Connection button to make sure the Agent can 'see' the Excel directory.
- Click the Save button. Your Excel Connection is completed.