CODEWITHSUNDEEP
windowspowershellcsvcompare
Patrick Garber
Patrick Garber

Reputation: 23

Compare two csvs with powershell and take value from one into other

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...

Upvotes: 0

Views: 58

Answers (2)

rokumaru
rokumaru

Reputation: 1244

The following is the solution using Compare-Object.

Compare-Object $contacts $mailing -Property First,Last -IncludeEqual -ExcludeDifferent -PassThru |
select * -ExcludeProperty SideIndicator

Upvotes: 1

DwarfMagic
DwarfMagic

Reputation: 19

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 $_ }

Upvotes: 1

Related Questions