Pulling the most recent file out of a directory

[SWW]

Hey all,

I have created a process that uses a control job which directs a file to be copied to a landing area, unzipped, parts copied to main folder, process the data from the main folder, clear the landing area and main folder, and archive the zip file. I have code to map to drives that contain the file I need(datafile1). The problem is there are multiple files in that folder and I only need the most recent file.

I read in some other discussions about reading Timestamps of a file. How can I tell DS to read the Timestamp and pull the most recent file?

Any thoughts or suggestions!!

Thanks

[ketfos]

Hi,
you can have before job routine in datastage where you can use unix command to list files in the folder, sorted by time modified (-t)
ls -t > temp
tail -1 temp

this will output the file with latest timestamp.

Ketfos

[Alexandre]

ls -t > temp
tail -1 temp

You can have the last line using pipes instead of using a temporary file :

Code: Select all

ls -t | tail -1

Alexandre.

[chulett]

However, if you look at the original post you'll see they are (hopefully) on a Windows server. So, a Windows solution would be something similar with 'dir':

Code: Select all

dir /b /od

This gets you a 'bare' listing (just filenames) sort in date order ascending. I'm not aware of any way to reverse the listing, so you'd need to do something like capture this output using DSExecute, see how many entries are in the returned dynamic array and grab the last one.

[danjm]

I think you can reverse the order by placing a hypen before the paramater you want reversed as:

dir /b /-o /d

[danjm]

actually the syntax would be more like:

dir /b/o-d

...for what you are trying to achieve.. I think.

[kcbland]

Since executing a system command returns screen output into an array, to process the array in reverse order is simple. Just start at the end of the array and work backwards.

Code: Select all

Call DSExecute("NT", "dir /b /o /d \overthere\thesefiles", ScreenOutput, ExitCode)
NumberOfLines=DCOUNT(ScreenOutput,@AM)
For Line=NumberOfLines To 1 STEP -1
   Filename=TRIM(ScreenOutput<Line>)
   If Filename # "" Then 
...
...
...
   End
Next Line

[SWW]

Guy,

Thanks for all your help. Below is the code I'm using to try and pull the latest file. I'm not very good with loops so don't be to hard on me.

Steve


*Loop through Source Parm Dir to pull the most recent file
Call DSLogInfo("Setting up Dynamic Array Loop to pull the most recent file from Source file directory", "Job Control")

* This is going to the mapped drive and sorting the zip files

Call DSExecute("NT", "K:\UnitedTote\ dir /b /o /d *.zip", ScreenOutput, ExitCode)

NumberOfLines = DCOUNT(ScreenOutput,@AM)

for i =NumberOfLines to 1 STEP -1
Filename = TRIM(ScreenOutput<i>)

If Filename # "" THEN
*Copy file to a local drive
Call DSLogInfo("Copying files by calling batch script via DSExecute command...", "Job Control")
Call DSLogInfo("Executing script to copy files from source data dir to copy to dir ", "Job Control")

* THIS IS WHERE I'M TRYING PLACE THE FILENAME IN THE COPY CMD
*WHERE K:\UnitedTote IS THE MAPPED DRIVE AND
* Filename IS SUPPOSED TO BE THE LATEST FILE.

Call DSExecute("NT","copy K:\UnitedTote\Filename # " :sCOPY_TO_DIR_Parm, msgs, rtncd)
Call DSLogInfo("Executing command - ":sBATCH_DIR_Parm:"copy_RGS_files.bat ", "Job Control")
Call DSLogInfo("msgs is ":msgs, "Job Control")
Call DSLogInfo("rtncd is ":rtncd, "Job Control")
End
next i


Any suggestions!!!

[kcbland]

Try using this:

Code: Select all

*Loop through Source Parm Dir to pull the most recent file
      Call DSLogInfo("Setting up Dynamic Array Loop to pull the most recent file from Source file directory", "Job Control")

