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
Using SQL to analyse Excel files (Read 2526 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Using SQL to analyse Excel files
11.11.06 at 22:32:24
 
Using SQL to analyse Excel files
 
Sometimes SQL is such a convenient language and tool to do analysis you wished you had it when analysing your Excel spreadsheets. I often find myself analysing spreadsheets and messing about with the HLOOKUP and VLOOKUP functions to create some cross sheet checking. A simple SQL join with some conditions would be soo nice to have...
 
Did you know you CAN use SQL to do this?
 
First, prepare your XLS file. If you have one "table" per sheet and when you start the table at A1 you can just use the sheet name as table name. Otherwise select the table on the spreadsheet and define a name for the range of cells. Use that name as the table name.
 
Next, connect to your XLS file. See:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1163266753
 
To demo this I have queries the SCOTT.EMP table into sheet1. I renamed sheet 1 to EMP.  
Then I queried the SCOTT.DEPT table into sheet2. I renamed it to DEPT.
I saved the XLS file as scott.xls.
 
I connected through OLE DB (Jet) to this XLS file.
Now I can query this file:
select * from [EMP$];
select * from [DEPT$];

 
To do the SQL joins I would like to demonstrate I am executing the following query:
select ename, loc
from [EMP$] e, [DEPT$] d  
where e.deptno = d.deptno

 
This is a very nice technique to be able to combine data from different sheets. Unfortunately you cannot use the SQL count function. However that can quickly overcome with some further analysis on the resulting spreadsheet.
Back to top
 
« Last Edit: 11.11.06 at 22:40:06 by Gerrit-Jan Linker »  

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