Configuring Connections: Microsoft Excel via the QuerySurge JDBC Driver for Excel

Versions: 8.0+

Versions:6.3+

QuerySurge now supports Microsoft Excel using QuerySurge's own JDBC-compliant Excel driver. Using this driver, you can use a standard SQL vocabulary to query your Excel files across all supported operating systems. See the QuerySurge Excel JDBC Syntax article for available syntax and examples.

For a QuerySurge Excel Connection, you will need the following information:

• The directory/file path of the Excel file(s)
• Whether your Excel sheets use the first row as a header row.
• Whether to infer column datatypes based on Excel formatting or return all data as a VARCHAR type.

Optionally, the following settings can be used to help Excel queries run on your Connection more effectively:

• The maximum number of bytes in the largest cell in any column. Typically, this is the maximum number of characters, unless your file contains Unicode characters.
• Whether the Excel processing should be performed on disk or in memory.
• Whether to detect potential zip bomb files.

Launch the Add Connection Wizard

1. Log into QuerySurge as an Admin user, and navigate to the Administrative view.
   
   
2. To configure a Connection, select Configuration > Connection in the Administrative tree (at the left).
   
   
3. Click on the Add button at the bottom left of the main panel to launch the Connection Wizard. Click Next.
   
   Note: Check the Advanced Mode checkbox for access to advanced features (such as String Options and Storage and File Options).
   
   
   
   
4. Provide a name for your Connection. Select QuerySurge Excel JDBC as the Data Source. Click Next.
   

   
   
   

5. File Details. Provide a full path to either the file's directory or the file itself including the appropriate file extension (e.g. "C:\myfolder" or "C:\myfolder\myexcel.xlsx"). The directory may be a network share directory (either mapped or UNC paths work).
   
   
6. Data Settings. Specify whether header information is included as the first row of your result set by setting Column Headers to Data in first row, and whether to inference column datatypes based on their Excel formatting or return all columns as VARCHAR by choosing an option under Data Types.
   
   
   
   
7. String Options. This section allows you to fine-tune your Excel Connection. (To use these settings, you need to check the Advance Mode checkbox at the beginning of the Wizard.)
   
   The Max String Width sets the maximum number of bytes in any column in your Excel sheet. Specifying a value smaller than the size of the largest data (viewing all data as a string type for this purpose) will lead to truncation. The default value is 500 bytes.
   
   
8. Storage and File Options. These settings offer the ability to choose where your Excel data is processed and whether to check it for "zip bomb" malicious intent. 
   
   The Process Excel Data option determines whether the driver processes your Excel data on disk or in memory. Choosing In Memory should result in significantly faster querying, you need to be certain that there is sufficient memory available and configured for the data. The On Disk option avoids any memory-associated issues by processing on disk, but usually is slower than processing in memory. The default is to run On Disk.
   
   Setting Detect Zip Bombs to Detect allows the driver to error out gracefully when the queried Excel sheet qualifies as a zip bomb. Excel files are zip-type archives, and a zip bomb is a malicious archive file designed to consume inordinate amounts of disk space and  memory when decompressed. The driver considers Excel files with inflation ratios below 0.01 as zip bombs, or files greater than 4.3 GB on disk after inflating. The Ignore option is the default.
   
   Note: Excel files with a decompressed file size of 4.3 GB on disk or greater will force an exception regardless of the file's inflation ratio or this setting. Thus, Excel files greater than 4.3 GB on disk after decompression cannot be used with this driver.
   
   
   
   
9. After you have reviewed your connection information, Save the Connection.
   
   
   
   
10. Congratulations! You've created a QuerySurge Connection. You're ready to use QuerySurge with Microsoft Excel files.
    
    Note: Make sure that you have deployed the driver for this Connection to all your QuerySurge Agents. See Adding a JDBC Driver to your QuerySurge Agent for instructions.
    
    Note: The user login under which the QuerySurge Agent is running must have read access to the directory path in which your Excel files are located. 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 for instructions.