Toad exporting data to multiple sheets in excel

Toad for Oracle & DevOps Toolkit
1

Is there a way to stop exporting data to multiple tabs in toad?

2

Please be more specific about what you are doing in Toad, how it comes out, and how you want it to come out. Also let us know which Toad version you are using.

3

And how many records... if it's "tabs" in the sense of worksheet tabs within Excel (if that's your target) there may not be a way to get around this if the volume of rows being exported is high.

4

Is this what you are looking for?

StartAtActiveCell
StartAtActiveCell.gif1132×695 1.41 MB

5

I have had this issue. I exported quite a lot of records.

6

if exporting to XLS file, Toad starts a new tab after 65,000 records
if exporting to XLSX file, Toad starts a new tab after approx 1M records.
if using the "Excel Instance" export option, it depends on the version of Excel.

7

I'm having a similar problem. I used the Automation Designer function to automate creating an Excel (XSLX) spreadsheet based on a database query, and I have it set to "Overwrite" if the file already exists, as shown in the below screenshot.

image
image685×588 108 KB

Yet when the automation is executed it keeps adding a new sheet as if I selected "Add new sheet" if the file already exists. Is there another setting that I'm unaware of that is causing this to happen?

Thanks

8

What is your Toad version?

9

Thanks for the quick reply, and sorry that I didn't include the software version before.

I'm using Toad for Oracle Xpert (64-bit) 12.9.0.71

10

It's a bug in that version, sorry. To work around it, you could add a "delete file" action to run immediately before the export dataset action.

I don't know exactly which version this was fixed in, but it is fixed in the latest version of Toad (13.3).

12

Thanks for the update and advice JohnDorlon. I was already considering writing a delete file process before the create Excel file action as a temporary work around, so glad to know that you agree.

Is it possible to create the delete file action with Toad Automation Designer? I was going to write something with Powershell, yet, if I can do it from Toad Automation Designer, it'd be better with only 1 application script to troubleshoot/audit.

Thank you

13

There is a Delete File action in Automation Designer. Look on the File Management tab.

Set it up like this. Then run App1. That will run Delete1 followed by ExportDataSet1.

image

1 Like