Querying a database for values in each row

  • Thread starter Thread starter Stephen Goldfinger
  • Start date Start date
S

Stephen Goldfinger

Howdy.

I have a worksheet where each row has several columns with
values. I want to take these column values and look up a
value from an external database. I also don't want to
create a query for each row (since there are many
thousands of rows). I know how to get a query to return a
value for a single row, but is there a way to get a query
to return a value for each row and place the result in a
column at the end of the row whose values are being
matched.

Thanks in advance for your help.

Stephen
 
In database terms, you want to LEFT JOIN all the rows in your Excel
range (table on the left) to those in the your external database
(table on the right) or a row of nulls if there is no match. The join
would look something like this:

SELECT T1.Col1, T1.Col2, T1.Col3, T2.LookupCol As NewCol
FROM MyExcelData T1 LEFT JOIN
MyExternalData T2 ON T1.Col1=T2.Col1
AND T1.Col2=T2.Col2
AND T1.Col3=T2.Col3

I've never known a RDBMS allow you to JOIN tables from different data
sources on the fly i.e. without previously linking servers (SQL
Server) or using linked tables (MS Access). You need both tables in
the same 'place'.

Also, in database terms, you are talking about appending a new column
to an existing table, which is not something normally done on the fly
with a query!

So it makes me wonder: is this a one off or something you will be
doing regularly in an Excel application?

If it's a one off, you are probably best off importing or linking the
Excel data within the RDBMS, create a new table locally using a LEFT
JOIN, then updating the original Excel source. If it was a regular
thing, it would be difficult to do all this in code from Excel if you
wanted to repeat regularly.

Post back with some more details e.g. the external database (MS
Access, SQL Server, another Excel workbook etc), what your data looks
like (Excel and external), how often you plan to do this, whether to
run from code, etc.
 
Thanks for taking the time to reply. Your assessment is right on the
mark. Here is an example (I hope they line up well.)

Lookup Lookup
Column 1 Column 2 Result
-------- -------- ------
A1 A2 R1
B1 B2 R2
C3 C3 R3

Explanation: Given the combination of values (A1 and A2), I can
determine the SINGLE result value, R1. Each combination of Column 1 and
Column 2 will return a singleton value.

Given what I know of Excel, I can write a query that takes the values of
the first row (i.e. A1 & A2) to then go get the resulting value R1. I
can then write another query for (B1 & B2) to get me R2. I don't want
to have to write multiple queries. I want one query (or a function)
that can take the Column 1 and Column 2 values for each row and get me
the Result value for that particular row.

I hope this clarifies the question.

Thanks.
 
Stephen

Here's how I'd do it: First create a module level variable for an ADODB
connection object. Set a reference to Microsoft ActiveX Data Object x.x
under Tools - References. Next, create a sub that opens the ADODB
connection. Create a sub that closes the ADODB connection which you should
call from your Workbook_BeforeClose event. Finally, create function that
creates a recordset and pull the data out of there. Here's an example.

Dim Conn As ADODB.Connection

Sub EstablishConnection()

Dim MyConn As String

Set Conn = New ADODB.Connection

MyConn = "DSN=MS Access 97 Database;DBQ=C:\Dick\db1.mdb;"
MyConn = MyConn & "DefaultDir=C:\Dick;DriverId=281;"
MyConn = MyConn & "FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

Conn.Open MyConn

End Sub

Function GetValue(Rng1 As Range, Rng2 As Range) As Variant

Dim MySql As String
Dim rs As ADODB.Recordset

If Conn Is Nothing Then
EstablishConnection
End If

MySql = "SELECT `Table 1`.number, `Table 1`.name, `Table 1`.TheDate "
MySql = MySql & "FROM `C:\Dick\db1`.`Table 1` `Table 1` "
MySql = MySql & "WHERE (`Table 1`.number='" & Rng1.Value & "') AND "
MySql = MySql & "(`Table 1`.name='" & Rng2.Value & "')"

Set rs = Conn.Execute(MySql)
rs.MoveFirst
GetValue = rs.Fields(2).Value

End Function

Sub TermConnection()

Conn.Close

Set Conn = Nothing

End Sub


You could create an destroy the ADODB connection inside of the function, but
that will increase your processing time and may slow you down. I think it's
better to open the connection once and close it once and leave it open while
the workbook is open.

You call the function like

=GetValue(A1,B1)

and the recordset returned inside the function should contain the proper
value, because as you say, there is only one combination of the first two
fields in the database.

Give it a try and let me know if you need any clarification.
 
Thanks for the great answer. Generally, it looks like it will fit the
bill. Of course, there's one hitch. I am trying to access an Oracle 9i
database and I don't know how to change the MyConn string appropriately
for Oracle. I don't know where to look for help documentation otherwise
I wouldn't waste any more of your time.

