Splitting Query results by column value into Excel worksheets

Toad for Oracle
1

I’m looking to take a query result and populate Excel tabs based on a column value.

For instance selecting cities and states and then having a separate tab in Excel for each state.

Is this possible, if so how?

2

There’s no way to automatically do it, but the easiest way I think would be:

  1. Run your query, and include an “order by state”.

  2. Select all of the rows for the first state, rt-click, choose “export dataset” and as the export format, choose “Excel Instance”. Choose “selected rows only” in the export dialog, and then click OK. Leave excel open.

  3. Go back to the grid, select all of the rows for the next state, and repeat. Toad will move the next set of rows to a new sheet in excel.

Hopefully not all 50 states are represented, but even if they are, this process shouldn’t take too long.

3

If you are going to be doing this a lot, you could probably use Automation Designer to automate it somehow. Below is just a very simple example that I thought of - it uses hard coded values, but with a little work, you could probably figure out how to automate it to do what you want. Here’s an idea to possibly start with tho…

Create Export Dataset actions for each state (again, you could probably make this cleaner by maybe reading from a master state list and just having one export action instead of one for each state)…

3441.pic.jpeg

Set each query to a different state, and to export to Excel Instance with the Sheet name being the same as the state in the query. You might be able to figure out a way to use variables here too so you don’t have to enter each state individually.

pic2.jpeg

Result:

6354.pic3.jpeg

4

Hi Dennis,

What version of Toad are this screen shots from? I have Toad version 11.0.0.116 and cannot find Import/Export tab

Thanks

5

They are actions in the Automation Designer window. Utilities Menu > Automation Designer

6

I like Dennis’ idea, the problem is that city/state was just a way to explain what I was trying to do. The reality is that hard coding it like was suggested isn’t feasable. I’m still working on getting this to work using variables or a list and looping on that.

In the mean time I just wrote some VB code to do the work in Excel. Thanks for the responses.