How do I check a colleague's spreadsheet list against the database?

Applies to BroadSQL 5.5.0 · reviewed October 18, 2026

What you'll accomplish

A colleague keeps a workbook of codes to review: the accounts of a project, the products of a campaign, the countries of a study. The list changes from week to week, and two questions come back each time: which rows of the database match the list, and which codes of the list do not exist in the database at all, so that the colleague can correct them.

You will read the column straight from the workbook in your query, by sheet and column name, to list the matching rows. Then you will find the codes that match nothing, which an IN list alone cannot tell you: by loading the list into a work table, the way that works on every database, and on H2 by turning the list into a table inside the query.

Short answer

Matching rows: WHERE CODE IN <@excel:c:\temp\review.xlsx:Countries:COUNTRY_CODE>. Codes that match nothing: LOAD the list into a work table, then LEFT JOIN it with the reference table and keep the rows without a match.

Before you start

You need an .xlsx workbook with a header row. To follow the example without one, let BroadSQL write it: connected to WORLD, this command creates review.xlsx in the export folder (the DefaultFolder setting, c:\temp\ by default on Windows), with a sheet Countries holding five codes in a COUNTRY_CODE column. It writes a file only:

DUMP (SELECT 'AUS' AS COUNTRY_CODE UNION ALL SELECT 'NZL' UNION ALL SELECT 'ROM' UNION ALL SELECT 'FJI' UNION ALL SELECT 'XYZ') TO review.Countries AS XLSX;

The message gives the full path of the file, for example c:\temp\review.xlsx. Two of the codes match no country: XYZ, a typing error, and ROM, the old code of Romania, which WORLD's COUNTRY table holds as ROU.

1. List the matching rows

Name the file, the sheet Countries and the column COUNTRY_CODE after <@excel:, separated by colons. BroadSQL reads the column each time the query runs, quotes each value and builds the list:

SELECT CODE, TRIM(NAME) AS NAME FROM COUNTRY WHERE CODE IN <@excel:c:\temp\review.xlsx:Countries:COUNTRY_CODE> ORDER BY CODE;

Five codes went in, three rows came out:

WORLD> SELECT CODE, TRIM(NAME) AS NAME FROM COUNTRY WHERE CODE IN <@excel:c:\temp\review.xlsx:Countries:COUNTRY_CODE> ORDER BY CODE;
|----|----------------------------------------------------|
|CODE|NAME                                                |
|----|----------------------------------------------------|
|AUS |Australia                                           |
|FJI |Fiji Islands                                        |
|NZL |New Zealand                                         |
|----|----------------------------------------------------|

3 rows fetched in 9 ms.

The sheet and the column are found by name, not by position, so a reordered sheet still gives the right values. When the colleague changes the workbook, save it and run the same query again.

2. Why the missing codes do not show

Two codes are missing, but the result cannot say which: an IN list filters the rows of a table, and a value that has no row has nothing to appear in. To list the values of the list that match nothing, the list must itself be a table, joined with the reference table.

3. Load the list into a work table

LOAD inserts a CSV file into an existing table, so create a small work table first. This changes the database you are connected to: on the practice set, where WORLD is the production database, run steps 3 to 5 on WORLD-QA, or BroadSQL asks for a confirmation before each change:

CREATE TABLE REVIEW_CODE (COUNTRY_CODE VARCHAR(3) NOT NULL);

In Excel, save the sheet Countries as "CSV UTF-8" under the name review.csv, in the export folder. Opened in a text editor, the file holds the header and the five codes:

COUNTRY_CODE
AUS
NZL
ROM
FJI
XYZ

Check it against the table without writing, then insert it. EXECUTE authorizes the insert without a question:

LOAD REVIEW_CODE review.csv PREVIEW;
LOAD REVIEW_CODE review.csv EXECUTE;

The preview reports 5 valid rows and 0 rejected; the second command inserts them in one transaction.

4. List the codes that match nothing

Join the list with COUNTRY and keep the codes for which no country was found:

SELECT R.COUNTRY_CODE FROM REVIEW_CODE R LEFT JOIN COUNTRY C ON C.CODE = R.COUNTRY_CODE WHERE C.CODE IS NULL ORDER BY R.COUNTRY_CODE;

It returns ROM and XYZ: the two codes to send back to the colleague, XYZ as a typing error and ROM as the old code of ROU.

5. On H2: the list as a table, without loading it

H2 can turn a list of values into a table inside a query, with TABLE(<column> <type> = (<values>)). A list source becomes exactly such a parenthesized list, so the workbook column can go there directly, without a work table:

SELECT T.COUNTRY_CODE FROM TABLE(COUNTRY_CODE VARCHAR = <@excel:c:\temp\review.xlsx:Countries:COUNTRY_CODE>) T WHERE T.COUNTRY_CODE NOT IN (SELECT CODE FROM COUNTRY) ORDER BY T.COUNTRY_CODE;

It returns the same two codes, read from the current workbook at each run. TABLE(...) is H2 syntax: on other databases, use the work table of steps 3 and 4, or that product's own way of turning a list into rows.

6. Report back

After either query, write the result for the colleague, or copy it into a reply:

DUMP / TO review_result AS XLSX;

COPY RESULT; copies the last result to the clipboard as tab separated text instead.

Variations

• A CSV file instead of a workbook: IN <@csv:c:\temp\review.csv:COUNTRY_CODE> reads one named column of a CSV file (UTF-8).
• The same check every week: keep the workbook at the same place and save the query as a Script (see Save a query with parameters and run it by name).
• The list arrives by email: copy it and write IN <@clipboard> (see Use a list of values from the clipboard).

Watch out

• A relative path is resolved against the folder BroadSQL was started from, not the export folder or the Script's folder. Use an absolute path when in doubt.
• Only .xlsx workbooks are read; the old .xls format is not supported.
• Numeric cells are read as plain numbers, date cells as ISO dates, formulas by their last computed value. Blank cells are skipped.
• Values are matched as they are written: aus matches AUS only if the column compares without regard to case.
• Close the workbook in Excel before writing it again with DUMP: a file held open by Excel cannot be replaced.

Clean up

Remove the work table from the connection where you created it, then delete review.xlsx, review.csv and review_result.xlsx from the export folder:

DROP TABLE REVIEW_CODE;

Related

• Patterns: <@excel:...>
• Load a CSV file into a table, with a preview first
• Find orphan rows, duplicates and missing values

Commands used

• DUMP
• LOAD
• COPY RESULT

Found something wrong or unclear on this page? Tell us, with the page address.