Microsoft Excel ODBC / QuerySurge Excel Syntax Conversion
With the advantages the QuerySurge Excel JDBC driver has over the conventional Microsoft Excel ODBC driver via the JDBC/ODBC bridge, users upgrading to QuerySurge version 6.3 (or higher) convert existing Excel queries to work with the QuerySurge Excel driver. This article is designed to help you with the conversion process. Following are common Excel queries, written in the new driver (ANSI-compliant) SQL syntax; these examples are equivalent to the sample queries listed in our Writing SQL Queries against Excel files article.
- Simple SELECT *
Microsoft ODBC:
SELECT * FROM [SALES$]
QuerySurge Excel:
with file in Connection...
SELECT * FROM [SALES$]
with directory in Connection...
SELECT * FROM [march2017/financials.xlsx].[SALES$];
As explained in our QuerySurge Excel JDBC Syntax article, when a Connection points to a directory of Excel files instead of a single file, the QuerySurge Excel JDBC driver expects the relative path to an Excel file within said directory. Like the example above, simply include its relative path within brackets ( [] ) before the sheet name, separated by a period.
In an attempt to simplify the following examples, a file-specific Connection will be assumed.
Similar to the ODBC driver, sheet names are followed by a dollar sign ( $ ).
Note that file or sheet names starting with numeric characters must be enclosed in double quotes: ex. ["4DAYS"$] - Simple SELECT
Microsoft ODBC:
SELECT [QUANTITY],[NAME],[PRICE] FROM [SALES$];
QuerySurge Excel:
SELECT "quantity", "name", "price" FROM [SALES$];
Because of the QuerySurge JDBC driver's ANSI-compliant syntax, the brackets used to identify column names in the ODBC driver are replaced with double-quotes ( "" ). - WHERE Clause
Microsoft ODBC:
SELECT [QUANTITY],[NAME],[PRICE] FROM [SALES$]
WHERE [SALE_ID] >= 23 AND [SALE_ID] <= 28;
QuerySurge Excel:
SELECT "quantity", "name", "price" FROM [SALES$]
WHERE "sale_id" >= 23 AND "sale_id" <= 28;
WHERE clauses remain unchanged from the ODBC driver's Excel SQL syntax. - Cell Ranges
Microsoft ODBC:
SELECT [QUANTITY],[NAME],[PRICE] FROM [SALES$A1:E101];
QuerySurge Excel:
SELECT "quantity", "name", "price" FROM [SALES$].[A1:E101];
Querying a specific range within a sheet uses a similar notation to the ODBC driver. Instead of separating the sheet name and range by the dollar sign, the two properties are now specified within their own bracket blocks separated by a period. This notation allows for a cleaner and more legible query. - Filter By Date. Filter by date with an ORDER BY
Microsoft ODBC:
SELECT [QUANTITY],[NAME],[PRICE] FROM [SALES$A1:E101]
WHERE [SALE_DATE] BETWEEN #12/6/2003# AND #1/1/2008#
ORDER BY [SALE_ID];
QuerySurge Excel:
SELECT "quantity", "name", "price" FROM [SALES$].[A1:E101]
WHERE "sale_date" BETWEEN '2003-12-6' AND '2008-1-1'
ORDER BY "sale_id" ASC;
Unlike the ODBC Excel SQL, the QuerySurge Excel JDBC driver expects all non-numeric values within single-quotes ( '' ).
*Specifying ASC or DESC is required when using ORDER BY with the QuerySurge Excel driver - String Functions
Microsoft ODBC:
SELECT [QUANTITY],[NAME],[PRICE] FROM [SALES$A1:E101]
WHERE [SALE_DATE] BETWEEN #12/6/2003# AND #1/1/2008#
AND MID([NAME],1,4) = 'NYNY'
ORDER BY [SALE_ID];
QuerySurge Excel:
SELECT "quantity", "name", "price" FROM [SALES$].[A1:E101]
WHERE "sale_date" BETWEEN '2003-12-6' AND '2008-1-1'
AND SUBSTR("name", 1, 4) = 'NYNY'
ORDER BY "sale_id";
The QuerySurge Excel JDBC driver supports a host of string functions provided by H2. For a list of these functions visit the H2 Database Functions Documentation. - Data Type Conversions
Microsoft ODBC:
SELECT [ID],CLNG([OID]),[DESC],[SALE_DATE] FROM [PRODUCTS$];
QuerySurge Excel:
SELECT "id", CAST("oid" AS INTEGER), "desc", "sale_date" FROM [PRODUCTS$];
Like other ANSI SQL drivers, QuerySurge Excel SQL takes advantage of the CAST instruction to change the datatype of a given column. - Date Formatting
Microsoft ODBC:
SELECT FORMATDATETIME( [SALE_DATE], 1),
FORMAT([SALE_DATE], 'yyyy-mm-dd')
FROM [SALES$]
WHERE [SALE_DATE] BETWEEN #12/6/2003# AND #1/1/2008#;
QuerySurge Excel:
SELECT "sale_date",
FORMATDATETIME("sale_date",'yyyy-MM-dd')
FROM [SALES$]
WHERE "sale_date" BETWEEN '12/6/2003' AND '1/1/2008';
Instead of using an id from a predefined format list of format strings like the ODBC driver, the inherited H2 database FORMATDATETIME function lets us explicitly provide our date format of choice. - Number Formatting
Microsoft ODBC:
SELECT [QUANTITY],[NAME],format([PRICE],'###.000')
FROM [SALES$]
WHERE [SALE_DATE] BETWEEN #12/6/2003# AND #1/1/2008#;
QuerySurge Excel:
SELECT "quantity", "name",TO_CHAR("price",'###.000')
FROM [SALES$]
WHERE "sale_date" BETWEEN '12/6/2003' AND '1/1/2008';
When formatting numeric values in H2 and subsequently QuerySurge Excel, we use the to_char function. Simply provide the name of the desired column followed by the expected format string. - Conditionals
Microsoft ODBC:
SELECT [QUANTITY],[NAME],
IIf([PRICE] < 1.00,'SPECIAL!',[PRICE])
FROM [SALES$];
QuerySurge Excel:
SELECT "quantity", "name",
CASE WHEN "price" < 1.00 THEN 'SPECIAL!' ELSE "price" END
FROM [SALES$];
As expected from an ANSI SQL driver, CASE blocks are used to query columns under conditions.
a) Null Handling
Microsoft ODBC:
SELECT IsNull([QUANTITY])
FROM [SALES$];
QuerySurge Excel:
SELECT "quantity" IS NULL
FROM [SALES$];
b) Null Handling As A Condition
Microsoft ODBC:
SELECT IIf(IsNull([QUANTITY]),'NULL FOUND!', [PRICE])
FROM [SALES$];
QuerySurge Excel:
SELECT
CASE WHEN "price" IS NULL
THEN 'NULL FOUND!'
ELSE "price" END
FROM [SALES$];
replaces the ODBC Excel SQL IsNull function with standard "IS NULL" instruction.