SYSDATE in output file name

strujill0 · October 30, 2013, 10:27am

I’m trying to use the Automation Designer tool to create a dynamic filename that has the query date range (sysdate-7 and sysdate) in the file name. What I want to see is something like ‘samplefilename_2013-10-23_to_2013-10-30.xls’ , but I’m running into some issues with the first date returning a literal rather than the actual date. In the action file name field I’m putting the following:

samplefilename_%sysdate-7%to%sysdate%.xls

And this is what is being returned when the file is created:

samplefilename_%sysdate-7%_to_2013-10-30.xls

**Is there anywhere to get sysdate-7 to return in the filename as a true date? **

reference article:

https://support.quest.com/SolutionDetail.aspx?ID=57716

Using Toad for Oracle v11.6.0.43

THANKS IN ADVANCE!!!

mstaszew · October 30, 2013, 1:35pm

I can't think of any way to accomplish this in Toad. You might be able to write a BAT script that can change the filename to what you want. Run your action and save the results to a predefined filename and have a second action that
follows which runs a BAT script to rename the file.

Michael

On 10/30/2013 01:28 PM, strujill0 wrote:

SYSDATE in output file name

Thread created by strujill0
I'm trying to use the Automation Designer tool to create a dynamic filename that has the query date range (sysdate-7 and sysdate) in the file name. What I want to see is something like 'samplefilename_2013-10-23_to_2013-10-30.xls' , but I'm running into
some issues with the first date returning a literal rather than the actual date. In the action file name field I'm putting the following:

samplefilename_%sysdate-7%to%sysdate%.xls

And this is what is being returned when the file is created:

samplefilename_%sysdate-7%_to_2013-10-30.xls

**Is there anywhere to get sysdate-7 to return in the filename as a true date? **

reference article:

https://support.quest.com/SolutionDetail.aspx?ID=57716

Using Toad for Oracle v11.6.0.43

THANKS IN ADVANCE!!!!

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.

Dennis_Paulus · October 30, 2013, 1:41pm

Hi,

There may be easier ways to do this, but here’s a quick example I came up with. You’ll probably want to modify it to your specific needs, but this might
give you an idea of how it could be done.

Add an ‘Execute Script’ action like below. Modify the script as wanted - basically you want to output the date to a file. This will create an output.txt
file in the directory I specified. Note: The key here was I turned feedback off, so it only displayed the date in the output file.
Add a ‘File Interator’ action like below, pointing to the output file created in Step 1.
Created a new User Variable called ‘Filename’ and reference the %File Iterator 1% variable, in the filename itself.
Under File Iterator, you can put whatever you want. You could reference the variable created in #3 like I did, but you probably want to do something
else with it, but the concept would be the same for other actions. The key is just to put it under the File Iterator, so you can reference that variable.

Here’s my outputted filename:

Hope this helps.

Dennis

mstaszew · October 30, 2013, 1:46pm

I was just testing Dennis. He passed. Listen to him.

On 10/30/2013 04:35 PM, Michael Staszewski wrote:

Re: SYSDATE in output file name

Reply by Michael Staszewski
I can't think of any way to accomplish this in Toad. You might be able to write a BAT script that can change the filename to what you want. Run your action and save the results to a predefined filename and have a second action
that follows which runs a BAT script to rename the file.

Michael

On 10/30/2013 01:28 PM, strujill0 wrote:

SYSDATE in output file name

Thread created by strujill0
I'm trying to use the Automation Designer tool to create a dynamic filename that has the query date range (sysdate-7 and sysdate) in the file name. What I want to see is something like 'samplefilename_2013-10-23_to_2013-10-30.xls' , but I'm running into
some issues with the first date returning a literal rather than the actual date. In the action file name field I'm putting the following:

samplefilename_%sysdate-7%to%sysdate%.xls

And this is what is being returned when the file is created:

samplefilename_%sysdate-7%_to_2013-10-30.xls

**Is there anywhere to get sysdate-7 to return in the filename as a true date? **

reference article:

https://support.quest.com/SolutionDetail.aspx?ID=57716

Using Toad for Oracle v11.6.0.43

THANKS IN ADVANCE!!!!

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.

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.

strujill0 · October 30, 2013, 3:39pm

Dennis - Thank you!! Your instructions with screenshots are perfect.

damir.vadas_250 · October 31, 2013, 1:21am

Hi,

This example from Dennis Paulus is amazing.Before this I thought it is impossible to make this, but now I know it is possible

However, still think that some kind of better support for date (and it’s variables) in Toad should be implemented in next releases, because date use is really in huge number of automations.

Brg,

Damir

Dennis_Paulus · October 31, 2013, 6:52am

Glad this was helpful. And I agree that it would be nice to have some added functionality around this. Perhaps an action that would just run a select statement, instead of having to run as a script. Then the results could be outputted directly to a variable, instead of having to create a temporary file and reading from that - eliminating a step. Of course, that brings up other possibilities around selecting multiple columns and multiple rows, etc.

If you feel strongly about this, I would recommend adding it to Idea Pond to have other users vote on the idea. The more interest for it, the more likely something would be implemented.

toadfororacle.ideascale.com

Thanks,

Dennis

damir.vadas_250 · October 31, 2013, 7:51am

Placed:

www.toadworld.com/…/date_variables_for_file_and_other_use_in_automation.aspx

Brg

Damir

pbutler · June 23, 2014, 11:41am

Your detailed description with images was very helpful, but I’m still having problems when exporting the file. I’m using Toad for Oracle Xpert, version 11.5.1.2.

It looks like “Create File1” is only used to create the file name. I didn’t do that, since I’m exporting a query, which will create the file.

output.txt=201406_20140623 (term + date). If I wanted to use the date by itself, I could put %sysdate% and it would use the short format set in control panel/Clock, Language and Region/Region and Language - Change the date, time, or number format

In Export Dataset2, I’ve tried both %Filename%.csv and Remedy_Student_%File Iterator1%.csv and that’s what the filenames end up being - not replacing the value of output.txt in the filename.

Thanks in advance.

pbutler · June 23, 2014, 11:44am

I had included a picture, which doesn’t appear. I am trying to schedule a job which will export data to an excel file and then email it.

Execute Script1 [Execute script text]

File Iterator1 [z:\remedy\output.txt]

(the next 2 are under File Iterator1)

Create Variable1 [Filename=z:\remedy\remedy_student_%File Iterator1%.csv]

Export Dataset2 [Delimited Text: select…]