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.

    Summary
    So here we have another perfect example of Toad having multiple ways to accomplish your task at hand. Each have their own merits. Use them at your own discretion. I didn’t talk about Toad being able automate this process, but you can code Toad to create and email your XLS files on demand or on a schedule.