Any ideas of either where to look or how to change it?

Thanks in advance.
 
Stephen

Here's my method for getting the connection string and the sql:

Set up an External Data Table manually in Excel (Data - Get External Data -
New Database Query). Set it up as close as you can to how you'll want it
when you do it through code. Then go to the Immediate Window and type

?Sheet1.QueryTables(1).Connection
?Sheet1.QueryTables(1).CommandText

to give you the proper syntax. Remove the DSN from the beginning of the
connection string if it's there.
 
I understand your problem. To recap, the solution is to join your
Excel table to your external database table then update your Excel
table with the generated recordset. But first you will have to get
both tables in the same place. I can elaborate but first tell me:

1) the external database (MS Access, SQL Server, another Excel
workbook etc)
2) how often you plan to do this e.g. is it a one-off exercise or a
regular application function.
 
Using Dick's function as a UDF in a formula in R1 and copying down to
R65536 would result in 65536 queries, 65536 trips across the network,
etc (and the connection is never closed).

The solution obviously works for you but if you get performace issues
(or complaints from your network admin or DBA!) bear in mind it is
possible to do this in one hit: *one* query to create a recorset for
all rows, then use update the spreadsheet using CopyFromRecordset or
some other method.

--
 
Jamie

Ah, the joys of the being THE network admin (I never complain to
myself.).<g>

Would you mind sketching out how you would set that up? I'd be interested
in learning a better way. Thanks.
 
Happy to oblige, Dick.

To join the Oracle and Excel tables they need to be in the same
location. It would probably be better to do the work in Oracle (i.e.
import data from Excel into Oracle) but I'll do the work in Excel.
Open a connection using Jet OLEDB and the Excel workbook as the data
source (I won't try and guess an Oracle connection string so I'll
infer a DSN). Execute this SQL statement (no recordset generated)
against the Excel connection:

SELECT KeyCol1, KeyCol2, Value
INTO NewTempTable
FROM
[ODBC;DSN=MyOracleDB;].MyOracleTable

Assume the Excel table is on Sheet1 and the Oracle table is now in the
same workbook on a new sheet called NewTempTable. For ease of
reference, use this data:

Excel table: Sheet1:
[A1:D1]=array("Name","KeyCol1","KeyCol2","Value")
[A2:C2]=array("Norarules",1,1)
[A3:C3]=array("Livehulas",1,2)
[A4:C4]=array("Regisaver",1,3)
[A5:C5]=array("Hevitoxic",1,4)
[A6:C6]=array("Domatican",2,1)
[A7:C7]=array("Pipesagap",2,2)
[A8:C8]=array("Luxasonic",2,3)
[A9:C9]=array("Katewudes",2,4)

Oracle table in now Excel: NewTempTable:
[A1:C1]=array("KeyCol1","KeyCol2","Value")
[A2:C2]=array(1,1,18)
[A3:C3]=array(1,2,24)
[A4:C4]=array(1,3,33)
[A5:C5]=array(1,4,52)
[A6:C6]=array(1,5,59)
[A7:C7]=array(1,6,60)
[A8:C8]=array(1,7,63)
[A9:C9]=array(1,8,88)

To generate the required data, create a recordset by executing this
SQL statement against the Excel connection:

SELECT T1.Name,T1.KeyCol1,T1.KeyCol2,T2.Value
FROM [Sheet1$] T1 LEFT JOIN [NewTempTable$] T2
ON T1.KeyCol1=T2.KeyCol1 AND T1.KeyCol2=T2.KeyCol2
ORDER BY T1.KeyCol1,T1.KeyCol2

To make the rowset visible, try this:

SELECT T1.Name,T1.KeyCol1,T1.KeyCol2,T2.Value
INTO GeneratedRowset
FROM [Sheet1$] T1 LEFT JOIN [NewTempTable$] T2
ON T1.KeyCol1=T2.KeyCol1 AND T1.KeyCol2=T2.KeyCol2
ORDER BY T1.KeyCol1,T1.KeyCol2

and view the new sheet called GeneratedRowset.

The left join means all the Excel rows are returned and Value returned
from the lookup table where one exists otherwise it is null. Sheet1
can now be updated as appropriate. In my example, I can generate the
recordset in the same order as the original table, therefore I could
use CopyFromRecordset to replace the whole table.

Beyond this it gets hard to generalize. The Oracle table may be too
large to fit simply into Excel or that network guy (<g>) may not be
happy with whole tables flying around the wires. If it's not crucial
to trigger the process from Excel, it may be simpler to link the
tables in MS Access and do the join from there.

--
 
Interesting, thanks.

Can I use the recordset from Oracle in a left join without putting it on a
sheet, thereby eliminating the need to create a new sheet?

