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
Connect to Excel XLS files (Read 7326 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Connect to Excel XLS files
11.11.06 at 18:39:13
 
Connect to Excel XLS files
 
With SQL*XL you can connect to Excel XLS files. Perhaps a bit strange to do this as SQL*XL works from within Excel. However it can be handy to use SQL to analyse a spreadsheet.
 
To connect to an XLS file the easiest is to use the Connection Wizard or the File Database Connections wizard from the new connections screen.  
 
You will be presented the following options when connecting to Excel XLS:
  • OLE DB Jet (recommended):
    If you can use this driver/provider to connect to the XLS file. It provides good support for the database browser. Named ranges and sheet names occur in the tables list. You can query a sheet with: select * from [sheet1$];
     
    To quickly connect to another spreadsheet select the connection in the connection history list and edit it. In the connection string just enter the new name of the XLS file as the Data Source.  
     
    If you don't have a XLS file yet you can even create the XLS by connecting to a new XLS file. Just type the new name as Data Source. After connecting use the create table statement to create a new "table".
     
    You can insert data into a table by using the insert multiple rows dialog in SQL*XL.
     
  • ODBC DSN-less
    Connecting through ODBC works fine for querying purposes only.  Plus point is that it creates a new XLS file when changing the Data Source to a new non existing file using the technique described above. Creating tables and inserting does not work.
     
  • ODBC DSN
    You can use this technique when you are sure the DSN points to the correct XLS file. As standard there seems to be an Excel Files DSN. Do not use this DSN as it never seems to point to a default database. You can point it to a database in the ODBC Data Source Administrator.

 
Reference:
Excel in Office 2007
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1187784947
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1187784947
Back to top
 
« Last Edit: 22.08.07 at 15:33:41 by Gerrit-Jan Linker »  

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