Mar
27
Written by:
Daniel Norwood
3/27/2009 4:30 PM
If you’re like many of the analysts I speak with on a regular basis, then you use Microsoft Excel. It’s a staple of the Information Age, right!? It seems like you can do everything but wash your car with Excel. But what if you want to get that data out of your database and into a series of worksheets… on a regular basis…? Unless you have lots of free time, this is probably something that you don’t look forward to. Let’s take a look at how Toad for Data Analysts can make your Excel-life even easier!
Let’s assume you have a series of five unique queries that you would generally run each week and manually copy the result of each into your XLS file. Toad for Data Analysts can be used to execute each query and even give you nice, helpful features to expedite the process. (Toad’s “One-Click Export” is a powerful time saving feature that we’ll cover in a future post.) However, the process can be streamlined to the point where it’s fully automated – the XLS files can even be copied to a network share or emailed to someone without you touching a thing.
Start with a script
Begin by taking all of the disparate queries and adding them together to form a single script. For instance, if your scripts all run against the same Oracle instance, just copy and paste the SQL from each query into a single SQL Editor file and separate each one with a semi-colon (for SQL Server use the “GO” keyword, etc…). This will create a single file that you can execute from Toad for Data Analysts and generate multiple result sets. Once you have the script working well and generating the data you need, let’s move on to the next step: building an automation timeline.
Build an automation timeline
Now that your script is in place, the automation timeline will help make that manual process of copy-and-paste non-existent. Start by clicking on the “4 Automate” button at the top of Toad for Data Analysts to create a new automation timeline. The timeline is simply a list of actions that you want to execute, each in order. Begin by clicking on the “Select to File” action. This adds the action to the timeline and also adds the associated action “Database Connection,” which is set to the current connection by default. (If you need to change this, use the Connection property in the Activity Properties pane on the right.) Double-click on the “Select to File” action to display a properties dialog for the action. Here you can select the SQL script that you just created, set the output XLS filename and optionally specify a unique name for the file. This action will execute your script and create an XLS file with a separate tab for each of the queries in your script. No more copy-and-paste!
Next steps
From here, the possibilities are endless! Maybe you want to execute a few more scripts and save them to XLS (or HTML or CSV)… maybe you need to zip the files and copy them to the web server… maybe you just need to send a quick email to someone with the data… perhaps you want to integrate the file you just created into some other process or application (using the “Run Application” command line action) – you’re only limited by your imagination.
Once your super-amazing timeline has been created, press the “Test” button to run the automation timeline and ensure that it works just like you thought it would. Once you’re satisfied that the timeline does what you need it to, press the “Schedule” button (on the lower right-hand corner of the window) to create a task in Windows Scheduler. As you can see, everything is done for you automatically; all that’s left for you to do is to set the actual schedule that you want this script to run on, enter your OS credentials and you’re set. (Note: this is Window’s Scheduler that you’re directly interacting with at this point. Toad for Data Analysts does not have access to your OS credentials.) Now that your timeline is created, tested and scheduled, sit back and relax – you’re done! Toad for Data Analysts will open up in minimized mode (you can see it on your Windows Taskbar), run through the actions in the timeline and quietly close itself. You don’t need to worry about it interrupting your work if you have Toad for Data Analysts open, either – the whole process happens in the background.
In the next version of Toad for Data Analysts we’ll be introducing new automation features like IF conditions, LOOPs, variable support and more. If you’re the kind of person that likes to live on the bleeding edge of technology and stay up to date with changes as they happen, join the beta program and see these new features first-hand. It’s open to everyone and available through ToadWorld.com!
Daniel