Automated Data Export/File Creation

[s***@yahoo.com]

Hi;
 
On a scale of 1 to 5, with 5 being the most
knowledgeable, I am a 1.5 as a TOAD user. I work for a small shop as a Report
Writer and create queries and views for use in Crystal Reports, mostly.  Oh
and we do not have a DB Programmer on
staff; the db we work with is owned by our Software vendor, who tend to be unhelpful with this type of thing.  We have two versions of TOAD, 10.6.13 and 11.0.0.116.
 
Recently, the contracted with a third-party vendor
to do some work for us and we need to provide them with a recurring file of
customers.  The text file must be
formatted in as pipe delimeted and the file name must use this convention:  THC_MEMBERS_YYYY-MM-DD-HH-MM-SS.txt, where
datetimestamp changes on every run and is in the specified format in the file
name (THC_MEMBERS_2013-01-03-14-51-47.txt).
 
The query has already been created and the data
exported manually and all is fine, however, I want to automate and schedule
this function.  In order to do so, I need
step-by-step instructions, including how to save the file with the above
specified naming convention. It probably needs a variable, but I don’t know how
to do that. I started looking at the Automation Designer, but quickly lost my
way.
 
Is there anybody who can point me to an easy to
use/understand set of documents that will help me figure this out, please???
 
Thank you in advance,
Sandi

[Ed Klinger]

Sandi,

I don't think you can get the filename to that specific format you are mentioning but you can get it close. In the export dialog where you put the path to the file name right click in that text box and you should see "Variables…" From there you can insert these variables into the file name.

The closest I think you will get is THC_MEMBERS_%DATEFILE%-%TIMEFILE%.txt which will equate to THC_MEMBERS_2013_1_3-50510.txt which is THC_MEMBERS_YYYY-MM_DD-HHMMSS.txt. It looks like it does not pad 0's into the DD,MM or HH part which is a bummer.

On the export screen you can then click the little camera looking icon on the bottom right this is where you will create an automation action. After that go to the menu Utilities - Automation Designer where you can set up a scheduled event for this action.

Here is a link that talks about the automation designer.

http://www.toadworld.com/Blogs/tabid/67/EntryId/601/Task-Automation-in-Toad%C2%AE-Saving-You-Hours.aspx
https://support.quest.com/SolutionDetail.aspx?id=SOL57716

Also try googling Toad Automation Designer for some more tips.

Ed
[TeamT]

Hi;
On a scale of 1 to 5, with 5 being the most knowledgeable, I am a 1.5 as a TOAD user. I work for a small shop as a Report Writer and create queries and views for use in Crystal Reports, mostly. Oh…and we do not have a DB Programmer on staff; the db we work with is owned by our Software vendor, who tend to be unhelpful with this type of thing. We have two versions of TOAD, 10.6.13 and 11.0.0.116.
Recently, the contracted with a third-party vendor to do some work for us and we need to provide them with a recurring file of customers. The text file must be formatted in as pipe delimeted and the file name must use this convention: THC_MEMBERS_YYYY-MM-DD-HH-MM-SS.txt, where datetimestamp changes on every run and is in the specified format in the file name (THC_MEMBERS_2013-01-03-14-51-47.txt).
The query has already been created and the data exported manually and all is fine, however, I want to automate and schedule this function. In order to do so, I need step-by-step instructions, including how to save the file with the above specified naming convention. It probably needs a variable, but I don’t know how to do that. I started looking at the Automation Designer, but quickly lost my way.
Is there anybody who can point me to an easy to use/understand set of documents that will help me figure this out, please???
Thank you in advance,
Sandi

[s***@yahoo.com]

________________________________
Thank you, Ed & Dennis;
You both answered me at the same time with, almost, the same directions!  That's great service! :-)
I actually started this morning by reading that blog listed below, but it didn't address my variable issue, at least in a way that I could figure it out.  The directions you both gave me are pretty clear, so I'll give them a whirl back at the office tomorrow, and hope the vendor will accept the change.
Thank you, again!
Sandi
Sent: Thursday, January 3, 2013 5:19 PM
Subject: Re: [toad] Automated Data Export/File Creation
 
