Speak guys!
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
Just like any other Powershell script, to run it, just open the command prompt, typing the command “cmd” in the search bar:
Another way to open the command prompt is to type Win + R, to open the Run window, type the command “cmd” and press the “Enter” key:
In the console screen that opened, type the command “powershell” to enter the Powershell console:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
$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' | ForEach { $Folder = $Shell.Namespace($_.DirectoryName) $File = $Folder.ParseName($_.Name) $Duration = $Folder.GetDetailsOf($File, 27) [PSCustomObject]@{ 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 don't know how to do this, copy the script above and right click on the console to paste (it will paste straight away, without showing the menu). Now press the "Enter" key to run:
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!
When I run the above command in powershell. I did change the path to my external HD. I get the following error below with no output to csv file.
Get-ChildItem : Access to the path 'D:\System Volume Information' is denied.
At line: 4 char: 1
+ Get-ChildItem -Path $Directory -Recurse -Force -Include *.mp4 *.mkv |
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : PermissionDenied: (D:\System Volume Information:String) [Get-ChildItem], UnauthorizedAcc
essException
+ FullyQualifiedErrorId : DirUnauthorizedAccessError,Microsoft.PowerShell.Commands.GetChildItemCommand