Three (4) Ways to get Data into Excel with Toad

I have been really hard on Excel lately. Excel is an excellent tool. Tools can be misused and abused, but it’s not their fault. I should know this being an advocate for Toad, but I digress.

Updated on 1/5/2011 to include 4th Method and add note on ‘Display all results in grid’ option.

You want to get data from Oracle to an MSFT Excel spreadsheet. You have Toad. I know of at least 3 ways you can do this. Each have their own advantages. Let’s take a few moments to explore your options.

Copy & Paste
By far my least favorite option, this is probably also the most popular method used by new users to Toad. You select your data in the grid, Ctrl+A, Ctrl+C, ALT+TAB, Ctrl+V – voila! Your data is now in Excel. What’s wrong with this method?

Nothing.

Everything!

You need to make sure Excel is open first. You won’t get any formatting, i.e. dates won’t come over as ‘date’. If you want to format the data in Excel after-the-fact, there’s no way to automate that without creating a macro.

But if it works for you, then happy days!

Export Dataset – Delimited Text
According to the developer, this is your fastest option. The only caveat is certain datatypes may not play nice. And of course it won’t be an XLS or XLSX file. I just tested this and it took about 25 seconds on my machine, not counting the time Excel took to load the file. At this point I would have to agree with the developer!

You’ll want to make sure you set the ‘Delimited Text’ export option to have the delimiter set to ‘Comma’ and the change the file extension from .TXT to .CSV (Comma-Separated Values). Also en-sure that auto-open is enabled and that you have Excel set as the default program for files of extension CSV.

Export Dataset – Excel File
This method is probably what you are used to using. You invoke it from any data grid. Toad will retrieve all the records from the database, and write them to a physical XLS file on your machine or network.

Toad gets a lot of grief for the number of options it has, but I’m never lacking for suggestions from users on how to make the tool more powerful or useful. The options for creating the Excel file here are just on this side of overwhelming, but there are a few I want to call out:

Launch after creation

Display all results in grid

Include SQL statement

Sheet name.

Most of these options are self-explanatory, but you do need to actually investigate your choices if you want to take advantage of them! The second choice does need a bit more explanation, so let’s cover that now.

Pay Attention, This Is Important!

“Display all results in grid” – Checking this will cause Toad to not only fetch the rest of your table or query rows from Oracle to put them in your file, it will also populate the data grid you are exporting from with said rows. That sounds fairly benign, and why WOULDN’T you want to do this? Well, if you do, you’re going to add a step to the process, and you’re going to need more memory. If your exports are taking too long to execute, then uncheck this option! This is one of those tricks I’ve picked up over the years and take for granted. Here’s an opportunity to impress your friends and co-workers.

However, if you do disable this, Toad will re-execute the query. So if the data has changed between runs, or if the initial query took a long time to run, you may want to leave this option enabled.

Some things to be aware of:

You do NOT need to have Office installed. Toad does not actually talk to MSFT to write the XLS file. We write it for you.

By default, we build a XLS file. You can change the extension manually to XLXS. Be forewarned I’ve observed some buggy behavior on larger datasets, so if you want a 1,000,000 row datasheet, read on to option #3!

Export Dataset – Excel Instance
This is the method I use more often than not. Why is that? I can:

Create 1,000,000 row worksheets

Append data to an existing sheet

Work with excel all day and not create any new XLS files to clutter my PC.

The ‘Instance’ option talks to MSFT. It asks, “Are you there Excel, it’s me, Toad.” If Excel answers ‘Yes’, it dumps the data over into a new sheet, or into the active one based on the ‘Start at active cell’ option. If it answers ‘No’, then it first opens Excel, then dumps the data. So, this option DEFINITELY requires you to have Excel installed and licensed.

Not to get too defensive, but I ONLY build the 1,000,000 XLS files as an exercise. I probably get asked how to accomplish this task about 2 – 3 times a week.

Example
I use SH.SALES as an example. It takes approximately 90 seconds for my 11gR2 database and Win64 Win7 machine with 8GB of RAM to create the instance of Excel with 918,884 rows or 15.9MB of data.

The primary reason I use the Instance option if for appending lots of data to single file. I can simply click into the cell I want the data to go, then click the ‘Export’ button in Toad and have that data sent over auto-magically formatted.

Your post regarding the Excel Instance Toad export option for 1,000,000 row worksheets really came in handy for me today. I was able to accomplish exactly what I was looking to do just by following the steps that you had laid out.

I’ve got a big query result set I’m trying to export (currently to .CSV) for import to Excel. This takes a very long time, so I looked up how to get around this lag, which led me here. I tried unchecking the “display all results in grid” option, but this is still taking a very long time and essentially hangs my system unless I out of the export. Any thoughts on other alternatives for the text-based output? (Perhaps I need to provide more details?) Thanks!

As i do not have excel installed on my database server i had just one choice to export the data into “Excel File”. I had some 20 queries which i wanted to dump the data into one excel workbook into multiple excel sheet. THis was not possible using the “Excel File” option though i had different Sheet Name mentioned it was overwriting and was not creating different sheet. So i ended up using CSV and then i had to write a macro to import all the CSV into different worksheet.
I might be wrong..if i am then please let me know….I currently using TOAD 9.7.0.51

If i am running several queries as a script and my resuts are in one output file that hass the reults of all the queries and several grid tabs that havethe individual results, how do I export all the queries to excel at once or just the output tabe that has all the results?

I haven’t been at Quest (now Dell) for almost two years, so any answer I have would be a bit out of date and no longer an ‘official’ answer.

However in general I can say with some bit of confidence that if you take your script and use the SPOOL command to write out the results, you’ll have more control. The same answer applies with Oracle SQL Developer…

How to add and save one more excel sheet to already created excel file(Export dataset).I have tried using excel instance but it opens new excel sheet and dumps all values. Moreover I have to save it explicitly.

Is there any option so that i can add this new excel sheet into already existing file and it gets automatically saved.

I know its crazy, but I am trying to export my query results to Excel from a table that has more than 256 columns. I am getting an error for too many columns. Is there a way to get around this other than reducing the number of columns selected to <_ 256? Thanks.

I mostly write about working with Oracle databases, mostly. I currently work for Oracle, but I'm not an official spokesman, nor am I authorized to speak on behalf of Oracle Corp. In other words, what you read here are my words and ramblings.

If you've detected a bit of snark, that means you've been paying attention. +1 bonus credits for you!