Action Palette, Execute script

[cjcfh00]

Hi,

I'm hoping you someone can assist.

On using the action palette, on the Misc tab, the execute script
option.
I created a execute script. On going to properties, I've specified
the script that should run under the scripts heading.

Under the output heading I've selected use single file. In the
directory field I've specified the xls file that I would like,
however after executing the script the data is exported to the file,
all the data is displayed in column A. How do a get the data to
speard over the columns?

Alternatively I have executed a script (which bascially consists of
two select statements).
The data that is brought back is returned into 2 grids. How can I
save the data from both grids into one excel file?

Thank you
Regards
CJ

[John Dorlon]

1)       If the data is returned from your query in a single column, it will go to the spreadsheet that way.   If you want the result to go to excel as a single row with multiple columns, you need to write your query that way.

2)      Right-click in the one grid and choose "Save As".   Use the "Excel Instance" export option and proceed with the export.    Leave excel open.

3)      Right-click in the next grid repeat step 2.  Toad will make a new sheet in excel and send the next dataset to it.

-John


From: toad@... [mailto:toad@...] On Behalf Of cjcfh00
Sent: Wednesday, October 01, 2008 6:51 AM
To: toad@...
Subject: [toad] Action Palette, Execute script


Hi,

I'm hoping you someone can assist.

On using the action palette, on the Misc tab, the execute script
option.
I created a execute script. On going to properties, I've specified
the script that should run under the scripts heading.

Under the output heading I've selected use single file. In the
directory field I've specified the xls file that I would like,
however after executing the script the data is exported to the file,
all the data is displayed in column A. How do a get the data to
speard over the columns?

Alternatively I have executed a script (which bascially consists of
two select statements).
The data that is brought back is returned into 2 grids. How can I
save the data from both grids into one excel file?

Thank you
Regards
CJ

[Erwin Rollauer]

This works very well in 9.6.  John's directions are also right on.

 

One thing to check. If you want the entire grid selected make sure that
EXPORT SELECT ROWS is NOT checked.

 

PS to John.

How about changing the wording to Export ONLY selected rows. It did not
make it obvious to me that unchecking would select all. But then I
probably don't read all the words all the time.

 

Erwin Rollauer, ISR, McGill, 514 398-5023 ex 00626

From: toad@... [mailto:toad@...] On Behalf Of
John Dorlon
Sent: Wednesday, October 01, 2008 9:12 AM
To: toad@...
Subject: RE: [toad] Action Palette, Execute script

 

1)       If the data is returned from your query in a single column, it
will go to the spreadsheet that way.   If you want the result to go to
excel as a single row with multiple columns, you need to write your
query that way.

2)      Right-click in the one grid and choose "Save As".   Use the
"Excel Instance" export option and proceed with the export.    Leave
excel open.

3)      Right-click in the next grid repeat step 2.  Toad will make a
new sheet in excel and send the next dataset to it.

 

-John

 

 

From: toad@... [mailto:toad@...] On Behalf Of
cjcfh00
Sent: Wednesday, October 01, 2008 6:51 AM
To: toad@...
Subject: [toad] Action Palette, Execute script

 

Hi,

I'm hoping you someone can assist.

On using the action palette, on the Misc tab, the execute script
option.
I created a execute script. On going to properties, I've specified
the script that should run under the scripts heading.

Under the output heading I've selected use single file. In the
directory field I've specified the xls file that I would like,
however after executing the script the data is exported to the file,
all the data is displayed in column A. How do a get the data to
speard over the columns?

Alternatively I have executed a script (which bascially consists of
two select statements).
The data that is brought back is returned into 2 grids. How can I
save the data from both grids into one excel file?

Thank you
Regards
CJ

 

 

[cjcfh00]

John / Erwin thanks for the replies:
 
I'll try and explain what I'm trying to do.
 
I'm trying to actually automatically(via a scheduled job) send a report to a user in excel format.
My first attempt was using  Reports Manager. I created the query, formated the report layout and then scheduled the report and chose the file format to be excel. On opening the report in excel, it seems as it the report was just pasted into excel, so if the user would like to sort on the informaiton, it wouldn't work well. Maybe I'm doing something wrong?
My second attempt to this was then using the Action palette.
I created a query e.g Query1, Right-clicked in the grid and choose "Save As, chose the excel format and then gave the file a name e.g File1.xls . I then created the second query e.g.Query2 Right-clicked in the grid and choose "Save As, chose the excel format and then gave the file a name e.g File2.xls. I then scheduled the both Export Datasets and then created a mail that would then attach both files (File1.xls and File2.xls) in the email notification... and this worked well. However, the user wants one excel file e.g CombinedFile.xls with the data of File1.xls to be on the first worksheet and the data of File2.xls to be on the second worksheet.
 
John, I tried what you suggested below and it worked however how am I going to save the file if I would like to do this via a scheduled job?
 
I hope what I've explained makes sense? Any assistance / suggestions?

--- On Wed, 10/1/08, Erwin Rollauer <erwin.rollauer@...> wrote:

