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
Query SQL table in Excel 2007 (Read 5565 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Query SQL table in Excel 2007
29.09.08 at 17:41:17
SQL table and Excel 2007
SQL tables can be used in Excel 2007 just as with other database versions. However in Excel 2007 it is convenient that there is no 65,000 row limit anymore. There is a new limit, yes, but it is far away at 1048576 to be precise.  
Is it a good idea to return 1,000,000 rows from a SQL table into Excel? Probably not unless you want to make a backup or an export. I notice however that many people do return more data now we have Excel 2007. In this topic I would like to offer some advice how the SQL*XL software can be used when large volumes of data are accessed.
Select * from table
You can just use select * from table and not supply a where clause. SQL*XL will start fetching data and when you have enough data in the sheet you may abort the download. Please ask yourself whether it is useful to get an unspecified subset of rows from such a table.  
What about memory
Memory is consumed when you fetch data from the database. The more rows SQL*XL returns the more memory is needed unless you specify the large recordset option in which case the memory consumption does not increase during the download.  
If your query runs slower than for the first few hundreds of rows you may want to use this option.  
If you do need to fetch a lot of data please consider using the XLS output target instead of the Excel output target. It will write a new XLS file at a blink of an eye (ok I exaggerate, but it is fast!) and then loads the file into Excel. I would recommend you to use this if you fetch more than say 10,000 rows.
Back to top

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