Forum Discussion

MP21260's avatar
MP21260
Copper Contributor
May 19, 2022

Data from Excel file to find specific files in folder

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

 

Office
powershell

3 Replies

  • Alan2022's avatar
    Alan2022
    Iron Contributor
    May 24, 2022

    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/

     

    • MP21260's avatar
      MP21260
      Copper Contributor
      Jun 02, 2022
      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
      • Alan2022's avatar
        Alan2022
        Iron Contributor
        Jun 02, 2022

        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.

         

Resources