How to compare text file and csv file using PowerShell

Question

I have a txt file call EmployeeID.txt that look something like this

Number      ID
32324       KLEON
23424       MKEOK

and I have a CSV file called FullInventory.csv that look something like this

Name     URL        UPN               Status
John    http://     KLEON@COM.COM     Yes

I'm trying to compare the two files and out put 2 different file called matchFound.csv and notFound.txt file.

If an ID from EmployeeID.txt is found in FullInventory.csv then output matchFound.csv with the all the column from FullInventory.csv

If an ID from EmployeeID.txt is NOT found in FullInventory.csv then output NotFound.txt with a data from EmployeeId.txt

$ImportTXT = Import-CSV -path $Global:EmployeeID -Delimiter "`t"
$ImportFullInv = Import-CSV -Path $Global:FullInventory 


ForEach ($TxtLine in $ImportTXT) {
  $TxtID = $TxtLine.ID

  if ($null -eq $txtID -or $txtID -eq '') {
    Write-Host "ID is empty"
  }
  else {
    $array = @();

    ForEach ($CSVLine in $ImportFullInv) {
      $CSVUPN = $CSVLine.UPN
      $UPNSPLIT = $CSVUPN -split "@"
      $array += $UPNSPLIT[0]
    }

    if ($array -contains $TxtID) {
    // Something is not right here.
   
      $CSVLine |  Export-Csv -Path "C:\Users\Desktop\matchFound.csv" -append
    

  
    }
    else {
      $TxtLine | Out-File -FilePath  "C:\Users\Desktop\notFound.txt" -append

    }
  }
}

The problem I have right now is matchFound.csv file is not outputting the correct Data. I think it outputting the last data columns from the csv file instead of the one that it matched. Any help or suggestion would be really apprecaited.

Answer 1

This can be accomplished using Group-Object -AsHashtable to allow fast lookups, the hash Keys would be the Name extracted from the UPN column. In addition to export this to two different files, you use a steppable pipeline instead of using -Append.

$map = Import-Csv $FullInventory -Delimiter "`t" |
    Group-Object { [mailaddress]::new($_.UPN).User } -AsHashTable -AsString

$pipeMatch = { Export-Csv 'C:\Users\Desktop\matchFound.csv' -NoTypeInformation }.GetSteppablePipeline()
$pipeMatch.Begin($true)

Import-Csv $EmployeeID -Delimiter "`t" | ForEach-Object {
    # if this ID exists in the full inventory
    if($map.ContainsKey($_.ID)) {
        # export all rows from inventory matching this ID
        $map[$_.ID] | ForEach-Object { $pipeMatch.Process($_) }
        # and go to the next ID
        return
    }

    # else, export this line to the not found csv
    $_
} | Export-Csv "C:\Users\Desktop\notFound.csv" -NoTypeInformation

$pipeMatch.End()