Excel function to make SQL-like queries on worksheet data?
You can use Get External Data
(despite its name), located in the 'Data' tab of Excel 2010, to set up a connection
in a workbook to query data from itself. Use From Other Sources
From Microsoft Query
to connect to Excel
Once set up you can use VBA
to manipulate the connection
to, among other thing, view and modify the SQL command that drives the query. This query does reference the in memory workbook, so doesn't require a save to refresh the latest data.
Here's a quick Sub
to demonstrate accessing the connection objects
Sub DemoConnection()
Dim c As Connections
Dim wb As Workbook
Dim i As Long
Dim strSQL As String
Set wb = ActiveWorkbook
Set c = wb.Connections
For i = 1 To c.Count
' Reresh the data
c(i).Refresh
' view the SQL query
strSQL = c(i).ODBCConnection.CommandText
MsgBox strSQL
Next
End Sub
Excel Equivalent of SQL query SELECT A.one, B.two FROM A INNER JOIN B ON A.three = B.three
Assuming your sheets are as follows:
Sheet1
Sheet2
In Cell A2
of Sheet3
enter the formula
=IFERROR(INDEX(Sheet1!$A$2:$A$8,MATCH(SMALL(IF(COUNTIF(Sheet1!$C$2:$C$10,Sheet2!$C$2:$C$10),Sheet2!$C$2:$C$10),ROW(1:1)),Sheet1!$C$2:$C$8,0)),"")
and in Cell B2
of Sheet3
enter the following formula
=IFERROR(INDEX(Sheet2!$B$2:$B$8,MATCH(SMALL(IF(COUNTIF(Sheet1!$C$2:$C$10,Sheet2!$C$2:$C$10),Sheet2!$C$2:$C$10),ROW(1:1)),Sheet1!$C$2:$C$8,0)),"")
Both the above formula are array formula so commit it by pressing Ctrl+Shift+Enter. Drag/Copy down as required. See image for reference.
-----------------------------------------------------------------------------------------------------------------------
If you also want to display third column of first two sheets in Sheet3
(which is ID
in my sample sheet) then enter following formula in Cell A2
=IFERROR(SMALL(IF(COUNTIF(Sheet1!$C$2:$C$10,Sheet2!$C$2:$C$10),Sheet2!$C$2:$C$10),ROW(1:1)),"")
This is also an array formula. In Cell B2
enter
=IFERROR(INDEX(Sheet1!$A$2:$A$8,MATCH(A2,Sheet1!$C$2:$C$8,0)),"")
And in Cell C2
enter
=IFERROR(INDEX(Sheet2!$B$2:$B$8,MATCH(A2,Sheet2!$C$2:$C$8,0)),"")
Drag/Copy down as required. See image below.
Got this from @ScottCraner's answer here.
There's another way of achieving this without using using formula and VBA. See if this helps.
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.
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.
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).
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.
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.)
Example, reading a worksheet:
SELECT * FROM [Sheet1$]
Example, reading a range:
SELECT * FROM MyRange
Example, reading an unnamed range of cells:
SELECT * FROM [Sheet1$A1:B10]
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
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
Related Topics
Is There Common Street Addresses Database Design for All Addresses of the World
SQL Multiple Columns in In Clause
Reference Alias in Where Clause
Select Random Row(S) in SQLite
Rbar VS. Set Based Programming for SQL
SQL to Entity Framework Count Group-By
What's the Difference Between "Where" Clause and "On" Clause When Table Left Join
How to Scale Pivoting in Bigquery
How to Select All the Columns from a Table, Plus Additional Columns Like Rownum
How to Compare SQLite Timestamp Values
How to Set Auto Increment Primary Key in Postgresql
Why Are Relational Set-Based Queries Better Than Cursors
Postgres Column "X" Does Not Exist
What Does Sp_Reset_Connection Do
Concat Field Value to String in SQL Server
Cannot Use Update with Output Clause When a Trigger Is on the Table
"In" Clause Limitation in SQL Server
Create Custom Function for Date Difference Excluding Weekends and Holidays in Oracle SQL