Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Query the database on a list of id's in Excel (Read 3589 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Query the database on a list of id's in Excel
05.04.06 at 11:45:44
 
Pramod from the UK asked the following question.
 
Question:
I have an excel sheet with a list of employee_id in column :A
All I want is Excel to query my Oracle database and to retrieve the employee_name for all the employee_id's in column :A and populate column :B in the excel sheet.
 
I though the below query would work. But it doesn't. Any help appreciated.  
SELECT emp_name FROM emp WHERE emp_id = :A TO :B;
 
Answer:
What you require is perfectly possible however not with the syntax you have tried. There are a few ways to do this:

  • Use Excel formulas to display the value
    Use the insert function SQL*XL menu item to put the SQLQueryValue function in column B to execute a query to retrieve the customer name from the database. The formula you need to create will look like this:
    =sqlxl.xls!SQLQueryValue("select ename from emp where empno = " & A2)
     
    The result of this query will be shown in the cell. In my test A2 contains employee id 7369 and the cell displays Smith as a result.
     
    You can copy this formula to all the cells where you need to retrieve the employee name. You may need to hit F9 to force Excel to calculate. It will give formulas like this:
    B2: =SQLQueryValue("select ename from emp where empno = " & A2)
    B3: =SQLQueryValue("select ename from emp where empno = " & A3)
    B4: =SQLQueryValue("select ename from emp where empno = " & A4)
    B5: =SQLQueryValue("select ename from emp where empno = " & A5)
     
  • Make select statements
    Use simpel Excel formulas to make select statements in Excel. You need to use the concatenate Excel function to glue text strings together. The formula you need is:
    =CONCATENATE("select empno, ename from emp where empno = ", A2, ";" )
     
    Excel will display:  
    select empno, ename from emp where empno = 7369;
     
    Now, you can copy this cell down to all the cells in column A for which you want to do a lookup:
    select empno, ename from emp where empno = 7369;
    select empno, ename from emp where empno = 7499;
    select empno, ename from emp where empno = 7521;
    select empno, ename from emp where empno = 7566;
    select empno, ename from emp where empno = 7654;
    select empno, ename from emp where empno = 7698;
    select empno, ename from emp where empno = 7782;

     
    You can now select all the cells that contain the SQL and open the SQL*XL SQL dialog. Specify Worksheet as SQL source and SQL*XL will use the SQL you just made on the worksheet.
     
  • Make select statements to output to a specified target cell
    You can use the same technique as above but use the To clause to set the cell where the results should be displayed:
    select ename from emp where empno = 1234 to B2;
     
    The difficulty is to make a cell formula that displays the address B2. You can use the Excel address function to make a cell address. Address(1,2) gives B1 for example (row 1, column 2). With the Excel Row() function you can retrieve the current row number. I have my employee id's in column A and I want the enames to be displayed in column B. The SQL I save in column C. I have used this formula:  
    =CONCATENATE("select ename from emp where empno = ", A2, " to ", ADDRESS(ROW(),2) , ";" )
     
    It gives the following result:
    select ename from emp where empno = 7369 to $B$2;
     
    Copying this formula to other cells in column C gives:
    select ename from emp where empno = 7369 to $B$2;
    select ename from emp where empno = 7499 to $B$3;
    select ename from emp where empno = 7521 to $B$4;
    select ename from emp where empno = 7566 to $B$5;
    select ename from emp where empno = 7654 to $B$6;
    select ename from emp where empno = 7698 to $B$7;

     
    Again, you need to select all the cells that contain the SQL and open SQL*XL's SQL dialog. Specify to not display column headers and I suggest you also switch off the option to put the SQL in a note. Then hit the button Ok for all so SQL*XL does not ask again for the options.
     

 
Back to top
 
« Last Edit: 17.11.06 at 11:55:30 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1