How to Run a SQL Query on an Excel Table

How to run a SQL query on an Excel table?

There are many fine ways to get this done, which others have already suggestioned. Following along the "get Excel data via SQL track", here are some pointers.

1. Excel has the "Data Connection Wizard" which allows you to import or link from another data source or even within the very same Excel file.

2. As part of Microsoft Office (and OS's) are two providers of interest: the old "Microsoft.Jet.OLEDB", and the latest "Microsoft.ACE.OLEDB". Look for them when setting up a connection (such as with the Data Connection Wizard).

3. Once connected to an Excel workbook, a worksheet or range is the equivalent of a table or view. The table name of a worksheet is the name of the worksheet with a dollar sign ("$") appended to it, and surrounded with square brackets ("[" and "]"); of a range, it is simply the name of the 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.

4. The native SQL will (more or less be) the SQL of Microsoft Access. (In the past, it was called JET SQL; however Access SQL has evolved, and I believe JET is deprecated old tech.)

5. Example, reading a worksheet: SELECT * FROM [Sheet1$]

6. Example, reading a range: SELECT * FROM MyRange

7. Example, reading an unnamed range of cells: SELECT * FROM [Sheet1$A1:B10]

8. There are many many many books and web sites available to help you work through the particulars.

Further notes

By default, it is assumed that the first row of your Excel data source contains column headings that can be used as field names. If this is not the case, you must turn this setting off, or your first row of data "disappears" to be used as field names. This is done by adding the optional HDR= setting to the Extended Properties of the connection string. The default, which does not need to be specified, is HDR=Yes. If you do not have column headings, you need to specify HDR=No; the provider names your fields F1, F2, etc.

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 worksheet 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.

Data types (worth trying) for CREATE TABLE: Short, Long, Single, Double, Currency, DateTime, Bit, Byte, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar, Decimal.

Connecting to "old tech" Excel (files with the xls extention): Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFolder\MyWorkbook.xls;Extended Properties=Excel 8.0;. Use the Excel 5.0 source database type for Microsoft Excel 5.0 and 7.0 (95) workbooks and use the Excel 8.0 source database type for Microsoft Excel 8.0 (97), 9.0 (2000) and 10.0 (2002) workbooks.

Connecting to "latest" Excel (files with the xlsx file extension): Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;"

Treating data as text: IMEX setting treats all data as text. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

(More details at http://www.connectionstrings.com/excel)

More information at http://msdn.microsoft.com/en-US/library/ms141683(v=sql.90).aspx, and at http://support.microsoft.com/kb/316934

Connecting to Excel via ADODB via VBA detailed at http://support.microsoft.com/kb/257819

Microsoft JET 4 details at http://support.microsoft.com/kb/275561

Running SQL queries from inside spreadsheet cells

I've got it done using Power Query. It was way easier than I thought. Special thanks to Jacek Wróbel for providing the idea in the comments.

Run SQL IN queries in Excel

ok, based on the answers and this post, this method enables to set multiple parameters (and unkmown size) in a sql query with excel. It doesn't use IN statement but the result is the same.

The SQL :

SELECT feature1 
FROM database
WHERE ?
LIKE '%,' + feature2 + ',%' ;

Here the ? has to looks like ',a,b,c,'

The Excel:

="'," & TEXJOIN(",",TRUE,A:A) & ",'"

This is not optimised for numerous rows, so an optimised solution is also welcome

Running SQL queries in Excel

Excel is not the worst tool for doing "repetitive data massage" (actually, it is often my first choice for such tasks). The key point is that you learn using formulas and VBA programming. Using VBA, you can automate (ok, almost) everything in Excel which you can do manually otherwise. A query like yours above will need a few lines more in VBA, written in a procedural style, but you can immediatly store the result in an aggregate sheet, format it nicely, add some code for loading your dump into an Excel sheet beforehand and so automate the whole process, not just the query part.

Performing SQL queries on an Excel Table within a Workbook with VBA Macro

One thing you may be able to do is get the address of the dynamic named range, and use that as the input in your SQL string. Something like:

Sheets("shtName").range("namedRangeName").Address

Which will spit out an address string, something like $A$1:$A$8

Edit:

As I said in my comment below, you can dynamically get the full address (including sheet name) and either use it directly or parse the sheet name for later use:

ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal

Which results in a string like =Sheet1!$C$1:$C$4. So for your code example above, your SQL statement could be

strRangeAddress = Mid(ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal,2)

strSQL = "SELECT * FROM [strRangeAddress]"

How to use a sql query and get data from one Excel sheet to another in VBA?

Below is a simple connection and query to another workbook.

Sub simple_Query()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    dbpath = "your path here"
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    strSQL = "SELECT * FROM [Sheet1$] "
    Set vNewWB = Workbooks.Add 'or .CopyFromRecordset rs to open workbook
    connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" & dbpath & ";Extended Properties=""Excel 12.0; HDR=YES; IMEX=1""; Mode=Read;"
    cn.Open connstr
    Set rs = cn.Execute(CommandText:=strSQL)
    vNewWB.Sheets(1).Range("A2").CopyFromRecordset rs
    For intcolIndex = 0 To rs.Fields.Count - 1
        Range("A1").Offset(O, intcolIndex).Value = rs.Fields(intcolIndex).Name
    Next
    rs.Close
    cn.Close
    Set cn = Nothing
    Set rs = Nothing
End Sub

Pass parameter from Excel to SQL in PowerQuery

You can reference a table on a sheet from Power Query and integrate values from that table into your other queries. Eg if ParameterTable is a single-row table on some worksheet with a column called "StartDate", something like

let
    theDate = Date.From( Record.Field(Table.First(ParameterTable),"StartDate") ),
    Source = Sql.Databases("localhost"),
    AdventureWorksDW2017 = Source{[Name="AdventureWorksDW2017"]}[Data],
    dbo_DimDate = AdventureWorksDW2017{[Schema="dbo",Item="DimDate"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimDate, each [FullDateAlternateKey] = theDate )    
in
    #"Filtered Rows"

for M query folding, or

let
    theDate = Date.From( Record.Field(Table.First(ParameterTable),"StartDate") ),
    sql = "
      select *
      from dimDate
      where FullDateAlternateKey = '" & Text.From(theDate) & "'
    ",
    Source = Sql.Database("localhost", "adventureworksdw2017", [Query=sql])
in
    Source

for dynamic SQL.

---

Related Topics