Naming Excel sheets when Exporting data from SQL Queries

I have a sql query file with couple of sql queries.
select * from a;
select * from b;
when i am export this file into excel sheet.
How do i name the sheets with the name of the table like
sheet1 -->A
sheet 2--> b
please let me know

Good Evening Paidkondala,

When you right click the grid and selext export to an excel file or instance, the dialogue has a text box where you can type the name of the worksheet.

I'd enclose a screen dump, but I'm not near my Toad installation, sorry.

HTH.

Cheers,
Norm. [TeamT]

Thanks for the input.
But i have around 200 sql queries to work on.
So, its hard to name individually
so looking for other alternatives

@paidikondala If you are willing to put your SQL queries in one query per .sql file, there is a solution for you. Let me know if that interested you and I'll find it.

On the other hand, if they are all in one file and you want to keep it that way, I don't think there is a way for Toad to automatically run each query and save the results to a named tab in the same excel file.

Thank you Jdorlon.
Yes, I am aware of the solution that it will work if i put each sql in one query file.in that way i have to create 200 files.So looking for other options.
Thank you

I don't think there is a way to get them all into named sheets, but maybe there is a way to at least get them all into one XLSX file. I'll give it a try a little bit later today. Will let you know either way.

I am wondering if you can use Automation here to loop through your queries and append a new sheet for each query in the existing Excel workbook?

@Gary.Jerep Good thought. The tricky part is that he's got all of his SQL statements all in one file.

@paidikondala Toad doesn't have a way to process SQL statements out of a single file. At this point, your best option is probably to write some code to create separate files for each one, then you can use Toad to export them all. Hopefully I can add something to Toad 13.3 where you can just give it the filename and let Toad do the rest.

That's a good idea to write a code to put each query in one SQL file and upload them all and run them at once.
Will try to do it

Automation is putting in to different excel sheets but naming them as sheet 1 and sheet 2
I was not able to change the sheet 1 as Tablename A, table name B and table name c

Haven't tried it yet, but you may need to use variables (e.g. to assign a variable to the table name in each query.

Can you send me a sample or can you please direct me to the link where can I see the sample code to assign the table name into variable

I don't think it is possible to assign the sheet name from a variable. But you can set it so that the sheet is named after the table you are querying from like this:

I am not sure if i can create multiple queries in a export data set.??
When i tried to create more than one query it's throws me a error ORA 00933??
Please let me know if i am doing anything wrong??
Thanks,

I describe how to do it here: Export dataset multiple queries

Note that the ability to use the folder iterator was added for 13.2 beta (Sorry, I forgot about that earlier...was reminded when I looked up this other forum post)

I've logged an enhancement for next version to be able to make your SQL file like this, and Toad will name the sheets if you specify them. I'll post back here when this is available.

-- sheetname: small_emp_no
select * from EMP where empno < 7500;
-– sheetname: large_emp_no
select * from EMP where empno > 7500;
-– sheetname: DEPT
select * from DEPT;
select * from BONUS;

Update 12/9/2019: This has been added in Toad for Oracle 13.3 Beta

You can also specify filename or table name with comments (for exporting to other formats)

-- filename: my_awesome_file
-- tablename: my_awesome_table
-- schemaname: my_awesome_schema
2 Likes