Sep 22, 2020 10:09:09 AM by Bert Scalzo
Wondering how to copy Oracle data to Excel using Toad?
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:
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:
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.
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
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.
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.
Blog: Create and Load Oracle Tables from Excel Spreadsheet using Toad for Oracle
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
Head over to the Toad for Oracle forum on Toad World®! Chat with Toad developers, and lots of experienced users.
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!
Tags: Toad for Oracle
Written by 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.
This popular author is known throughout the industry for his instructive books, which include:
• Oracle DBA Guide to Data Warehousing and Star Schemas
• TOAD Handbook (1st Edition)
• TOAD Handbook (2nd Edition)
• TOAD Pocket Reference (2nd Edition)
• Database Benchmarking: Practical Methods for Oracle & SQL Server
• Advanced Oracle Utilities: The Definitive Reference
• Oracle on VMware: Expert Tips for Database Virtualization
• Introduction to Oracle: Basic Skills for Any Oracle User
• Introduction to SQL Server: Basic Skills for Any SQL Server User
• Toad Unleashed
• Leveraging Oracle Database 12cR2 Testing Tools
• Database Benchmarking and Stress Testing
Drop Bert an email at email@example.com and he’ll write you back, and maybe send you an autographed book!