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.

Two ways to export Oracle data from Excel. Open a Toad for Oracle SQL Editor. Execute a query that returns data. If the amount of data is not excessive, multi-select the desired rows and cut and paste 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.

Invoke the Export Dataset wizard, then select 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.

Next the Export Dataset wizard will result displaying several format choices.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.



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.


Need help managing data? Try Toad for free!

Quest Software® is here to help you simplify complexity, reduce cost and risk, and drive performance. Maybe that’s why we’re consistently voted #1 by DBTA readers and have 95%+ customer satisfaction rate.  

Try Toad for Oracle free for 30 days.

Already in a trial? Talk to sales or buy now online.

Already a loyal fan of Toad for Oracle? Renew now.

Toad® database management tools are cross-platform solutions from Quest® that can help new and veteran DBAs, and even “accidental” DBAs manage data in Oracle, SQL Server, DB2, SAP, MySQL, and Postgres environments. 

Visit Toad World® often for free advice via our blogs, free interactive help via our forums and free trial downloads. 


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!

About the Author

Bert Scalzo

Bert Scalzo is a guest-blogger for Quest and a renowned database expert, Oracle® ACE, author, database technology consultant, and formerly a member of Dell Software’s TOAD dev team. With three decades of Oracle® database experience to draw on, Bert’s webcasts garner high attendance and participation rates. His work history includes time at both Oracle Education and Oracle Consulting. Bert holds several Oracle Masters certifications and has an extensive academic background that includes a BS, MS and Ph.D. in computer science, as well as an MBA, and insurance industry designations. Bert is a highly sought-after speaker who has presented at numerous Oracle conferences and user groups, including OOW, ODTUG, IOUG, OAUG, RMOUG and many others. Bert enjoys sharing his vast knowledge on data modeling, database benchmarking, database tuning and optimization, "star schema" data warehouses, Linux® and VMware®. As a prolific writer, Bert has produced educational articles, papers and blogs for such well-respected publications as the Oracle Technology Network (OTN), Oracle Magazine, Oracle Informant, PC Week (eWeek), Dell Power Solutions Magazine, The LINUX Journal, LINUX.com, Oracle FAQ, Ask Toad and Toad World.

Start the discussion at forums.toadworld.com