From: Erwin Rollauer <erwin.rollauer@...>
Subject: RE: [toad] Action Palette, Execute script
To: toad@...
Date: Wednesday, October 1, 2008, 1:45 PM








This works very well in 9.6.  John’s directions are also right on.
 
One thing to check. If you want the entire grid selected make sure that EXPORT SELECT ROWS is NOT checked.
 
PS to John.
How about changing the wording to Export ONLY selected rows. It did not make it obvious to me that unchecking would select all. But then I probably don’t read all the words all the time.
 
Erwin Rollauer, ISR, McGill, 514 398-5023 ex 00626


From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of John Dorlon
Sent: Wednesday, October 01, 2008 9:12 AM
To: toad@yahoogroups. com
Subject: RE: [toad] Action Palette, Execute script
 
1)       If the data is returned from your query in a single column, it will go to the spreadsheet that way.   If you want the result to go to excel as a single row with multiple columns, you need to write your query that way.
2)      Right-click in the one grid and choose "Save As".   Use the "Excel Instance" export option and proceed with the export.    Leave excel open.
3)      Right-click in the next grid repeat step 2.  Toad will make a new sheet in excel and send the next dataset to it.
 
-John
 
 


From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of cjcfh00
Sent: Wednesday, October 01, 2008 6:51 AM
To: toad@yahoogroups. com
Subject: [toad] Action Palette, Execute script
 



Hi,

I'm hoping you someone can assist.

On using the action palette, on the Misc tab, the execute script
option.
I created a execute script. On going to properties, I've specified
the script that should run under the scripts heading.

Under the output heading I've selected use single file. In the
directory field I've specified the xls file that I would like,
however after executing the script the data is exported to the file,
all the data is displayed in column A. How do a get the data to
speard over the columns?

Alternatively I have executed a script (which bascially consists of
two select statements).
The data that is brought back is returned into 2 grids. How can I
save the data from both grids into one excel file?

Thank you
Regards
CJ
 
 














     

[John Dorlon]

OK, now I understand.    Unfortunately, there is no way to completely automate what you are after.

You can use the export dataset action to save data to an excel file, and you can even do more than one dataset per action, but as you have found it creates one file per dataset.   The Excel Instance format can write multiple datasets to one instance of excel, but when that export format was written, it was intended as more of an interactive thing, so it doesn't save the file for you.

You could save both datasets to a MS Access file, I don't know if that would help you or not.

From: toad@... [mailto:toad@...] On Behalf Of Cj Hendricks
Sent: Wednesday, October 01, 2008 9:10 AM
To: toad@...
Subject: RE: [toad] Action Palette, Execute script

John / Erwin thanks for the replies:

I'll try and explain what I'm trying to do.

I'm trying to actually automatically(via a scheduled job) send a report to a user in excel format.
My first attempt was using  Reports Manager. I created the query, formated the report layout and then scheduled the report and chose the file format to be excel. On opening the report in excel, it seems as it the report was just pasted into excel, so if the user would like to sort on the informaiton, it wouldn't work well. Maybe I'm doing something wrong?
My second attempt to this was then using the Action palette.
I created a query e.g Query1, Right-clicked in the grid and choose "Save As, chose the excel format and then gave the file a name e.g File1.xls . I then created the second query e.g.Query2 Right-clicked in the grid and choose "Save As, chose the excel format and then gave the file a name e.g File2.xls. I then scheduled the both Export Datasets and then created a mail that would then attach both files (File1.xls and File2.xls) in the email notification.... and this worked well. However, the user wants one excel file e.g CombinedFile.xls with the data of File1.xls to be on the first worksheet and the data of File2.xls to be on the second worksheet.

John, I tried what you suggested below and it worked however how am I going to save the file if I would like to do this via a scheduled job?

I hope what I've explained makes sense? Any assistance / suggestions?

--- On Wed, 10/1/08, Erwin Rollauer <erwin.rollauer@...> wrote:
From: Erwin Rollauer <erwin.rollauer@...>
Subject: RE: [toad] Action Palette, Execute script
To: toad@...
Date: Wednesday, October 1, 2008, 1:45 PM
This works very well in 9.6.  John’s directions are also right on.

One thing to check. If you want the entire grid selected make sure that EXPORT SELECT ROWS is NOT checked.

PS to John.
How about changing the wording to Export ONLY selected rows. It did not make it obvious to me that unchecking would select all. But then I probably don’t read all the words all the time.

Erwin Rollauer, ISR, McGill, 514 398-5023 ex 00626
From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of John Dorlon
Sent: Wednesday, October 01, 2008 9:12 AM
To: toad@yahoogroups. com
Subject: RE: [toad] Action Palette, Execute script


1)       If the data is returned from your query in a single column, it will go to the spreadsheet that way.   If you want the result to go to excel as a single row with multiple columns, you need to write your query that way.

2)      Right-click in the one grid and choose "Save As".   Use the "Excel Instance" export option and proceed with the export.    Leave excel open.

3)      Right-click in the next grid repeat step 2.  Toad will make a new sheet in excel and send the next dataset to it.

