Determine datetime of file without Run Program task

I have a data file on a network file share. I need to determine the datetime of the file. This info is not in the file, so I need to get it from the file system. I have already written a .bat file that can do this, but here is the catch. The Run Program task does not like UNC file paths for the Run Directory parameter, it insists on a drive letter. When you publish an automation script to Toad Intelligence Server, it requires that all file paths are converted to UNC file paths. So when I publish a script with the Run Program task, it changes the Run Directory path to UNC format, and the Run Program task fails.

So here is the challenge. Can anyone tell me a way to determine the datetime of a file on a network file share without using the Run Program task? I don’t see any other task that would let me get that info, and using Oracle (dbms_lob.fileopen) is not an alternative option.

Hi hharwood,

Here’s RunProgram with Powershell version that accepts UNC path:

Program to run: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Arguments: -command "(Get-Item “//hostname/folder/structure/file.txt”).LastWriteTime.ToString(“yyyy-MM-dd HH:mm:ss”)"

But then you are left with another challenge - how do you get the result of this in toad? I guess you would have to adjust this command to export the result into .csv file and then in another step in Toad automation script, using Import to table step where you import this csv file so you can then query it in 3rd step SELECT top 1 result from myTempTable


Thanks, Martin, did not realize that powershell was available from my notebook. So yes, calling it on the C drive should work from both my notebook and the server.

As for getting the result into TDP, I already had that worked out. Here is my original .bat file that Run Program was calling:

FOR %%a IN (\FileServer\MyShare\DataFile.csv) DO SET FileDate=%%~ta
ECHO FileDateTime
ECHO %FileDate%

I then used an Import task to pull it into a variable. I’ll just have to suss out the powershell part to capture the data to the csv file.

Here is the Arguments parameter I ended up crafting:

-command “& {(Get-Item \server\fileshare\DataFile.csv).LastWriteTime.ToString(“MM-dd-yyyy hh:mm:ss tt”) > “\server\fileshare\FileDate_Save.csv”}”

And then I just import the value.

My you boys are clever[H][Y] Have a good weekend!