CODEWITHSUNDEEP
powershellcsv
JRod
JRod

Reputation: 33

Remove line from file after performing a match

I am matching two files based on PersonID, and outputting to a third (ListAfinal). Essentially, I am adding the address and phone numbers for those entries that have it, and adding blank spaces for those who don't. I want to remove the lines from ListB that were matched to ListA, so after the matching we are left with a ListB with no matches in ListA.

$f1=(import-csv 'C:\Desktop\ListA.csv' -header FirstName, LastName, Birthdate, PersonID, Address, PhoneNumber)[1..999]
$f2=(import-csv 'C:\Desktop\ListB.csv' -header PersonID, Address, PhoneNumber)[1..999]
$f1|
   %{
      $PersonID=$_.PersonID
      $m=$f2|?{$_.PersonID -eq $PersonID}
      $_. Address=$m.Address
      $_. PhoneNumber=$m.PhoneNumber
    }
$f1 | ConvertTo-Csv -NoTypeInformation | select -Skip 1 |  % {$_.Replace('"','')} | Out-File 'C:\Desktop\ListAFinal.csv'

Every method I have found online does the line removal based off of a specific string, but in my case I just want to remove the line if it was matched. Any ideas?

Upvotes: 2

Views: 94

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200283

Create a hashtable (a hashtable of hashtables actually) from your second CSV:

$csv1 = Import-Csv 'C:\Desktop\ListB.csv' -Header PersonID, Address, PhoneNumber |
  Select-Object -Skip 1 -First 999

$contacts = @{}
$csv1 | ForEach-Object {
  $contacts[$_.PersonID] = @{
    Address     = $_.Address
    PhoneNumber = $_.PhoneNumber
  }
}

Then use calculated properties for adding the information to the data from the first CSV:

$csv2 = Import-Csv 'C:\Desktop\ListA.csv' -Header FirstName, LastName, Birthdate, PersonID, Address, PhoneNumber |
  Select-Object -Skip 1 -First 999

$csv2 |
  Select-Object *, @{n='Address';e={$contacts[$_.PersonID]['Address']}},
    @{n='PhoneNumber';e={$contacts[$_.PersonID]['PhoneNumber']}} |
  Export-Csv 'C:\Desktop\ListAFinal.csv' -NoType

Note that I strongly recommend against removing headers or quotes from your output CSV unless you have very compelling reasons to do so.

To remove records from $csv2 that have a match in $csv1 simply do this:

$IDs = $csv1 | Select-Object -Expand PersonID
$csv2 | Where-Object { $IDs -notcontains $_.PersonID }

Upvotes: 1

Related Questions