If I do create a new sheet with the Oracle rs, why not use array formulae to
pull the information over - too slow? I'm going to test this one myself, but
if you know the answer, I'll take it.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

onedaywhen said:
Happy to oblige, Dick.

To join the Oracle and Excel tables they need to be in the same
location. It would probably be better to do the work in Oracle (i.e.
import data from Excel into Oracle) but I'll do the work in Excel.
Open a connection using Jet OLEDB and the Excel workbook as the data
source (I won't try and guess an Oracle connection string so I'll
infer a DSN). Execute this SQL statement (no recordset generated)
against the Excel connection:

SELECT KeyCol1, KeyCol2, Value
INTO NewTempTable
FROM
[ODBC;DSN=MyOracleDB;].MyOracleTable

Assume the Excel table is on Sheet1 and the Oracle table is now in the
same workbook on a new sheet called NewTempTable. For ease of
reference, use this data:

Excel table: Sheet1:
[A1:D1]=array("Name","KeyCol1","KeyCol2","Value")
[A2:C2]=array("Norarules",1,1)
[A3:C3]=array("Livehulas",1,2)
[A4:C4]=array("Regisaver",1,3)
[A5:C5]=array("Hevitoxic",1,4)
[A6:C6]=array("Domatican",2,1)
[A7:C7]=array("Pipesagap",2,2)
[A8:C8]=array("Luxasonic",2,3)
[A9:C9]=array("Katewudes",2,4)

Oracle table in now Excel: NewTempTable:
[A1:C1]=array("KeyCol1","KeyCol2","Value")
[A2:C2]=array(1,1,18)
[A3:C3]=array(1,2,24)
[A4:C4]=array(1,3,33)
[A5:C5]=array(1,4,52)
[A6:C6]=array(1,5,59)
[A7:C7]=array(1,6,60)
[A8:C8]=array(1,7,63)
[A9:C9]=array(1,8,88)

To generate the required data, create a recordset by executing this
SQL statement against the Excel connection:

SELECT T1.Name,T1.KeyCol1,T1.KeyCol2,T2.Value
FROM [Sheet1$] T1 LEFT JOIN [NewTempTable$] T2
ON T1.KeyCol1=T2.KeyCol1 AND T1.KeyCol2=T2.KeyCol2
ORDER BY T1.KeyCol1,T1.KeyCol2

To make the rowset visible, try this:

SELECT T1.Name,T1.KeyCol1,T1.KeyCol2,T2.Value
INTO GeneratedRowset
FROM [Sheet1$] T1 LEFT JOIN [NewTempTable$] T2
ON T1.KeyCol1=T2.KeyCol1 AND T1.KeyCol2=T2.KeyCol2
ORDER BY T1.KeyCol1,T1.KeyCol2

and view the new sheet called GeneratedRowset.

The left join means all the Excel rows are returned and Value returned
from the lookup table where one exists otherwise it is null. Sheet1
can now be updated as appropriate. In my example, I can generate the
recordset in the same order as the original table, therefore I could
use CopyFromRecordset to replace the whole table.

Beyond this it gets hard to generalize. The Oracle table may be too
large to fit simply into Excel or that network guy (<g>) may not be
happy with whole tables flying around the wires. If it's not crucial
to trigger the process from Excel, it may be simpler to link the
tables in MS Access and do the join from there.

--

"Dick Kusleika" <[email protected]> wrote in message
Jamie

Ah, the joys of the being THE network admin (I never complain to
myself.).<g>

Would you mind sketching out how you would set that up? I'd be interested
in learning a better way. Thanks.


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.
Click to expand...
Click to expand...
 
Thanks for all the ideas.

I ended up solving this with a simpler, inelegant solution.

I created a worksheet with a query which returns a copy of the table
from the Oracle database.

I then added a lookup function to the original worksheet to match the
values returned from Oracle. In order to do the match this way I had to
concatenate several columns that would normally have appeared in a WHERE
clause. That allowed me to use Excel's lookup functions which match a
single value in a table against another single value.

I will still investigate your solutions because they are more useful for
future use.

Thanks again for all the ideas.
 
Dick,
You cannot query a recordset as if it were a table, so no, you have to
do the join in either Oracle or Excel (or use ODBC pass through e.g.
linked tables in MS Access). And to get the Oracel table into Excel
you should be able to use Jet's SELECT..INTO syntax to import the data
direct (not using a recorset = faster) but I haven't tested this for
Oracle.
 
ODW

Too bad. Thanks for the info. Another scenario that I want to test is
bringing the recordset over one time at Workbook_Open, then just looping
through it as the function is called. That way the function isn't creating
network traffic. I'll report back if I ever get around to doing any of this
testing.
 
Back
Top