How to query an Excel file in ODBC

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.