[original] (super simple) use ffprobe to batch extract video, audio and image metadata information and automatically write it into Excel table (amateur sharing)

 

█ task

Batch extract the basic metadata information (such as coding format, code rate, frame rate, number of frames, sampling rate, resolution, proportion, etc.) of video, audio and picture files (hereinafter referred to as the target file) in MP4, AVI, RMVB, H.264, FLV, MP3, WAV, MPEG, FLAC, 3GP, JPG, PNG, PSD and other formats, and export them to Excel table, txt text or csv text data table and other files.

█ ideas

1. ffprobe can obtain video, audio and image metadata information and export it in a fixed format, which is suitable for regular expression to extract key information.

2. The simple circular command can traverse the file and call ffprobe to extract the file information in batch. After simple processing, it is passed to the regular expression command to match, extract and export the key information to the file.

█ tools

1. Ffprobe (ffprobe can be used by installing ffmpeg or calling ffprobe independent program directly)

2. Command line terminals in powershell or other mainstream locales( It is used to call ffprobe with circular command to realize file management functions such as batch extraction and export of file information. It is recommended to use powershell, because most win systems are pre installed with powershell on the desktop, and can also be installed on linux and macOS platforms)

3. On the mobile terminal (mobile phone), Android and apple iOS systems can call ffprobe with python, linux and other commands through terminal simulators such as termux and pythonista respectively (the idea is similar).

█ code

(1) Code description

1. Take the powershell environment as an example.

2. Take the case where the target file is in a folder as an example. Open the target folder in the file manager, type "powershell" (without double quotes) directly in the address bar, and open powershell( You can also use the cd command to enter the folder where the target file is located)

3. When the number of target files is small, you can use GetFileDropList() of [System.Windows.Clipboard] class to realize the simple operation of clicking on a file to obtain information:

Select the target file in Explorer and ctrl+c copy. In powershell, you can obtain the list of selected files through the get childitem @ ([system. Windows. Clipboard]: getfiledroplist()) command. For subsequent operations and commands, refer to the complete code below.

4. For the case where the target file crosses folders, share three simple ideas:

(1) Directly type multiple file (folder) paths in the path parameter of ls command.

(2) The gc command reads the pre-made text file (txt or other text format) containing the file (folder) path, and then passes it to the ls command( gc is a nickname for the get content command)

(3) Insert a uniform identifier into the suffix of the target file, such as "#", that is, batch modify the suffix to the style of ". MP4 #", and then add ". MP4 #" to the part of filtering files in the following code. After execution, restore the file suffix in batch( Batch modification and restoration of file names can be easily completed by dragging and dropping with various renaming software.)

(2) Code and notes (complete code attached)

