Excel Cell Ranges using SQL – Named – Unnamed – ODBC – JDBC

Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on email

How to query Excel files using ODBC – JDBC?

Summary :

  • Why use square brackets for SQL against Excel?
  • How to access range of cells from Excel?
  • How to use un-named range of cells?

This topic is covered in various tutorial for InfoCaptor. These following snippets are taken directly from Microsoft website as easy reference

Select Excel Data with Code
Your Excel data may be contained in your workbook in one of the following:

* An entire worksheet.
* A named range of cells on a worksheet.
* An unnamed range of cells on a worksheet.

Specify a Worksheet

To specify a worksheet as your recordsource, use the worksheet name followed by a dollar sign and surrounded by square brackets. For example:

strQuery = “SELECT * FROM [Sheet1$]

You can also delimit the worksheet name with the slanted single quote character (`) found on the keyboard under the tilde (~). For example:

strQuery = “SELECT * FROM `Sheet1$`

Microsoft prefers the square brackets, which are the standing convention for problematic database object names.

If you omit both the dollar sign and the brackets, or just the dollar sign, you receive the following error message:

… the Jet database engine could not find the specified object

If you use the dollar sign but omit the brackets, you will see the following error message:

Syntax error in FROM clause.
If you try to use ordinary single quotes, you receive the following error message:
Syntax error in query. Incomplete query clause.

 

Specify a Named Range

To specify a named range of cells as your recordsource, simply use the defined name. For example:

strQuery = “SELECT * FROM MyRange

Specify an Unnamed Range

To specify an unnamed range of cells as your recordsource, append standard Excel row/column notation to the end of the sheet name in the square brackets. For example:

strQuery = “SELECT * FROM [Sheet1$A1:B10]

A caution about specifying worksheets: The provider assumes that your table of data begins with the upper-most, left-most, non-blank cell on the specified worksheet. In other words, your table of data can begin in Row 3, Column C without a problem. However, you cannot, for example, type a worksheeet title above and to the left of the data in cell A1.

A caution about specifying ranges: When you specify a worksheet as your recordsource, the provider adds new records below existing records in the worksheet as space allows. When you specify a range (named or unnamed), Jet also adds new records below the existing records in the range as space allows. However, if you requery on the original range, the resulting recordset does not include the newly added records outside the range.

The Core Tools

Create dashboard for any Database

Data Visualizer and Dashboard Application
SALE
This is the best dashboard software for its price. One good thing we did was to hire their consulting services to build few dashboard prototypes and provide some quick dashboard training.
- Terry Seal, IL
We evaluated Xcelsius and Qlikview and the cost for organization to implement dashboards was quoted over 10,000 USD. For fraction of the above quoted price, we were able to buy the licenses for the web based dashboard software and get some free training. This is truly a dashboard software for small businesses like us.
IT Manager of a Trucking company, OH