Как сравнить и отфильтровать файлы в каталоге с именем файла в текстовом файле или в листе Excel?

I have 100000 mp3 files in one directory, each file contain a random filename, and I have another excel sheet containing the mp3 file's name and to whom it maps to. Is there any way to develop a script or 3rd party software can help categorize these files into 3 different folder

• In the Excel

  1. Column 1: File name = fasf4a5465.mp3, Column 2 'File Group' = Group1
  2. Column 1: File name = fasdf35434.mp3, Column 2 'File Group' = Group2
  3. Column 1: File name = vefbgwsbgg.mp3, Column 2 'File Group' = Group3

Okay, so it's a script you want to do this then Powershell to the rescue with the below logic.

PowerShell Script Variables Explained

• $ExcelFile =
  • You will need to make this variable point to your XLS or XLSX file (yes either one will work) which contains the mappings, or the column values per row which the first column is the MP3 file name and the second column is the group name or the new subfolders these files will be copied to.
• $CSVFile =
  • You will need to make this variable point to the CSV converted temporary file (based on what's in the Excel ($ExcelFile)) for the rest of the logic to do it's magic. If this file already exists, I put the logic to overwrite it so no worries on a static file name.
• $MP3SourceDir =
  • You will need to make this variable point to your MP3 source directory where these file will be grabbed from initially based on the file names from first column in the Excel spreadsheet. Warning: you MUST keep the \$($_.first) logic in this logic—just plug in the other path part only.
• $NewMP3Dir =
  • You will need to make this variable point to the new location where the new [group] second column folders will be created for the applicable first column MP3 files to be copied. Warning: you MUST keep the \$($_.second) logic in this logic—just plug in the other path part only.

PowerShell Script Example

$ExcelFile = "C:\Path\Excel Worksheet.xlsx" $CSVFile = "C:\Path\ExportCSV.csv" $Excel = New-Object -ComObject excel.application $Excel.visible = $False $Excel.displayalerts = $False $WorkBook = $Excel.Workbooks.Open("$ExcelFile") $WorkSheet = $Workbook.worksheets.Item(1) If (Test-Path $CSVFile){ Remove-Item $CSVFile } $WorkBook.SaveAs("$CSVFile", 6) $Excel.quit() Import-Csv "$CSVFile" -Header ("first","second") | ForEach { $MP3SourceDir = "D:\Path\SourceMP3\$($_.first)" $NewMP3Dir = "D:\Path\NewMP3\$($_.second)" New-Item -ItemType Dir "$NewMP3Dir" -Force Copy-Item "$MP3SourceDir" -Destination "$NewMP3Dir\$($_.first)" -Force } 

(Note this is the source I got the logic idea from for the xls to csv conversion to then read the delimited values in the csv file to then do the rest so I made many adjustments as well)

(This script essentially takes an existing XLS or XLSX file with two column [mapping] values in column A and column B (all rows), and saves it in CSV format to another CSV file. It then reads from that CSV file and the CSV value of column A (per line or row) is used as the file name to copy to another folder (or subfolder), which is the column B CSV value (on the same line or row). Each line or row should contain two values with the first being a file name and the second being a folder name—these are the values looped through running the commands to copy, etc.)

Source: PowerShell XLS to CSV

Further PowerShell Command Reading

• PowerShell Import-CSV

• PowerShell Copy-Item

• PowerShell New-Item

• PowerShell Remove-Item

• PowerShell Test-Path