Loading ...
Sorry, an error occurred while loading the content.

Automated Data Export/File Creation

Expand Messages
  • sfultz77@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
    Message 1 of 4 , Jan 3, 2013
    • 0 Attachment
      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
      Message 2 of 4 , Jan 3, 2013
      • 0 Attachment
        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.  


        Also try googling Toad Automation Designer for some more tips.

        Ed
        [TeamT]



        On Jan 3, 2013, at 3:04 PM, sfultz77@... wrote:



        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
        Message 3 of 4 , Jan 3, 2013
        • 0 Attachment

          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: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of sfultz77@...
          Sent: Thursday, January 03, 2013 3:05 PM
          To: toad@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

        • sfultz77@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
          Message 4 of 4 , Jan 3, 2013
          • 0 Attachment



            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


            From: Ed Klinger <edk@...>
            To: toad@yahoogroups.com
            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

            Also try googling Toad Automation Designer for some more tips.

            Ed
            [TeamT]



            On Jan 3, 2013, at 3:04 PM, sfultz77@... wrote:



            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





          Your message has been successfully submitted and would be delivered to recipients shortly.