How to copy only the latest file when files have a same name but different suffix with date and time stamp using TDP Automation?
Would the latest file always have today’s date?
Yes Greg. Thank You for looking into this.
You can copy the file using a variable set with todays date formatted to match the date part of the file name. You can use the Toad function To_char(Current_date(), ‘YYYYMMDD’), Oracle style function but will work when connected to SQL server as well. Just change the YYYMMDD to what you need like MMDDYYY or YYYY-MM-DD, etc. Then you can use the varaible call it TodaysDate to copy the file. Copy YourFileName[tag:TodaysDate]#*.csv, the wildcard * after the date will take care of any time portion after the date. You made need to do this by batch file and using run program to call your batch job and pass the [tag:TodaysDate]# variable into the DOS batch or just use Run Program to call the Copy comand and pass your path, file name, variable, wildcard and file extension on the argument line. Use double quotes if you have spaces in folder or file names.
Batch file example I call Copy2Current.bat:
Rem copy file to import file name using variable parameter %1 to pass the file sufffix
Copy R:\Admit_data%1*.xls P:\Projects\Medecision\Data\Import\ACM\Admit_data.xls
Rem File copied. Remove Rem from last line for production / put Rem before Exit command for testing to keep window open.
Rem Exit
I use the above to copy the file each time and rename it to a default name that my import step is looling for. Basically I just stip off the date/time.
Hello Greg, Thank you for step by step execution path.
As suggested, I created a variable, created a batch file and passed the argument to the run program, but received an error message as below. I’m new to TDP and really do not know how to correct this. Can you please advise/ Thank you
11:55 PM: Script_1.log: Setting up environment
– 12/15/2014 9:11:55 PM: Script_1.log: Script run by Toad Data Point 3.3.0.1526
– 12/15/2014 9:11:55 PM: Script_1.log: Build started
– 12/15/2014 9:11:55 PM: Script_1.log: Compiling Script
– 12/15/2014 9:11:57 PM: Script_1.log: Build Completed
– 12/15/2014 9:11:57 PM: Script_1.log: Begin execution script activities
– 12/15/2014 9:11:57 PM: Script_1.log: Connecting to TXSH6P.Lak.COM (LYANAMADALA), AMIOWN
– 12/15/2014 9:11:57 PM: Script_1.log: Variable “TodaysDate” set to ‘20141215’
– 12/15/2014 9:11:57 PM: Script_1.log: Start run program Run_1
– 12/15/2014 9:11:57 PM: Script_1.log: Run_1 - The specified executable is not a valid application for this OS platform.
– 12/15/2014 9:11:57 PM: Script_1.log: Failed
Can you post a screenshot on how you have filled out the values for the Run Activity?
Debbie, there was a problem with how I passed the argument. I fixed it and the copy worked fine even though there was a different error this time in the log file.
15/2014 10:55:50 PM: Script_1.log: Run_1 Error Message: CMD.EXE was started with the above path as the current directory.
– 12/15/2014 10:55:50 PM: Script_1.log: Run_1 Error Message: UNC paths are not supported. Defaulting to Windows directory.
This community is amazing ! Thanks a ton both of you!
I had the same issue. It likes mapped drives better than UNC paths but it is usally just a warning message and works anyhow. I still changed mine to mapped drives (I don’t like seeing error messages). Glad you got it working.