Linker IT Software
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
Noobie... (Read 3049 times)
YaBB Newbies


Posts: 13
19.11.07 at 17:28:10
Ok Ive never used SQL*XL in its real Excel environment, and I'm struggling with something simple. I have 10,000 rows of Excel data - and 1 field is the primary key to a database. I need to use that key (for each row) and retrieve additional information into adjacent columns. I can do this for 1 row, e.g. in H3 I insert the SQL:-
"select home_phone from address where staff_id = :A3" - but how do I replicate that down 10,000 rows and get it do work? (When I copy - all the SQL syntax refers to cell A3 - clearly not what I want)
Back to top
  IP Logged
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Re: Noobie...
Reply #1 - 19.11.07 at 21:01:50
The easiest is to use the SQLQueryValue function. It will return the value of the query in the cell. Note that if you want to retrieve 2 values per query you can make an array formula. See pointers below for more information about this.
In your case you probably have your PK values in column A. Suppose you want to add the home phone number in column D.
Enter the following formula in D3 (assuming the first 2 rows are titles):
=SQLQueryValue("select home_phone from address where staff_id =" & A3)
If it is correct the phone number is displayed. Now simply copy this formula down and you will notice that A3 is replaced by A4, etc. Note that 10,000 rows will mean that you run 10,000 queries. That may take a little time. Excel will not rerun the Query unless the value in column A changes. I suggest you paste the formula in blocks of 1000 and save the file in between. Afer you retrieved all the phone numbers I suggest you copy column D and paste it as values to prevent all the functions from rerunning.
See also:
SQLQueryValue as Array formula
Query the database on a list of id's in Excel
Back to top
« Last Edit: 19.11.07 at 21:07:47 by Gerrit-Jan Linker »  

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