0

I have two CSVs in powershell that I am trying to extract data from and put into a csv. For example: contacts.csv with First, Last, Email mailinglist.csv with First,Last,Email

MailingList email is blank, and I need to pull the email addresses from contacts.csv

Mailinglist has some, but not all, of the contacts as contacts.csv. I need to pull the email addresses from contacts.csv only for the rows that have a match in Mailinglist.csv

So far I have:


    $contacts = Import-Csv .\contacts.csv
    $mailing = Import-Csv .\mailing.csv

    $compare = $mailing | select $_ | $_ -notcontains $contacts
    $email = $contacts | Select $_ | Where { $_ -contains $compare }

$compare gives me the correct First and Last columns. Correct being that it only returns values matching $mailing, not anything else.

The $email variable comes back empty.

Help? I have a spreadsheet with 850 records I would really like to not have to do manually...

Patrick Garber
  • 23
  • 4
  • Change `-notcontains` into `-notin` and `-contains` into `-in`, OR swap the variables. – Theo Mar 02 '20 at 21:43

2 Answers2

1

What you're looking for is called a 'left join'. Try searching around for a join (possibly this good explanation from Join-Object will help you figure out what you need) in Powershell and you should find some applicable examples. There are a few types of joins, like 'inner join' and 'left/right join' as well.

Powershell doesn't have great built-in functions for joining, so you'll likely need a small function or two.

Just by looking at your code though, you may want to try changing: Where { $_ -contains $compare }

To: Where { $compare -contains $_ }

DwarfMagic
  • 21
  • 1
1

The following is the solution using Compare-Object.

Compare-Object $contacts $mailing -Property First,Last -IncludeEqual -ExcludeDifferent -PassThru |
select * -ExcludeProperty SideIndicator
rokumaru
  • 1,244
  • 1
  • 8
  • 11