Wondering how to copy Oracle data to Excel using Toad?

The problem with exporting Oracle data to Excel

Many business analysts rely heavily upon Microsoft Excel as one of their primary tools for manipulating and reporting upon important business data. You will often hear them make requests such as the following:

• CopyingOracle data to Excel
• ExportingOracle data to Excel
• ExtractingOracle data to Excel
• DumpingOracle data to Excel

They key point is that regardless of how the business users ask, the request is essentially the same. Some very common ways they obtain such access to the database data included in an Excel spreadsheet are:

• Business user sending a formal database data extraction requests to their information systems people to perform and then deliver the needed Excel Spreadsheet, or a “comma delimited” text file to load in Excel themselves. However, this process can take time—sometimes more than the business user can wait.
• Business user accessing the database data directly by themselves via Excel’s external data interface capability. However, this requires ODBC connectivity; plus Excel direct database access can be quite slow, a little complicated, and distracts from task at hand. In fact, some companies do not allow or support users accessing database data via ODBC.
• Business user truly self-servicing by extracting the database data themselves using a powerful tool like Toad® by Quest® for Oracle, and then working with that data in Excel. This approach is quick and easy, plus Toad for Oracle integrates easily into the business analyst’s workflow.

The solution to exporting Oracle data to Excel

Many business analysts strongly favor the third choice of using Toad for Oracle to self-service. In this week’s blog I’ll review some common scenarios that facilitate easily copying Oracle data to Excel using Toad. The techniques that I’ll be showing work essentially the same in both of Toad’s two main screens: the SQL Editor and Schema Browser. I’ll demonstrate using the SQL Editor, which is probably the most used screen within Toad.

The technique of moving Oracle data to Excel

Smaller data sets

So in Figure 1 below I’ve opened a Toad for Oracle SQL Editor and executed a query that returns some data. If the amount of data (i.e. the number of rows) that you are copying from Oracle to Excel using Toad is not excessive, then you can simply multi-select the desired rows and do a standard Windows operation to cut (CONTROL-C) and paste (CONTROL-V) the data into Excel.

Figure 1: Cut and paste data from Oracle to Excel

Larger data sets

If the amount of data (i.e. the number of rows) that you are copying from Oracle to Excel using Toad is the entire table, rather than having to vertically scroll to select all of the rows, instead you could do a standard Windows operation to multi-select (CONTROL-A) all of the rows followed by a cut (CONTROL-C) and paste (CONTROL-V). However that would be the hard and potentially slow way, plus that many rows might not fit in your desktop computer’s free memory. A better method would be to invoke the Export Dataset wizard by either pressing the SQL Editor’s toolbar icon for Export Dataset or performing a Right-Hand-Mouse click in the data grid followed by selecting the pop-up menu’s Export Dataset option as shown below in Figure 2.

Figure 2: Invoking the Export Dataset Wizard

Either invocation method will result in displaying the Export Dataset wizard shown below in Figure 3, which is a very powerful facility in Toad for Oracle worthy of its own blog about the many things it permits one to do. For now I’ll simply focus on copying from Oracle to Excel using Toad. There are several Format choices which result in copying from Oracle to Excel, including: Excel File (creates a new Excel file), Excel Instance (creates a new worksheet in currently opened Excel instance), and delimited text which can be imported by Excel.

Figure 3: Export Dataset Wizard

Note that selecting among the different Formats will result in different options being displayed. So take your time to note and select from the many different options presented.

In my case I wanted all the rows, therefore unchecked the option for Export only selected rows. I wanted the column headers and cell borders both included. On my desktop the process of copying from Oracle to Excel using Toad for one million rows took less than a minute – your mileage will vary.

Conclusion

If you are a business user who wants to copy from Oracle to Excel, use Toad. You can do simple cut and paste, export to Excel file, export to Excel worksheet, and even export to text delimited. Thus you can self-service and get the data you need without requesting help from your information systems people or using slow and problematic interfaces such as ODBC. No other tool makes are copying data from Oracle to Excel easier and quicker than Toad. Plus, no matter you’re whether using the freeware or commercial version of Toad, the process is essentially the same.

Related Toad World posts

Blog: Create and Load Oracle Tables from Excel Spreadsheet using Toad for Oracle

Useful resources

Toad for Oracle general information

Technical brief: Top Five Reasons to Choose Toad Over SQL Developer

Case study: Opening doors and creating opportunities with data insights

Have questions, comments?

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!