Data from Excel file to find specific files in folder

[MP21260]

Hello,

I'm trying to include data from specific ranges (contain files names) in a worksheet from Excel file in order to find this data in folders.

After many searches, I couldn't get the way to accomplish it.

Here the powershell script :

$emplacement = "D:\DOCUMENTAIRE\"
$myFolders = @(
"File A",
"File B",
"File C",
"File D",
"File E",
"File F",
"File G",
"File H",
"File I",
"File J",
"File K",
"File L"
)
$nbDocuments=0

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open('D:\PROJET_DOCUMENTAIRE\Suivi_v1.0.xlsx')
$workSheet = $Workbook.Sheets.Item(1)
$WorkSheet.Name.Range

Write-Host "NUM;FOLDER;AGENCY;TYPE;DOMAIN;FILE"

For($i=0;$i -lt $myFolders.Length;$i++) 
{ 
    $files = Get-ChildItem -Path $emplacement$($myFolders[$i]) | Where-Object { $_.Name -match ' ' }
    For($j=0;$j -lt $files.Length;$j++) 
    { 
        $explode_content = $files[$j].ToString().Split("_")
        $nbDocuments++
        Write-Host "$nbDocuments;$($myFolders[$i]);$($explode_content[0]);$($explode_content[1]);$($explode_content[2]);$($files[$j])" 
        
    }
}

$files | export-csv C:\Users\Admin\Desktop\List.csv -delimiter ';'

Write-Host "---------------------------------"
Write-Host "Total documents : $nbDocuments"
Write-Host "---------------------------------"

cmd / c pause

Thank you in advance for your help !

Math

[#2]

HI @MP21260 

# Get the List of Files base on Location
$files = Get-ChildItem "$($env:USERPROFILE)\Desktop\Test Files"

# Loop each files
foreach($file in $files){

    # Get File Fullname location & name 
    $file.Fullname

}

References: https://shellgeek.com/powershell-search-for-files/

[#3]

Hi @Alan2022,

thank you for replying but I couldn't understand.

How can I put these command lines into the initial script ?

Thank you again.

Math

[#4]

@MP21260 

Can you explain more what you really want to do?

Get List of Data via Columns in Excel.

# Install-Module -Name ImportExcel -Scope CurrentUser

# Get Data from Excel column Header FileName
$datas = Import-Excel "C:\...\TestFile.xlsx" 

foreach($data in $datas){
    $data.FileName
}

# List all the files within a folder

$files = Get-ChildItem "C:\...\Folder"
# Loop each files
foreach($file in $files){
    # Get List of Files
    $file.Fullname
}

With this you can now compare the list of filename from excel & folder list.