Toad World® Forums

Export dataset multiple queries


#1

Hello guys
This is my first post here and I have a question about queries export.

I have multiple .sql files (+600) and each return between 100k - 300k lines.
I need to export the results from each .sql to .xlsx files.

What I’m doing now, F9, right click “export dataset”, type the name of the file (that should be the same as the .sql file) and export to Excel file but it’s exhaustive.
I tried list iterator + export dataset but in the tab Dataset I put %List Iterator1% as I saw in this same forum and I have “invalid SQL statement”.

Is there any way to automatize this work or simplify it? My knowledge in Toad is almost zero, please, any help would be great. Thank you


#2

The Folder Iterator is what you want here, but I see that the export dataset’s “SQL FIle” feature doesn’t support variables. I am adding this for next beta. Assuming all your SQL files are in the same folder, you will be able to do this:

.SQL Files are in a folder called c:\Queries.

Set up your action like this:
image

Folder iterator detail:
image

Export Dataset detail:

then, right-click on the “Folder” action and choose “Run”.


#4

Thanks a lot man. I believe it will work. Just one more question
I’m using Toad for Oracle 13.0.0.80 and I don’t see “SQL file” radio in the Dataset tab.
Any tip?

tab


#5

The “SQL File” button first appears in Toad version 13.1. And I just made a change for tomorrow’s beta so that variables would work there.

So…update to version 13.1, and you can get the beta here: Toad for Oracle 13.2 Beta

The beta will not work unless 13.1 is installed. You can keep 13.0 installed also if you want.


#6

Thanks again. I’ve updated to 13.1 and new I have to SQL file option however, I have another problem. I tried a lot of ways to make it work but I had no success. Could you help me again?
Thank you very much for your help.


#7

Sorry, I should have been more clear above.

13.1 has the SQL File field but in 13.1, it doesn’t support variables like %folder%.
I added variable support to the next 13.2 beta, which should come out later today. So if you get the Toad for Oracle 13.2 Beta, after today’s update, then what I described above should work.

We’ll post an update here https://forums.toadworld.com/c/toad-for-oracle-beta when today’s beta update is released.


#8

Ohhh yes, sure. I’d read your comment too fast and hadn’t understood, now I got it HAHA
Thanks again :grin:


#9

I’ve just tested 13.2.0.9 and it worked like a charm.
That’s awesome, thanks a lot. You saved me aprox. 2 months work


#10

Great! You’re welcome.