CODEWITHSUNDEEP
powershell
Kais
Kais

Reputation: 77

Loop Through excel file using Powershell

I'm a beginner in Powershell, and I have a code that loop through PDF files get all their names. Then loop in an excel file for a match if true give the name of the site. here is a sample of my PDF Files, Excel File and Code explained, if you need any other explanation please tell me :

I get all Files Name here

$filename = Get-childItem "T:\DVO\DVO-S\Activites\Gestion - Information clients\Base Installee\02-LOGISTIQUE\SB - TU\SB NORIA\FAIT BI\*" -Recurse  | Where-Object {!$_.PSIsContainer}

Set the root to the Excel File and Workbook sheet

$fil = '\\tm.corp\turbomeca\DVO\D2S-MRO\Activites\30-Noria\30-01 Activité\30-01-04 Plan de Progrès\2020-PDP\Fiches projet\44- simplification traitement des CoC Noria\extract pour projet gestion des CoC\Noria - Noria Search Advanced.xlsx'
$sheetName = "Noria - Noria Search Advanced"

Open the File

$objExcel = New-Object -ComObject Excel.Application
$WorkBook = $objExcel.Workbooks.Open($fil)
$sheet = $WorkBook.worksheets.Item($sheetName)
 $objExcel.visible = $false
 $objExcel.DisplayAlerts = $false

Set the last Row

$rowMax = ($Sheet.usedRange.rows).count

Select the columns I want to work with

$RowNoria, $colNoria = 4,1 #Noria column
$RowNheSN,$colNheSN = 4,6 # NHESN column
$RowSite, $colSite =  4,29 #Site column

Here I want To get for every file the SN which is the bold part on this exemple of file name $SN = 10074_292720384_C_2021.05.07.pdf (every thing before the first "underscore") and then get the SB number which is $SB = 10074_292720384_C_2021.05.07.pdf (3 last numbers of the number between "underscore" I then tried to put the $SB in a way that can match the names in Noria column by doing this $SBF = "*_*$SB_*"

for ($i=0; $i -lt $filename.Count; $i++){
       $F = $filename[$i].Name
       $F
       $SN = $F.split("_")[0]
       $split = $F.split("_")[1] 
       $SB = -join $split[-3..-1]
       $SBF = "*_*$SB_*"

In this loop I added a loop that is going to look for $SN in NHESN column and try to find that exact number and look for $SBF in the Noria column. If this two condition are met give me the SITE in the same Row

for($j=1; $j -le $rowMax-1; $j++){
        $Noria = $sheet.cells.Item($RowNoria+$j, $colNoria).text
        $NheSN = $sheet.cells.Item($RowNheSN+$j, $colNheSN).text
        $Site = $sheet.cells.Item($RowSite+$j, $colSite).text
        if ($Noria -like $SBF -and $NheSN -eq $SN) {
        
                Write-Host ($Noria)
                Write-Host ($Site)
                write-Host ($NheSN)
                Break
                }
        else {Continue}
      }

As a Result I need This For exemple

and once i get it go to next file

But The result I get is Once it found a match in SN ($SN -eq $NHESN) it gave me the first row with that SN or NHESN but I want the row with the two condition for $SN -eq $NHESN and $SBF -eq $NORIA to be true The false result i get for exemple is

Thank you in advance for your time and help

And here is the all the code

$filename = Get-childItem "T:\DVO\DVO-S\Activites\Gestion - Information clients\Base Installee\02-LOGISTIQUE\SB - TU\SB NORIA\FAIT BI\*" -Recurse  | Where-Object {!$_.PSIsContainer}

$fil = '\\tm.corp\turbomeca\DVO\D2S-MRO\Activites\30-Noria\30-01 Activité\30-01-04 Plan de Progrès\2020-PDP\Fiches projet\44- simplification traitement des CoC Noria\extract pour projet gestion des CoC\Noria - Noria Search Advanced.xlsx'
$sheetName = "Noria - Noria Search Advanced"

$objExcel = New-Object -ComObject Excel.Application
$WorkBook = $objExcel.Workbooks.Open($fil)
$sheet = $WorkBook.worksheets.Item($sheetName)
 $objExcel.visible = $false
 $objExcel.DisplayAlerts = $false

$rowMax = ($Sheet.usedRange.rows).count

$RowNoria, $colNoria = 4,1
$RowNheSN,$colNheSN = 4,6
$RowSite, $colSite =  4,29

for ($i=0; $i -lt $filename.Count; $i++){


       $F = $filename[$i].Name
       $F
       $SN = $F.split("_")[0]
       $split = $F.split("_")[1] 
       $SB = -join $split[-3..-1]
       $SBF = "*_*$SB_*"
                         
    for($j=1; $j -le $rowMax-1; $j++){
        $Noria = $sheet.cells.Item($RowNoria+$j, $colNoria).text
        $NheSN = $sheet.cells.Item($RowNheSN+$j, $colNheSN).text
        $Site = $sheet.cells.Item($RowSite+$j, $colSite).text
        if ($Noria -like $SBF -and $NheSN -eq $SN) {
        
                Write-Host ($Noria)
                Write-Host ($Site)
                write-Host ($NheSN)
                Break
                }
        else {Continue}
      }
             
    }
$objExcel.quit()

Upvotes: 2

Views: 11673

Answers (1)

Cpt.Whale
Cpt.Whale

Reputation: 5351

_ is a valid character for variable names, so $SBF is getting set incorrectly on this line:

$SBF = "*_*$SB_*"

Use a backtick to escape it:

$SBF = "*_*$SB`_*"

Upvotes: 2

Related Questions