Sandi,
I don't think you can get the filename to that specific format you are mentioning but you can get it close.  In the export dialog where you put the path to the file name right click in that text box and you should see "Variables
"  From there you can insert these variables into the file name.
The closest I think you will get is THC_MEMBERS_%DATEFILE%-%TIMEFILE%.txt  which will equate to THC_MEMBERS_2013_1_3-50510.txt which is THC_MEMBERS_YYYY-MM_DD-HHMMSS.txt.  It looks like it does not pad 0's into the DD,MM or HH part which is a bummer.
On the export screen you can then click the little camera looking icon on the bottom right this is where you will create an automation action.  After that go to the menu Utilities - Automation Designer where you can set up a scheduled event for this action.
Here is a link that talks about the automation designer.  
http://www.toadworld.com/Blogs/tabid/67/EntryId/601/Task-Automation-in-Toad%C2%AE-Saving-You-Hours.aspx
https://support.quest.com/SolutionDetail.aspx?id=SOL57716
Also try googling Toad Automation Designer for some more tips.
Ed
[TeamT]

Hi;
 
On a scale of 1 to 5, with 5 being the most knowledgeable, I am a 1.5 as a TOAD user. I work for a small shop as a Report Writer and create queries and views for use in Crystal Reports, mostly.  Oh
and we do not have a DB Programmer on staff; the db we work with is owned by our Software vendor, who tend to be unhelpful with this type of thing.  We have two versions of TOAD, 10.6.13 and 11.0.0.116.
 
Recently, the contracted with a third-party vendor to do some work for us and we need to provide them with a recurring file of customers.  The text file must be formatted in as pipe delimeted and the file name must use this convention:  THC_MEMBERS_YYYY-MM-DD-HH-MM-SS.txt, where datetimestamp changes on every run and is in the specified format in the file name (THC_MEMBERS_2013-01-03-14-51-47.txt).
 
The query has already been created and the data exported manually and all is fine, however, I want to automate and schedule this function.  In order to do so, I need step-by-step instructions, including how to save the file with the above specified naming convention. It probably needs a variable, but I don’t know how to do that. I started looking at the Automation Designer, but quickly lost my way.
 
Is there anybody who can point me to an easy to use/understand set of documents that will help me figure this out, please???
 
Thank you in advance,
Sandi

[Dennis Paulus]

Hi Sandi,
This will get you close to what you want, although the filename is not exact. Not sure how firm your requirements were for the filename format, so thought I'd send you this just in case.

1) Go to Options > Variables and add a new User Variable - let's say you call it 'MY_FILENAME' with a value of 'THC_MEMBERS_%DATEFILE%_%TIMEFILE%'.

2) In Automation Designer, create a new App and add the action 'Export Dataset'. Set the following parameters

Export Format: Delimited Text
Output: File: C:\Temp\%MY_FILENAME%.txt (note you can r-click on the field and choose variables as well)
Delimiter Character: Pipe
You can turn off column headers if you don't want them to appear as well

3) On the Dataset tab, enter your query (eg. select * from scott.emp)

4) Apply and run the action.

The result will be a pipe delimited file as you expected, but unfortunately the filename will be like 'THC_MEMBERS_2013_1_3_51146 PM.txt', because we are using the pre-canned system variables.

I also can send you another way of doing this, which is a bit more complicated which uses pl/sql to create the file you want. The filename will in the format you want, but the location of the file is saved to the database server or to a share, which may or may not work for you either. If you're interested in something like that, please email me directly and I'd be happy to share it with you.

Thanks,
Dennis

From: ***@yahoogroups.com [mailto:***@yahoogroups.com] On Behalf Of ***@yahoo.com
Sent: Thursday, January 03, 2013 3:05 PM
To: ***@yahoogroups.com
Subject: [toad] Automated Data Export/File Creation


Hi;

On a scale of 1 to 5, with 5 being the most knowledgeable, I am a 1.5 as a TOAD user. I work for a small shop as a Report Writer and create queries and views for use in Crystal Reports, mostly. Oh
and we do not have a DB Programmer on staff; the db we work with is owned by our Software vendor, who tend to be unhelpful with this type of thing. We have two versions of TOAD, 10.6.13 and 11.0.0.116.

Recently, the contracted with a third-party vendor to do some work for us and we need to provide them with a recurring file of customers. The text file must be formatted in as pipe delimeted and the file name must use this convention: THC_MEMBERS_YYYY-MM-DD-HH-MM-SS.txt, where datetimestamp changes on every run and is in the specified format in the file name (THC_MEMBERS_2013-01-03-14-51-47.txt).

The query has already been created and the data exported manually and all is fine, however, I want to automate and schedule this function. In order to do so, I need step-by-step instructions, including how to save the file with the above specified naming convention. It probably needs a variable, but I don’t know how to do that. I started looking at the Automation Designer, but quickly lost my way.

Is there anybody who can point me to an easy to use/understand set of documents that will help me figure this out, please???

Thank you in advance,
Sandi