Exporting Data to Excel

Toad for Oracle & DevOps Toolkit
1

Hi,

I'm using Toad for Oracle Professional 10.5.1.3

We've a set of 6 scripts. The first script fetches data of 12 columns and n rows. The second one fetches 11 columns and so on.

The number of rows of data fetched varies with script. The title / header of the last column fetched is common with all the scripts.

TEST.jpeg
TEST.jpeg779×272 38.9 KB

We manually cut paste the results (except column 1 title) from all the 6 fetched excel sheets into one excel sheet as shown in above pic.

Now, can we script / set things in toad so that all the 6 scripts ran as one script will fetch the results as shown above.

Regards.

2

I have done something similar using Automation Designer. Create an app with multiple steps. Each step is an Export Dataset running one query. The first is output
to an Excel File. Each subsequent step outputs to an Excel Instance. When it is done you have a workbook with a tab for each query. You can then use Excel macros stored in an autoload “personal” workbook to copy-and-paste the tabs together. Not an “elegant”
solution, but it gets the job done and does it in seconds versus minutes doing it manually. You can even schedule it to run automatically once you have it perfected.

If there is a better way I’d sure like to know about it. I’ve done this sort of thing a few times. It’s tedious to set up, but it gets the job done and with
a lot less manual work once it’s set up.

From: vjdilip [mailto:bounce-vjdilip@toadworld.com]

Sent: Tuesday, January 07, 2014 9:37 AM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Exporting Data to Excel

Exporting
Data to Excel

Thread created by vjdilip

Hi,

I’m using Toad for Oracle Professional 10.5.1.3

We’ve a set of 6 scripts. The first script fetches data of 12 columns and n rows. The second one fetches 11 columns and so on.

The title / header of the last column fetched is common with all the scripts.

We manually cut paste the results (except column 1 title) from all the 6 fetched excel sheets into one excel sheet as shown in above pic.

Now, can we script / set things in toad so that all the 6 scripts ran as one script will fetch the results as shown above.

Regards.

To reply, please reply-all to this email.

Stop
receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

3

If each script is just a SELECT statement, you can copy them all into a single script that says

SELECT a,b,c,d,e,f,g,h,i,j,k,l

FROM

UNION ALL

SELECT a,b,c,d,e,f,g,h,i,j,NULL,l

FROM

UNION ALL

SELECT a,b,c,d,e,f,g,h,i,NULL,NULL,l

FROM

UNION ALL

…

And export that result set to Excel.

Nate Schroeder

US Row Crops IT Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. G3WB - Saint Louis, MO - 63167

314-694-2592

From: vjdilip [mailto:bounce-vjdilip@toadworld.com]

Sent: Tuesday, January 07, 2014 9:37 AM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Exporting Data to Excel

Exporting Data to Excel

Thread created by vjdilip

Hi,

I’m using Toad for Oracle Professional 10.5.1.3

We’ve a set of 6 scripts. The first script fetches data of 12 columns and n rows. The second one fetches 11 columns and so on.

The title / header of the last column fetched is common with all the scripts.

We manually cut paste the results (except column 1 title) from all the 6 fetched excel sheets into one excel sheet as shown in above pic.

Now, can we script / set things in toad so that all the 6 scripts ran as one script will fetch the results as shown above.

Regards.

This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled

to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and

all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.

All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its

subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of “Viruses” or other “Malware”.

Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying

this e-mail or any attachment.

The information contained in this email may be subject to the export control laws and regulations of the United States, potentially

including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of

Treasury, Office of Foreign Asset Controls (OFAC). As a recipient of this information you are obligated to comply with all

applicable U.S. export laws and regulations.

4

Good point Nate. I didn’t think about that because my automated queries are different queries with different outputs and running against different database
instance (production and test) for the purposes of comparing overnight test results to overnight production results.

From: nathan.e.schroeder [mailto:bounce-nathaneschroeder@toadworld.com]

Sent: Tuesday, January 07, 2014 2:29 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Exporting Data to Excel

RE:
Exporting Data to Excel

Reply by nathan.e.schroeder

If each script is just a SELECT statement, you can copy them all into a single script that says

