Matching Excel files with PowerShell – Part 2

The next day, I was in good mood and decided to learn about function. I would change my code into a function.

function Identify-Leaver {
    # 3 parameters: Leaver Exel path and sheet name, PCOwner Excel path
    Param ([String]$LeaverExcel,[String]$LeaverSheet,[String]$PCOwnerExcel,[String]$NewSheet)
    #log file
    $logFile = 'C:\Users\lddthao\OneDrive - Schlumberger\Scripts\LeaverConfirm\Log.txt'
    $totalscan = 0
    $foundcount = 0
    $notfoundcount = 0
    $LeaverList = Import-Excel -Path $LeaverExcel -WorksheetName $LeaverSheet
    $PCOwner = Import-Excel -Path $PCOwnerExcel
    Write-Output "Start scanning $LeaverExcel sheet $LeaverSheet and match with $NewSheet owner list" | Out-File $logFile -Append
    foreach ($Leaver in $LeaverList) {
         $isfound = 0
         $totalscan = $totalscan + 1
         foreach ($Owner in $PCOwner) {
            # if found, record to Found sheet, break loop
            if (($Leaver).GIN -like ($Owner).EmployeeNumber){
                $isfound = 1
                $foundcount = $foundcount + 1
                $found = $Owner | Export-Excel 'C:\Scripts\LeaverConfirm\Found.xlsx' -WorkSheetname $NewSheet -Append  
                break
            }

        }
    
        if ($isfound -eq 0){
            $notfoundcount = $notfoundcount + 1
            $notfound = $Leaver | Export-Excel 'C:\Scripts\LeaverConfirm\NotFound.xlsx' -WorkSheetname $NewSheet -Append
        }    
    
    }
    
    Write-Output "Scanned with $NewSheet Done" | Out-File $logFile -Append
    Write-Output "++ Total leaver scanned: $totalscan" | Out-File $logFile -Append
    Write-Output "++Found: $foundcount" | Out-File $logFile -Append
    Write-Output "++ Not Found: $notfoundcount" | Out-File $logFile -Append
    Write-Output "Found items recorded in C:\Scripts\LeaverConfirm\Found.xlsx sheet $NewSheet" | Out-File $logFile -Append
    Write-Output "Not Found items recorded in C:\Scripts\LeaverConfirm\Not Found.xlsx sheet $NewSheet" | Out-File $logFile -Append
    Write-Output "================================================" | Out-File $logFile -Append
}

Now to make it easier as I have many PCOwner files to compare, I placed all those files in to a PCOwner folder and run a script to automatically feed those files to my function:

$OwnerFolder = 'C:\Users\lddthao\OneDrive - Schlumberger\Scripts\LeaverConfirm\PCOwner'
$OwnerFileList = Get-ChildItem $OwnerFolder | %{$_.FullName}
$numberoffiles = $OwnerFileList.Length
Write-Output "Found $numberoffiles files in Owner directory"
Write-Output "Scan file 0 "
Identify-Leaver -LeaverExcel 'C:\Users\lddthao\OneDrive - Schlumberger\Scripts\LeaverConfirm\LeaverList.xlsx' -LeaverSheet 'Leavers' -PCOwnerExcel $OwnerFileList[0] -NewSheet '1' 
for ($i=1; $i -lt $numberoffiles; $i++){
    Write-Output "Scan file $i"
    $j = $i + 1
    Identify-Leaver -LeaverExcel 'C:\Users\lddthao\OneDrive - Schlumberger\Scripts\LeaverConfirm\NotFound.xlsx' -LeaverSheet $i -PCOwnerExcel $OwnerFileList[$i] -NewSheet $j 
}

It worked just fine with the screen output:

A piece of the log file:

The script run well but I couldn’t fix the challenge, there’re still 2xx GINs couldn’t be identified.