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
Pull large volumes of data into Excel for analysis (Read 8267 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Pull large volumes of data into Excel for analysis
10.03.07 at 10:06:07
Pull large volumes of data into Excel for analysis
Ian from Australia sent us the following question.
Just enquiring about Excel SQL. My company currently extracts a large volume of data into Excel spreadsheets that use Pivot tables to summarise statistical data.
Would SQL*XL be able to accomodate this ?
Yes it can help you with that. This is actually what SQL*XL is best in. SQL*XL takes care your data is imported into Excel ready to be analysed. Data types are checked to prevent Excel making the wrong decisions about the data types.  
Pulling data into Excel can be done in various ways. The traditional way is to type the SQL into the SQL dialog and to query it directly into Excel. If  you use more than 1000 rows we suggest you use the large recordset option in the resultset options dialog. This option lets you query a lot of data without all the data going into your computer's memory first. The large recordset option is not the default as chosing it will prevent you from making updates to the data later. Updates can only be done on data that is pulled into Excel using the normal dataset option. If you don't care about updates you can set the large recordset option as default.
The best performance is received when you turn off the formatting option. Even better performance is achieved when you use the XLS output target instead of the Excel output target. The XLS output target will write a new binary XLS file. This process is very very fast.
See this topic to read more about performance tips:
There are other techniques too to pull a lot of data into Excel quickly. You can e.g. directly bind the data to a pivot table using SQL*XL. Refreshing the data is also possible so if you open the spreadsheet at a later date you can connect to the database and requery/refresh the data bound to the pivot table. Note that you can use the pivot table even when you have disconnected from the database. You could e.g. take the excel file with the pivot table to a customer on your laptop and change the pivot table there, dump some cross sections, etc. All the features of the pivot table will work as normal.
For more information about using pivot tables as output target see this topic:
A new technique we have introduced recently also lends itself to view large sets of data. Using the SQLQueryValue formula a cell can be bound to a query value. When you enter the formula as an array formula you can bind a range of cells to multiple columns and rows of a dataset. If you e.g. would use the formula:
=SQLQueryValue("select empno, sal, hiredate from emp") and you would apply this formula to A1:C100 you would view the contents of the emp table in your sheet. The query will only be executed once and the results will be displayed as formula result.
To read more about this technique see the following topics:
Back to top
« Last Edit: 10.03.07 at 10:11:04 by Gerrit-Jan Linker »  

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