User is connected to an Excel file in Toad for Data Analysts. They have a column with null and empty strings. They want to run a query using the WHERE clause to exclude nulls and empty strings.
RESOLUTION:
You can run queries on an Excel connection in the Editor window. In Excel, the SQL syntax for NOT NULL is
WHERE TABLE.COLUMN IS NOT NULL
The syntax exclusion operator (!=) is <>. The character for empty string is two single quotes, ''
WHERE TABLE.COUMN <> ''
A complete Excel query example, where the file name is ExcelFile.xlsx, and the worksheet name is Sheet1:
SELECT `Sheet1`.EMPLOYEE_ID
, `AutoRange_Sheet1`.FIRST_NAME
, `AutoRange_Sheet1`.LAST_NAME
FROM `C:\ExcelFile.xlsx`.`Sheet1` `Sheet1`
WHERE (`Sheet1`.LAST_NAME is not null AND `Sheet1`.LAST_NAME <> '')
Please contact Microsoft for Excel documentation for syntax and operators.
