WHERE clause Syntax for Importing Data into Sentinel Visualizer

WHERE Clause Syntax for Importing Data into Sentinel Visualizer

Introduction

Sentinel Visualizer lets you choose data to import from a variety of sources. The data can be from these formats:

Excel spreadsheet
Access database's table or query
SQL Server database's table or query
Text file

After choosing the source data, a screen with options for the data type appears with a preview of the data.

Import Excel Form Import Excel Options

At the bottom of the form is an optional WHERE clause:

WHERE clause

By default, the WHERE clause is blank and all the records are imported. You can use the WHERE clause to select a subset of the source data rows based on values in the columns. If you need more space, maximize the form.

Example

A relationship is defined with two entities and their relationship type. If you have call data records containing two phone numbers and the first always calls the second, you do not need to use a WHERE clause. Simply assign the first phone number to Entity 1 of the relationship and the second to Entity 2.

But if your data has two phone numbers and a column designating whether the call was outgoing or incoming, you need to import the data differently to preserve the call direction:

Phone 1	Phone 2	Direction
703-356-4700	202-555-1212	OUT
703-356-4700	212-555-3434	IN

We need to swap the phone number columns based on whether it is an outgoing or incoming call. Rather than two different Source Data, use the WHERE clause:

Set the WHERE clause to Direction = 'OUT' to import Phone 1 and 2 assigned to Entity 1 and 2 respectively,
Create another import with Direction = 'IN' to assign Phone 2 to Entity 1, and Phone 1 to Entity 2. After both imports, your relationships have all the calls with the correct direction from Entity 1 to Entity 2.

WHERE Syntax

The WHERE clause uses standard SQL syntax. Here are some hints:

Put the column name in brackets: [Column Name]
For an exact match, use the equal sign = followed by the value.

Data Type	Syntax	Example
Text (string)	Use single quotes before and after the value. Note the value may or may not need to be case sensitive based on your data source. See Case Sensitivity for details.	[Product Name] = 'Sentinel Visualizer'
Number	No quotes are needed and just use the number	[Quantity] = 100
Date	For Excel, Access and Text, put the value between hash tags # with Year-Month-Day syntax (YYYY-MM-DD):	[Order Date] = #2026-08-31#
	For SQL Server, use single quotes around the date.	[Order Date] = '2026-12-31'

Range Syntax

Instead of an exact match with the equal sign, you can specify range searches on numeric and date columns:

Operator	Selection	Objective	Example
<>	Does not equal	Not in Virginia	[State] <> 'VA'
>=	Greater than or equal to	100 and more	[Quantity] >= 100
>	Greater than	More than 100, but not 100	[Quantity] > 100
<	Less than	Less than 100, but not 100	[Quantity] < 100
<=	Less than or equal to	100 or less	[Quantity] <= 100
BETWEEN .. AND	Select values between low and high values. (Inclusive of those values similar to >= AND <=)	Between 500 and 1000	[Quantity] BETWEEN 500 AND 1000

Range Searching on Date Columns

Note that exact matches on Dates are challenging. Date values may contain both date and time. Midnight is considered 0 time; hours after that are fractions of a day (e.g. Noon is 0.5 day).

Date columns that have time may only appear as dates without time based on the display formatting, so you may not realize the data contains time.

When filtering on values for a day, it's best to use syntax like this:

([Start Date] >= #2026-05-21#) AND ([Start Date] < #2026-05-22#)

Using BEWTEEN like this gets both midnights:

[Start Date] BETWEEN #2026-05-21# AND #2026-05-22#

Advanced WHERE Syntax

For more advanced searches:

Operator	Selection	Objective	Example
AND	AND to combine multiple criteria. Use parentheses around each selection.	Outgoing calls after January 1, 2026	([Direction] = 'OUT') AND ([Called Date] >= #2026-01-01#)
OR	OR can be used to select a combination of values	Records from Washington DC and Virginia	([State] = 'DC') OR ([State] = 'VA')
IN	Select values without using multiple OR clauses. The values are comma separated within parenthesis. The same rules apply to the values based on type: Strings are between single quotes Numbers do not need quotes Dates use # (or single quote for SQL Server).	Records from Washington DC, Maryland, and Virginia Values in a numeric field ID equal to 5, 25 or 72	[State] IN ('DC', 'MD', 'VA') [ID] IN (5, 25, 72)
LIKE	LIKE with % to perform wildcard searches	Names starting with A	[First Name] LIKE 'A%'
	Wildcard can also be used to find strings that end with a letter	Names ending in Z	[First Name] LIKE '%Z'
	Multiple wildcards can be used to find text in any part of the field	Fields with ME in it	[Last Name] LIKE '%ME%'
NOT IN	Add NOT to perform the opposite. NOT IN would be rows without those values.	Records outside of Washington DC, Maryland, and Virginia	[State] NOT IN ('DC', 'MD', 'VA')
NOT LIKE	NOT LIKE selects rows that do not match the pattern	Last Names that don't start with Mc	[Last Name] NOT LIKE 'MC%'

Case Sensitivity

Text searches are either Case Sensitive or Case Insensitive. For example, when searching for 'Beth', a case sensitive match only finds values with the exact case 'Beth'. For case insensitive matches, it finds BETH, beth, Beth, bEth, beTH, etc.

Case sensitivity depends on the type of file being imported.

File Type	Case Sensitive	Case Insensitive
Excel
Access
Text
SQL Server	Depends on Collation Order	Usually

SQL Server case sensitivity is based on the database's collation order.

For most English installations, they are Case Insensitive (CI) which is in the collation name: SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AS
Case Sensitive (CS) collations include: SQL_Latin1_General_CP1_CS_AS, Latin1_General_CS_AS, and Latin1_General_BIN2 (binary, strictly case sensitive)

If the source data is Case Sensitive, to perform Case Insensitive searches, convert the column data to all upper or lower case, then perform the search. Note that the case conversion commands are different from SQL Server:

Operation	Excel, Access, Text Commands	SQL Server Commands
Convert to Upper Case	UCASE([First Name]) = 'BETH' UCASE([First Name]) LIKE '%BETH%'	UPPER([First Name]) = 'BETH' UPPER([First Name]) LIKE '%BETH%'
Convert to Lower Case	LCASE([First Name]) = 'beth' LCASE([First Name]) LIKE '%beth%'	LOWER([First Name]) = 'beth' LOWER([First Name]) LIKE '%beth%'

Operation

Excel, Access, Text Commands

SQL Server Commands

Convert to Upper Case

UCASE([First Name]) = 'BETH'
UCASE([First Name]) LIKE '%BETH%'

UPPER([First Name]) = 'BETH'
UPPER([First Name]) LIKE '%BETH%'

Convert to Lower Case

LCASE([First Name]) = 'beth'
LCASE([First Name]) LIKE '%beth%'

LOWER([First Name]) = 'beth'
LOWER([First Name]) LIKE '%beth%'