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.

As a general aside: [Try to avoid using the increase assignment operator (`+=`) to create a collection](https://stackoverflow.com/a/60708579/1701026) as it might become pretty expensive. — iRon, Apr 01 '23 at 07:11

score 2 · Accepted Answer · answered Mar 31 '23 at 19:31

2

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()

answered Mar 31 '23 at 19:31

Santiago Squarzon

41,465
5
14
37

1

Once again thank you so much Santiago for the help. Really appreciated it. – aasenomad Mar 31 '23 at 19:48
@aasenomad my pleasure, glad it was helpful :) – Santiago Squarzon Mar 31 '23 at 19:49
Hi @SantiagoSquarzon I have a lot data in both file, over 124,654 users and it taking a very long time finish so I'm just wondering if you know if there is a faster process?. – aasenomad Apr 06 '23 at 19:18
1

@aasenomad perhaps changing the `ForEach-Object` for a `foreach` loop would make this go faster but you would be sacrificing memory for performance. If you have enough memory then it would be fine. If you can ask a new question that would be great – Santiago Squarzon Apr 06 '23 at 19:50

How to compare text file and csv file using PowerShell

1 Answers1