Pull large volumes of data into Excel for analysis10.03.07 at 10:06:07

Pull large volumes of data into Excel for analysis

Ian from Australia sent us the following question.

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 ?

Answer: 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: http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1135066681/2

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: http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1163601907

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: http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1162290970/0http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1172472828/3

Copyright (C) 1995-2007 Linker IT
Software BV. All Rights Reserved. Oracle is a registered trademark of
Oracle corporation. Excel and Office are registered trademarks of
Microsoft corporation. Other names appearing on the site may be trademarks
of their respective owners. Software,
files, documents, articles and other material are provided
"as is" and without warranties as to performance or mechantability or
any other warranties whether expressed or implied. No
warranty of fitness for a particular purpose is offered.
sitemap