Introduction
This article will provide some examples of queries for the ODBC Excel driver.
Steps
First, refer to our article on how to connect to one or more Excel files using ODBC PowerPack.
In the preview tab, you can use the following example queries based on your specific use case needs.
SELECT a Table
To retrieve the entire table, use the following command:
SELECT *
FROM "Team Sheet"
| Date | Team name | Results | Division | Status |
|---|---|---|---|---|
| 26/08/2023 | Avanti Soccer Academy Avanti Soccer | 3 | 12U Pre-Elite | Scheduled |
| 26/08/2023 | Texas Lightning Soccer Club | 2 | 13U Elite | Scheduled |
| 26/08/2023 | Renegades SC | 1 | 15U Division 1 | Not Scheduled |
| 26/08/2023 | BVB International Academy Texas | 3 | 11U Pre-Elite | Scheduled |
| 26/08/2023 | Solar SC | 1 | 14U Elite | Not Scheduled |
| 26/08/2023 | Steel United Texas Steel United | 1 | 16U Division 1 | Scheduled |
| 26/08/2023 | Americas Futbol Club Americas | 2 | 15U Division 2 | Scheduled |
| 26/08/2023 | Revolution Premier SC Revolution | 1 | 12U Pre-Elite | Not Scheduled |
| 26/08/2023 | Forms Academy | 3 | 11U Pre-Elite | Scheduled |
| 26/08/2023 | NTX Celtic | 1 | 16U Division 1 | Scheduled |
SELECT with Range
To start from a specific cell, use:
SELECT *
FROM "Team Sheet"
WITH (RangeStartCell='C1')
SELECT with StartRange and EndRange
To get a specific range from a table, use:
SELECT from DataTable without Columns
To remove the columns from the table, use:
SELECT *
FROM "Team Sheet"
WITH (RangeStartCell='B8', Headerless=1)
SELECT with Skip Rows from the Top
To skip N rows from the beginning, use:
SELECT *
FROM "Team Sheet"
WITH (SkipRows=8)
INSERT
To add a new row to the sheet, use:
INSERT INTO "Team Sheet" (Co100, Co101, Co102, Co103, Co104)
VALUES ('27/08/2023', 'California team', 2, '15U Division 2', 'Not Scheduled')
UPDATE
To update an existing row, use:
UPDATE "Team Sheet"
SET Co1='27/03/2023', Co2='New Team', Co3=4, Co4='15U Division 2', Co5='Not Scheduled'
WITH (RangeStartCell='A3')
WHERE
To use a conditional query for specific rows, use:
SELECT *
FROM "Team Sheet"
WHERE (Division='15U Division 2' AND (Results > 2 OR Status = 'Scheduled'))
WHEREIN
To check if a value is in a column, use:
SELECT *
FROM "Team Sheet"
WHERE Results IN (4, 1)
Conclusion
This format should make it easier for readers to follow and understand the different query examples for the ODBC Excel driver.