SELECT a,b,c,d,e,f,g,h,i,j,k,l

FROM

UNION ALL

SELECT a,b,c,d,e,f,g,h,i,j,NULL,l

FROM

UNION ALL

SELECT a,b,c,d,e,f,g,h,i,NULL,NULL,l

FROM

UNION ALL

…

And export that result set to Excel.

Nate Schroeder

US Row Crops IT Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. G3WB - Saint Louis, MO - 63167

314-694-2592

From: vjdilip
[mailto:bounce-vjdilip@toadworld.com]

Sent: Tuesday, January 07, 2014 9:37 AM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Exporting Data to Excel

Exporting
Data to Excel

Thread created by vjdilip

Hi,

I’m using Toad for Oracle Professional 10.5.1.3

We’ve a set of 6 scripts. The first script fetches data of 12 columns and n rows. The second one fetches 11 columns and so on.

The title / header of the last column fetched is common with all the scripts.

We manually cut paste the results (except column 1 title) from all the 6 fetched excel sheets into one excel sheet as shown in above pic.

Now, can we script / set things in toad so that all the 6 scripts ran as one script will fetch the results as shown above.

Regards.

This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled

to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and

all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.

All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its

subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of “Viruses” or other “Malware”.

Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying

this e-mail or any attachment.

The information contained in this email may be subject to the export control laws and regulations of the United States, potentially

including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of

Treasury, Office of Foreign Asset Controls (OFAC). As a recipient of this information you are obligated to comply with all

applicable U.S. export laws and regulations.

To reply, please reply-all to this email.

Stop
receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

5

Yeah, I figured if the easy answer would work you would have done it already, but it didn’t hurt to suggest it.

Nate Schroeder

From: crjohnso [mailto:bounce-crjohnso@toadworld.com]

Sent: Tuesday, January 07, 2014 3:05 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Exporting Data to Excel

RE: Exporting Data to Excel

Reply by crjohnso

Good point Nate. I didn’t think about that because my automated queries are different queries with different outputs and running against different database
instance (production and test) for the purposes of comparing overnight test results to overnight production results.

From: nathan.e.schroeder [mailto:bounce-nathaneschroeder@toadworld.com]

Sent: Tuesday, January 07, 2014 2:29 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Exporting Data to Excel

RE:
Exporting Data to Excel

Reply by nathan.e.schroeder

If each script is just a SELECT statement, you can copy them all into a single script that says

SELECT a,b,c,d,e,f,g,h,i,j,k,l

FROM

UNION ALL

SELECT a,b,c,d,e,f,g,h,i,j,NULL,l

FROM

UNION ALL

SELECT a,b,c,d,e,f,g,h,i,NULL,NULL,l

FROM

UNION ALL

…

And export that result set to Excel.

Nate Schroeder

US Row Crops IT Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. G3WB - Saint Louis, MO - 63167

314-694-2592

From: vjdilip [mailto:bounce-vjdilip@toadworld.com]

Sent: Tuesday, January 07, 2014 9:37 AM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Exporting Data to Excel

Exporting
Data to Excel

Thread created by vjdilip

Hi,

I’m using Toad for Oracle Professional 10.5.1.3

We’ve a set of 6 scripts. The first script fetches data of 12 columns and n rows. The second one fetches 11 columns and so on.

The title / header of the last column fetched is common with all the scripts.

We manually cut paste the results (except column 1 title) from all the 6 fetched excel sheets into one excel sheet as shown in above pic.

Now, can we script / set things in toad so that all the 6 scripts ran as one script will fetch the results as shown above.

Regards.

This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled

to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and

all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.

All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its

subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of “Viruses” or other “Malware”.

Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying

this e-mail or any attachment.

The information contained in this email may be subject to the export control laws and regulations of the United States, potentially

including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of

Treasury, Office of Foreign Asset Controls (OFAC). As a recipient of this information you are obligated to comply with all

applicable U.S. export laws and regulations.

6

Could you use dblinks to collect all the results into a single temporary table in test, then use a single query to dump it to Excel?

7

yes in any way that oracle supports db_links compatibility (versions between databases)