#1●The header is made according to the metadata type to be extracted, which can be customized#
"file name`t Stream number`t Encapsulation format`t Total length(s)`t size(B)`t Total bit rate(b/s)`t Video coding format`t wide`t high`t proportion`t Frame rate(fps)`t Video stream length(s)`t Video bit rate(b/s)`t Total frames`t Audio coding format`t sampling rate(kHz)`t Channels `t Audio stream length(s)`t Audio stream bit rate(b/s)`t File path" > Audio and video information.lhl;
#2●according to ffprobe Write the regular expression corresponding to the matching text in the exported fixed format (match in three segments to improve the execution speed)#'@ ()' is an array representation.
$find="(?<=#nb_streams=)(.*?)(?=#).*?(?<=#format_name=)(.*?)(?=#).*?(?<=#duration=)(.*?)(?=#).*?(?<=#size=)(.*?)(?=#).*?(?<=#bit_rate=)(.*?)(?=#)","(?<=#codec_name=)(.*?)(?=#).*?(?<=#width=)(.*?)(?=#).*?(?<=#height=)(.*?)(?=#).*?(?<=#display_aspect_ratio=)(.*?)(?=#).*?(?<=#r_frame_rate)(.*?)(?=#).*?(?<=#duration=)(.*?)(?=#).*?(?<=#bit_rate=)(.*?)(?=#).*?(?<=#nb_frames=)(.*?)(?=#)","(?<=#codec_name=)(.*?)(?=#).*?(?<=#sample_rate=)(.*?)(?=#).*?(?<=#channels=)(.*?)(?=#).*?(?<=#duration=)(.*?)(?=#).*?(?<=#bit_rate=)(.*?)(?=#)";
$fcount=@(5,8,5);clv lhl,xwrite,xcount,fail,count -ErrorAction SilentlyContinue;
#3●Traverse the target folder and filter out the files to be extracted according to the suffix#?( Question mark) indicates where command, "|" pipe symbol, "%" (percent sign) indicates foreach object command[ Array] is also a representation of an array.
ls -Recurse | ?{$_.Extension -in(".3gp",".asf",".avi",".f4v",".flv",".h264",".ico",".jpeg",".jpg",".m4v",".mov",".mp4",".mpeg",".rm",".rmvb",".wmv",".mkv",".wav",".aac",".amr",".ogg",".mp3",".gif",".png",".psd")} | %{[array]$lhl+=$_};
#4●call ffprobe Get file metadata information#The ffprobe command does not accept the transmission of pipeline data. It can use foreach, for, while, etc. to realize the loop. Simply process the extracted information (convert the array into a string to facilitate the batch search of regular expressions). "` n "stands for newline," ` "is an escape character in powershell.
foreach($i in $lhl){
$txt=((ffprobe  -i $i.fullname -show_format -show_streams -print_format default -v quiet) -join"#" -creplace"#[[]/STREAM]#","`n");
clv xMatches;
#5●Extracting corresponding key information by regular expression matching#”@{} "is the expression of hash table.
foreach($n in (0..2)){clv Matches;$txt -match $find[$n];$xMatches+=@{$n=$Matches[1..($Matches.Count-1)]}};
#6●adopt if,elseif,else Judge the type of file (video, audio, picture or video without audio stream, etc.) according to the conditions, and standardize the extracted information, so as to unify the format of information output. Export information to a file#
if($xMatches -ne $null){clv Matches,write;
$write+=foreach($n in (0..2)){if($xMatches[$n] -eq $null){@("-")*$fcount[$n]}else{$xMatches[$n]}};$write;}
else{$write=@("Acquisition failed!")*18;$fail+=@($i.fullname);$count+=1}
([array]$i.name+$write+[array]$i.fullname) -join"`t" | write >> Audio and video information.lhl;
#7●Optional command#With the optional command at the end, the extracted information is written automatically excel in#
$xcount+=1;$xwrite+=@{($xcount+1)=@("lhl")+[array]$i.name+$write+[array]$i.fullname};#Optional command
}
#8●If the file information acquisition fails, its full path is displayed on the screen.#
if($count -gt 0){Write-Warning $count"Failed to get files! ";$fail;clv fail,count;}
#9●Send the obtained metadata information of the target file to the pasteboard for easy pasting#
gc Audio and video information.lhl | clip.exe;ii Audio and video information.lhl;#(Pop up window to open the text file, you can choose to use excel Or other software open#
###So far, the information has been extracted and stored in the text file and pasteboard#
#10●#The following is an optional command to write the information obtained in batch excel Form, and automatically beautify the basic form(This command takes a long time (it takes about 1 second to write 1 file)#
$xwrite=$xwrite+@{1=@("lhl","file name","Stream number","Encapsulation format","Total length(s)","size(B)","Total bit rate(b/s)","Video coding format","wide","high","proportion","Frame rate(fps)","Video stream length(s)","Video bit rate(b/s)","Total frames","Audio coding format","sampling rate(kHz)","Channels ","Audio stream length(s)","Audio stream bit rate(b/s)","File path")}#Make header#

$xlsx=(New-Object -ComObject excel.application).Workbooks.Add();#call Excel#
$cell=$xlsx.ActiveSheet.Cells;$starttime=Get-Date -Format "yyyy year mm month dd day hh Time mm branch ss second";

foreach($col in 20..1){foreach($row in (1+$xcount)..1)#Write information#
{$cell.Item($row,$col)=$xwrite[$row][$col];
$cell.Item($row,$col).borders.linestyle=1;
$cell.Item($row,$col).borders.ColorIndex=2;
if($row -eq 1){$cell.Item($row,$col).interior.ColorIndex=1;
$cell.Item($row,$col).font.ColorIndex=2}
elseif($row%2 -eq 0){$cell.Item($row,$col).interior.ColorIndex=2}
else{$cell.Item($row,$col).interior.ColorIndex=15}}}
$cell.Item(1+$xcount+1,1)="start time:"+$starttime+" Completion time:"+(Get-Date -Format "yyyy year mm month dd day hh Time mm branch ss second");$cell.Item(1+$xcount+1,1).font.ColorIndex=3;$cell.Item(1+$xcount+1,1).font.size=13;

$xlsx.ActiveSheet.Name="Audio and video information";$xlsx.saveas((gl).path + '\Audio and video information.xlsx');#Rename and save#

The final effect is as follows:

(2) Complete code

