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
Enrich spreadsheet with database information (Read 1803 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Enrich spreadsheet with database information
19.08.09 at 11:57:57
 
Enrich spreadsheet with database information
 
To enrich a spreadsheet with information from a database you can use the the SQLQueryValue function in the SQL*XL software. As an example of how this can be done consider the following example.
 
Example:
Suppose we have a spreadsheet with customer names and order number in column A and B and that we want to know which product was ordered.  
 
This can be easily solved with the SQL*XL software.  
 
First, connect to the database. A database connection is necesary for the SQLQueryValue function to work.
 
In column C we need to enter a function to retrieve the product_id. In C2 enter the function:
=SQLQueryValue("select product_id from orders where order_id=" & B2)
 
Copy the formula and copy it down so it fills all cells in column C for which there are values in column B. SQL*XL will automatically execute the queries and show the product_id that was purchased. You may need to press F9 to force Excel to recalculate.
 
Finally we need to lookup the product description for the product_id just retrieved. We use column D for this. In cell D2 enter the function
=SQLQueryValue("select description from products where product_id=" & C2)
 
Again, copy this function down for all rows that contain values.
 
This small example has shown how to enrich a spreadsheet with information from a database.
Back to top
 
 

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