Many business analysts rely heavily upon Microsoft Excel as one of their primary tools for manipulating and reporting upon their business data. Sometimes they will send data extraction requests to their information systems (IS) people to perform and then deliver, sometimes they’ll instead access it themselves in place via the external data interface within Excel, and sometimes they’ll extract the data themselves from the Oracle database and then perform their magic upon that data in Excel. No matter what the scenario, the facts are that business people often want to work with Oracle database information in their tool of choice – MS Excel – and thus there is an overriding need for making that data readily accessible. That’s where Toad can be of great assistance. In this week’s blog I’ll review several very key and common scenarios whereby Toad facilitates working on Oracle data within Excel.

 

Look at this screen snapshot below – I performed a query within the Toad SQL Editor and merely want to copy the selected/highlighted rows from the Toad data grid into an Excel workbook. I simply perform a mulit-row selection, perform a copy (i.e. control-c) and a paste within Excel (i.e. control-v) – resulting in the Excel workbook PASTE_#1. That’s it – simply the steps one might expect in a Windows environment. But what if I don’t want the column headers pasted in with the data – can Toad handle that too?

The answer (as it almost always is with Toad) is YES – Toad can do that. You simply need to investigate to find the mechanism provided for whatever task you’re looking to perform. In his case, you simply select the rows and right-hand-mouse click to see the data-grid menu, and then choose “Save-As” – which displays the dialog shown in the upper center of the following screen snapshot, and choose Excel Instance as the export format. Note that the check-box for “Include column headers” is not checked, this turns off the column headers being taken along for the ride. Now when the OK button is then pressed, Toad will now create a new workbook in an already running instance of Excel, as shown by Sheet5 below. Again that’s all there is too it – pretty simple and easy.

But what if Excel was not already running at the same time? Now what can Toad do? As before the answer is YES –Toad can once again accommodate almost any of your needs. As in the prior screen snapshot simply choose the export type of Excel file as shown below with the “Launch after creation” check-box checked – then Toad will now instead create the new Excel .XLS file with the selected Toad data and launch Excel to display it.

So what if the reason you’re sending the data over to Excel is simply so you can create a fancy PDF report for some business manager, YES – Toad could do that too. I won’t go into great detail since I’m already a couple pages into this – but search Toad’s online help for “Fast Reports” for a complete explanation. But here’s a simple example of what one might do within Toad – and possibly eliminate the need for using Excel or any other tool.

 

Steps to create the PDF file for the report shown below:

Here’s what the report designer looks like:

And here’s an example report – which can be exported in all the file formats shown in the drop-down box below: