In this very simple article, I'm going to share with you a script that I developed to meet a very common need in the daily lives of those who create courses and training, which is to create a spreadsheet or CSV file, containing the list of videos, size and duration of each video, in order to maintain the folder structure to be able to calculate the total duration of the videos and also to be able to add the total duration per directory and per file, to make this available on a course platform, for example.
For this example, I used my course files on Database on Azure, which are already separated by separate directories for each module:
But notice that there are 2 folders within the course directory, called "General" and "Marketing", which are promotional videos and I don't want to consider them to assemble and make available the training schedule or to calculate the total duration of the videos . I.e, I want to ignore these 2 directories from my listing..
Also, at the end of the listing, instead of showing it on the console screen, I want to export this list to a CSV file, where I can open it in a text editor or Microsoft Excel.
How to Run Powershell Scripts
Now just type the Powershell commands you want.
Running the Powershell script to generate the listing
To run our Powershell script to generate the desired listing, copy the script below and paste it into the Powershell screen:
$Directory = "C:\Cursos\Azure\"
$Shell = New-Object -ComObject Shell.Application
Get-ChildItem -Path $Directory -Recurse -Force -Include *.mp4 | where fullname -notmatch 'Marketing' | where fullname -notmatch 'Geral' |
$Folder = $Shell.Namespace($_.DirectoryName)
$File = $Folder.ParseName($_.Name)
$Duration = $Folder.GetDetailsOf($File, 27)
Directory = $_.Directory -Replace ([Regex]::Escape("\Videos")), "" -Replace ([Regex]::Escape($Directory)), ""
Name = $_.Name -Replace ".mp4", ""
Size = "$([int]($_.length / 1mb)) MB"
Duration = $Duration
} | Export-Csv -Path "C:\Cursos\Azure\temp.csv" -NoTypeInformation -Encoding UTF8 -Delimiter ";"
If you saw this screen above, it's because the script ran without errors.
Opening the generated temp.csv file, we will see the complete list of videos, directory, size and duration. I can select, for example, videos only from the “Module 1 – Azure SQL” directory and now analyze the number of videos, average duration and total duration of these videos, as shown in the example below:
So, did you like this tip?
A big hug and until next time!