RE: Automated Data Export/File Creation

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: [email protected] [mailto:[email protected]] On Behalf Of [email protected]
Sent: Thursday, January 03, 2013 3:05 PM
To: [email protected]
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