-John


From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of cjcfh00
Sent: Wednesday, October 01, 2008 6:51 AM
To: toad@yahoogroups. com
Subject: [toad] Action Palette, Execute script

Hi,

I'm hoping you someone can assist.

On using the action palette, on the Misc tab, the execute script
option.
I created a execute script. On going to properties, I've specified
the script that should run under the scripts heading.

Under the output heading I've selected use single file. In the
directory field I've specified the xls file that I would like,
however after executing the script the data is exported to the file,
all the data is displayed in column A. How do a get the data to
speard over the columns?

Alternatively I have executed a script (which bascially consists of
two select statements).
The data that is brought back is returned into 2 grids. How can I
save the data from both grids into one excel file?

Thank you
Regards
CJ

[cjcfh00]

thanks for the reply and suggestion.
 
CJ

--- On Wed, 10/1/08, John Dorlon <john.dorlon@...> wrote:

From: John Dorlon <john.dorlon@...>
Subject: RE: [toad] Action Palette, Execute script
To: "toad@..." <toad@...>
Date: Wednesday, October 1, 2008, 2:19 PM








OK, now I understand.    Unfortunately, there is no way to completely automate what you are after.
 
You can use the export dataset action to save data to an excel file, and you can even do more than one dataset per action, but as you have found it creates one file per dataset.   The Excel Instance format can write multiple datasets to one instance of excel, but when that export format was written, it was intended as more of an interactive thing, so it doesn't save the file for you.
 
You could save both datasets to a MS Access file, I don't know if that would help you or not.
 


From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of Cj Hendricks
Sent: Wednesday, October 01, 2008 9:10 AM
To: toad@yahoogroups. com
Subject: RE: [toad] Action Palette, Execute script
 








John / Erwin thanks for the replies:

 

I'll try and explain what I'm trying to do.

 

I'm trying to actually automatically( via a scheduled job) send a report to a user in excel format.

My first attempt was using  Reports Manager. I created the query, formated the report layout and then scheduled the report and chose the file format to be excel. On opening the report in excel, it seems as it the report was just pasted into excel, so if the user would like to sort on the informaiton, it wouldn't work well. Maybe I'm doing something wrong?

My second attempt to this was then using the Action palette.
I created a query e.g Query1, Right-clicked in the grid and choose "Save As, chose the excel format and then gave the file a name e.g File1.xls . I then created the second query e.g.Query2 Right-clicked in the grid and choose "Save As, chose the excel format and then gave the file a name e.g File2.xls. I then scheduled the both Export Datasets and then created a mail that would then attach both files (File1.xls and File2.xls) in the email notification. ... and this worked well. However, the user wants one excel file e.g CombinedFile. xls with the data of File1.xls to be on the first worksheet and the data of File2.xls to be on the second worksheet.

 

John, I tried what you suggested below and it worked however how am I going to save the file if I would like to do this via a scheduled job?

 

I hope what I've explained makes sense? Any assistance / suggestions?

--- On Wed, 10/1/08, Erwin Rollauer <erwin.rollauer@ mcgill.ca> wrote:

From: Erwin Rollauer <erwin.rollauer@ mcgill.ca>
Subject: RE: [toad] Action Palette, Execute script
To: toad@yahoogroups. com
Date: Wednesday, October 1, 2008, 1:45 PM




This works very well in 9.6.  John’s directions are also right on.
 
One thing to check. If you want the entire grid selected make sure that EXPORT SELECT ROWS is NOT checked.
 
PS to John.
How about changing the wording to Export ONLY selected rows. It did not make it obvious to me that unchecking would select all. But then I probably don’t read all the words all the time.
 
Erwin Rollauer, ISR, McGill, 514 398-5023 ex 00626


From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of John Dorlon
Sent: Wednesday, October 01, 2008 9:12 AM
To: toad@yahoogroups. com
Subject: RE: [toad] Action Palette, Execute script
 
1)       If the data is returned from your query in a single column, it will go to the spreadsheet that way.   If you want the result to go to excel as a single row with multiple columns, you need to write your query that way.
2)      Right-click in the one grid and choose "Save As".   Use the "Excel Instance" export option and proceed with the export.    Leave excel open.
3)      Right-click in the next grid repeat step 2.  Toad will make a new sheet in excel and send the next dataset to it.
 
-John
 
 


From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of cjcfh00
Sent: Wednesday, October 01, 2008 6:51 AM
To: toad@yahoogroups. com
Subject: [toad] Action Palette, Execute script
 




Hi,

I'm hoping you someone can assist.

On using the action palette, on the Misc tab, the execute script
option.
I created a execute script. On going to properties, I've specified
the script that should run under the scripts heading.

Under the output heading I've selected use single file. In the
directory field I've specified the xls file that I would like,
however after executing the script the data is exported to the file,
all the data is displayed in column A. How do a get the data to
speard over the columns?

Alternatively I have executed a script (which bascially consists of
two select statements).
The data that is brought back is returned into 2 grids. How can I
save the data from both grids into one excel file?

Thank you
Regards
CJ