Excel Spreadsheet - Best way to perform an Oracle Query on a cell

Question

Hi there,

I have an Excel Spreadsheeet.

There is a cell containing a concatenated name and surname (don't ask why), for example:

Cell A2 BLOGGSJOE

On this cell, I would like to run the following SQL and output it to cell A3, A4 and A5

SELECT i.id, i.forename, i.surname FROM individual i  WHERE UPPER(REPLACE('" & A2 & "', ' ', '')) =  UPPER(REPLACE(i.surname|| i.forename, ' ', ''))  AND NVL(i.ind_efface, 'N') = 'N'

Any idea how I could perform an oracle query on each cell and return the result?

I have enabled an oracle datasource connection in Excel, just not sure what to do now.

Is this a stupid approach, and can you recommend a better more proficient way?

Thanks muchly! I lack the necessary experience in this type of thing! :-)

EDIT: I am aware that I could just write a simple ruby/php/python/whatever script to loop through the excel spreadsheet (or csv file), and then perform the query etc. but i thought there might be a quick way in excel itself.

Answer 1

Pls check my answer here. Using that code, you can call your Oracle query (recursively or not). I think it would be faster to:

1. first build a list of items to be retrieved (BLOGGSJOE, JOEBAR, JUSTINCASE)
2. then retrieve your Oracle data in a separate list using IN (BLOGGSJOE, JOEBAR, JUSTINCASE) as your criteria
3. finally make a lookup in Excel on that newly downloaded list

Answer 2

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String

Set cn = New ADODB.Connection
cn.ConnectionString = 'Get one from http://www.connectionstrings.com/oracle 
'eg. "Driver={Oracle in OraHome92};Dbq=tns;Uid=uid;Pwd=pwd;"
cn.Open

sql = Your SQL

Set rs = cn.Execute(sql)

for loop or something to paste records

rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

if you format your spreadsheet properly you could assign rs to a Variant and then assign the variant to a range instead of looping through it.

Edit

With your current SQL you would need to loop through each cell and query the db, then save the results to A3:A5. Edit 2, Something like that:

Private Function GetRow(user As String) As Variant
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Dim v As Variant

Set cn = New ADODB.Connection
cn.ConnectionString = "Driver={Oracle in OraHome92};Dbq=tns;Uid=uid;Pwd=pwd;"
cn.Open

sql = "SELECT i.id, i.forename, i.surname FROM individual i  WHERE UPPER(REPLACE('" & user & "', ' ', '')) =  UPPER(REPLACE(i.surname|| i.forename, ' ', ''))  AND NVL(i.ind_efface, 'N') = 'N'"

Set rs = cn.Execute(sql)

v = rs.GetRows()
GetRow = v

rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Function

Sub a()
Dim user As String
For i = 2 To x
    user = ActiveSheet.Cells(i, 1).Value

    GetRow (user)
    ActiveSheet.Cells(i, 2).Value = GetRow(0)
    ActiveSheet.Cells(i, 3).Value = GetRow(1)
    ActiveSheet.Cells(i, 4).Value = GetRow(2)

Next

End Sub

Note that I have used different cells that you wanted and the GetRow is 2D not 1D, but is hard for me to write it up with no access to actual spreadsheet/db