How to get file duration

Hi ,
I want to scan a folder after every 8 hours and want my robot to append the name, date created and Duration of only newly arrived file in a excel. Is there any method to do this?

I would suggest scheduling the job to run every 8 hours per your requirement, then all you need to do is make the process check the folder for any files that is not in the excel file already.

Steps to take would be to:
—Read existing excel file and store in datatable - Excel Scope with Read Range
—Get Files from folder - .net code => System.IO.Directory.GetFiles(foldername,".",System.IO.SearchOption.AllDirectories)
—Compare list of files with existing datarows
—If not in table, Add Data Row activity

pseudocode:

Excel Scope
   Read Range: store to datatable `I'll name exitingTable`

Assign: files = System.IO.Directory.GetFiles(foldername,"*.*",System.IO.SearchOption.AllDirectories)

If activity: condition: existingTable.AsEnumerable.Where(Function(r) Directory.GetFiles("","",system.IO.SearchOption.AllDirectories).Any(Function(f) System.IO.Path.GetFileName(f) = r("Filename").ToString.Trim And System.IO.Directory.GetCreationTime(f) = Convert.ToDate(r("CreationTime")) ) ).Count = 0
    Add Data Row activity: ArrayRow => {System.IO.Path.GetFileName(f),System.IO.Directory.GetCreationTime(f)}

By the way, if you have the System.IO namespace, you can leave that part off if you want.

Also, you may need to make adjustments as you code this, like if your Date is in a certain format, then Convert.ToDate() might not work. The Add Data Row (ArrayRow) should be in the order as your table; I am assuming you have 2 columns so I used a 2 item array for filename and creationtime.

I hope this gets you in the right direction.

Regards.

1 Like

I made a mistake in the above logic, so let me make the correction. You would need to store the files that are not in the existing table to an array, then loop through them to Add the rows of data…

pseudocode:

Excel Scope
   Read Range: store to datatable `I'll name exitingTable`

Assign: files = System.IO.Directory.GetFiles(foldername,"*.*",System.IO.SearchOption.AllDirectories)

Assign: newFiles = files.Where(Function(f) Not existingTable.AsEnumerable.Any(Function(r) System.IO.Path.GetFileName(f) = r("Filename").ToString.Trim And System.IO.Directory.GetCreationTime(f) = Convert.ToDate(r("CreationTime") ) ).ToArray

For each f In newFiles `TypeArgument as String`
    Add Data Row activity: ArrayRow => {System.IO.Path.GetFileName(f),System.IO.Directory.GetCreationTime(f)}

Excel Scope
    Write Range: existingTable `with new rows added`

Hopefully, that’s slightly cleaner…

Regards. @Atul_Rai

1 Like

Thanks @ClaytonM

I will follow the suggested steps but It will only give me the names of the file. I have all video or audio files and I want want my automation to write the Duration of the file. Can you suggest me how to achieve this?

I have never done this. I suggest looking through these results: https://www.google.com/search?rlz=1C1GGRV_enUS751US751&ei=AoWjXLyACa_l_QacuqvQBw&q=get+duration+of+audio+file+.net&oq=get+duration+of+audio+file+.net&gs_l=psy-ab.3..0i8i30.3852.8224..8445...0.0..0.95.1247.16…0…1…gws-wiz…35i304i39j0i8i7i30j0i8i13i30j35i39j0i7i5i30.I9ha1U7ikXk

I’ll get back to you if I figure out the code needed for this. Essentially, you would just add the duration into the condition next to CreationTime in my above examples.

Regards.

Unable to understand it

I found powershell code that can return the duration of a file. I’ll create a workflow quick that utilizes this with the other steps you were needing above, so you can understand it better. It will be a few hours before I can upload it (cause I’m busy) so be patient.

Thanks.

Thanks @ClaytonM

Hi @ClaytonM

I have found the duration for video file in Properties->Details tab. Is there any namespace available to get the information from properties window.
image

Guys, I have created a snippet that invokes a powershell script to retrieve the file duration
GetDurationOfFile.xaml (9.8 KB)

Please pass the value in the input arguments:

Poewrshell script reference:
https://social.technet.microsoft.com/Forums/lync/en-US/bad2dbb1-5deb-48b8-8f8c-45e2b353dba0/how-do-i-get-video-file-duration-in-powershell-script?forum=winserverpowershell

Thanks a lot @Dominic

I will use your xaml to create my desired workflow.