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
Using SQL to analyse Excel files (Read 2488 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:
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