"file name`t Stream number`t Encapsulation format`t Total length(s)`t size(B)`t Total bit rate(b/s)`t Video coding format`t wide`t high`t proportion`t Frame rate(fps)`t Video stream length(s)`t Video bit rate(b/s)`t Total frames`t Audio coding format`t sampling rate(kHz)`t Channels `t Audio stream length(s)`t Audio stream bit rate(b/s)`t File path" > Audio and video information.lhl;
$find="(?<=#nb_streams=)(.*?)(?=#).*?(?<=#format_name=)(.*?)(?=#).*?(?<=#duration=)(.*?)(?=#).*?(?<=#size=)(.*?)(?=#).*?(?<=#bit_rate=)(.*?)(?=#)","(?<=#codec_name=)(.*?)(?=#).*?(?<=#width=)(.*?)(?=#).*?(?<=#height=)(.*?)(?=#).*?(?<=#display_aspect_ratio=)(.*?)(?=#).*?(?<=#r_frame_rate)(.*?)(?=#).*?(?<=#duration=)(.*?)(?=#).*?(?<=#bit_rate=)(.*?)(?=#).*?(?<=#nb_frames=)(.*?)(?=#)","(?<=#codec_name=)(.*?)(?=#).*?(?<=#sample_rate=)(.*?)(?=#).*?(?<=#channels=)(.*?)(?=#).*?(?<=#duration=)(.*?)(?=#).*?(?<=#bit_rate=)(.*?)(?=#)";
$fcount=@(5,8,5);clv lhl,xwrite,xcount,fail,count -ErrorAction SilentlyContinue;
ls -Recurse | ?{$_.Extension -in(".3gp",".asf",".avi",".f4v",".flv",".h264",".ico",".jpeg",".jpg",".m4v",".mov",".mp4",".mpeg",".rm",".rmvb",".wmv",".mkv",".wav",".aac",".amr",".ogg",".mp3",".gif",".png",".psd")} | %{[array]$lhl+=$_}#
foreach($i in $lhl){
$txt=((ffprobe  -i $i.fullname -show_format -show_streams -print_format default -v quiet) -join"#" -creplace"#[[]/STREAM]#","`n");
clv xMatches;
foreach($n in (0..2)){clv Matches;$txt -match $find[$n];$xMatches+=@{$n=$Matches[1..($Matches.Count-1)]}};
if($xMatches -ne $null){clv Matches,write;
$write+=foreach($n in (0..2)){if($xMatches[$n] -eq $null){@("-")*$fcount[$n]}else{$xMatches[$n]}};$write;}
else{$write=@("Acquisition failed!")*18;$fail+=@($i.fullname);$count+=1}
([array]$i.name+$write+[array]$i.fullname) -join"`t" | write >> Audio and video information.lhl;
$xcount+=1;$xwrite+=@{($xcount+1)=@("lhl")+[array]$i.name+$write+[array]$i.fullname};
}
[System.Windows.Clipboard]::GetText();
if($count -gt 0){Write-Warning $count"Failed to get files! ";$fail;clv fail,count;}
gc Audio and video information.lhl | clip.exe;
ii Audio and video information.lhl ;
$xwrite=$xwrite+@{1=@("lhl","file name","Stream number","Encapsulation format","Total length(s)","size(B)","Total bit rate(b/s)","Video coding format","wide","high","proportion","Frame rate(fps)","Video stream length(s)","Video bit rate(b/s)","Total frames","Audio coding format","sampling rate(kHz)","Channels ","Audio stream length(s)","Audio stream bit rate(b/s)","File path")}
$xlsx=(New-Object -ComObject excel.application).Workbooks.Add();
$cell=$xlsx.ActiveSheet.Cells;$starttime=Get-Date -Format "yyyy year mm month dd day hh Time mm branch ss second";
foreach($col in 20..1){foreach($row in (1+$xcount)..1)
{$cell.Item($row,$col)=$xwrite[$row][$col];
$cell.Item($row,$col).borders.linestyle=1;
$cell.Item($row,$col).borders.ColorIndex=2;
if($row -eq 1){$cell.Item($row,$col).interior.ColorIndex=1;
$cell.Item($row,$col).font.ColorIndex=2}
elseif($row%2 -eq 0){$cell.Item($row,$col).interior.ColorIndex=2}
else{$cell.Item($row,$col).interior.ColorIndex=15}}}
$cell.Item(1+$xcount+1,1)="start time:"+$starttime+" Completion time:"+(Get-Date -Format "yyyy year mm month dd day hh Time mm branch ss second");$cell.Item(1+$xcount+1,1).font.ColorIndex=3;$cell.Item(1+$xcount+1,1).font.size=13;
$xlsx.ActiveSheet.Name="Audio and video information";$xlsx.saveas((gl).path + '\Audio and video information.xlsx');

 

(3) Other instructions

1. You can save the above code as a. ps1 script file and open it under the target folder to achieve one click operation.

2. The name of the target file (folder) contains special characters such as "[", which may need special processing.

Tags: Python Windows

Posted on Wed, 01 Sep 2021 15:35:29 -0400 by mooshuligan