* This is going to the mapped drive and sorting the zip files

      Call DSExecute("NT", "K:\UnitedTote\ dir /b /o /d *.zip", ScreenOutput, ExitCode)

      NumberOfLines = DCOUNT(ScreenOutput,@AM)

      for i =NumberOfLines to 1 STEP -1
         Filename = TRIM(ScreenOutput)

         If Filename # "" THEN
*Copy file to a local drive
            Call DSLogInfo("Copying files by calling batch script via DSExecute command...", "Job Control")
            Call DSLogInfo("Executing script to copy files from source data dir to copy to dir ", "Job Control")

* THIS IS WHERE I'M TRYING PLACE THE FILENAME IN THE COPY CMD
*WHERE K:\UnitedTote IS THE MAPPED DRIVE AND
* Filename IS SUPPOSED TO BE THE LATEST FILE.
            CopyCommand = "copy K:\UnitedTote\":Filename:" ":sCOPY_TO_DIR_Parm
            Call DSLogInfo("Copy command: ":CopyCommand, "Info")
            Call DSExecute("NT",CopyCommand, msgs, rtncd)


            Call DSLogInfo("Executing command - ":sBATCH_DIR_Parm:"copy_RGS_files.bat ", "Job Control")

            Call DSLogInfo("msgs is ":msgs, "Job Control")
            Call DSLogInfo("rtncd is ":rtncd, "Job Control")
         End
      next i

[SWW]

Kenneth,

Here is the code I used. The parameter Filename is not being populated correctly. I listed the log messages after where they occurred. Seems it doesn't like something relating to the VOC.

Also is there some way to see what value Filename takes on each time. If I knew it was actually going to the mapped drive and pulling a value, that's half the battle.

Thanks for all your time.

Steve



*Loop through Source Parm Dir to pull the most recent file
Call DSLogInfo("Setting up Dynamic Array Loop to pull the most recent file from Source file directory", "Job Control")
* This is going to the mapped drive and sorting the zip files
Call DSExecute("NT", "K:\UnitedTote\ dir /b /o /d *.zip", ScreenOutput, ExitCode)
NumberOfLines = DCOUNT(ScreenOutput,@AM)
for i =NumberOfLines to 1 STEP -1
Filename = TRIM(ScreenOutput)
If Filename # "" THEN
*Copy file to a local drive
Call DSLogInfo("Copying files by calling batch script via DSExecute command...", "Job Control")
Call DSLogInfo("Executing script to copy files from source data dir to copy to dir ", "Job Control")
* THIS IS WHERE I'M TRYING PLACE THE FILENAME IN THE COPY CMD
*WHERE K:\UnitedTote IS THE MAPPED DRIVE AND
* Filename IS SUPPOSED TO BE THE LATEST FILE.
CopyCommand = "copy K:\UnitedTote\":Filename: " ":sCOPY_TO_DIR_Parm
Call DSLogInfo("Copy command: ":CopyCommand, "Info")

THIS IS THE MESSAGE IN THE LOG
Message:
CTRL_JOB_RGS..JobControl (Info): Copy command: copy K:\UnitedTote\'K:\UnitedTote\' is not recognized as an internal or external command,
operable program or batch file.
D:\Tote_Raw\RGS\RGS_Zip\

Call DSExecute("NT",CopyCommand, msgs, rtncd)
Call DSLogInfo("Executing command - ":sBATCH_DIR_Parm:"copy_RGS_files.bat ", "Job Control")
Call DSLogInfo("msgs is ":msgs, "Job Control")


THIS IS THE MESSAGE IN THE LOG
Message:
CTRL_JOB_RGS..JobControl (Job Control): msgs is The syntax of the command is incorrect.
Verb "OPERABLE" is not in your VOC.
Verb "D:\TOTE_RAW\RGS\RGS_ZIP\" is not in your VOC.




Call DSLogInfo("rtncd is ":rtncd, "Job Control")
End
next i

[ray.wurlod]

You may have extra line terminators in ScreenOutput. These will have been translated into field marks (@FM). Try:

Code: Select all

Filename = Trim(Convert(@FM,"",ScreenOutput))