CODEWITHSUNDEEP
powershellcsv
Hogne
Hogne

Reputation: 13

Comparing two files: Single column in FirstFile - Multiple columns in SecondFile

I've figured out how to compare single columns in two files, but I cant figure out how to compare two files, with one column in the first and multiple columns in the second file. Both containing emails.

First file.csv (contains single column with emails)
[email protected]
[email protected]
[email protected]

Second file.csv (contains multiple column with emails)
[email protected],[email protected],[email protected]
[email protected],[email protected],[email protected]

By comparing I would like to output, the difference. This would result in.

Output.csv
[email protected]

Anyone able to help me? :)

Single columns comparison and output difference

#Line extracts emails from list
$SubscribedMails = import-csv .\subscribed.csv | Select-Object -Property email

#Line extracts emails from list
$ValidEmails = import-csv .\users-emails.csv | Select-Object -Property email

$compare = Compare-Object $SubscribedMails $ValidEmails -property email -IncludeEqual |  where-object {$_.SideIndicator -eq "<="} | Export-csv .\nonvalid-emails.csv –NoTypeInformation
(Get-Content .\nonvalid-emails.csv) | ForEach-Object { $_ -replace ',"<="' } > .\nonvalid-emails.csv

Upvotes: 0

Views: 120

Answers (4)

Flynn Handley
Flynn Handley

Reputation: 450

Can you try this?

$One = (Get-Content .\FirstFile.csv).Split(',')

$Two = (Get-Content .\SecondFile.csv).Split(',')

$CsvPath = '.\Output.csv'

$Diff = @() 

(Compare-Object ($One | Sort-Object) ($two | Sort-Object)| `
    Where-Object {$_.SideIndicator -eq '<='}).inputobject | `
    ForEach-Object {$Diff += New-Object PSObject -Property @{email=$_}}

$Diff | Export-Csv -Path $CsvPath -NoTypeInformation

Output.csv will contain entries that exist in FirstFile but not SecondFIle.

Upvotes: 0

Hogne
Hogne

Reputation: 13

None of the suggestions so far works :(
Still hoping :)
Will delete comment when happy :p

Upvotes: 0

youngz
youngz

Reputation: 179

If you want to maintain your code, can you consider a script like:

    #Line extracts emails from list
    $SubscribedMails = import-csv .\subscribed.csv | Select-Object -Property email

    Rename-Item .\users-emails.csv users-emails.csv.bk
    Get-Content .\users-emails.csv.bk).replace(',', "`r`n") | Set-Content .\users-emails.csv

    #Line extracts emails from list
    $ValidEmails = import-csv .\users-emails.csv | Select-Object -Property email


    $compare = Compare-Object $SubscribedMails $ValidEmails -property email -IncludeEqual |  where-object {$_.SideIndicator -eq "<="} | Export-csv .\nonvalid-emails.csv –NoTypeInformation
    (Get-Content .\nonvalid-emails.csv) | ForEach-Object { $_ -replace ',"<="' } > .\nonvalid-emails.csv

    Remove-Item .\users-emails.csv
    Rename-Item .\users-emails.csv.bk users-emails.csv

or, more simplest

    #Line extracts emails from list
    $SubscribedMails = import-csv .\subscribed.csv | Select-Object -Property email

    Get-Content .\users-emails.csv).replace(',', "`r`n") | Set-Content .\users-emails.csv.bk

    #Line extracts emails from list
    $ValidEmails = import-csv .\users-emails.csv.bk | Select-Object -Property email


    $compare = Compare-Object $SubscribedMails $ValidEmails -property email -IncludeEqual |  where-object {$_.SideIndicator -eq "<="} | Export-csv .\nonvalid-emails.csv –NoTypeInformation
    (Get-Content .\nonvalid-emails.csv) | ForEach-Object { $_ -replace ',"<="' } > .\nonvalid-emails.csv

    Remove-Item .\users-emails.csv.bk

Upvotes: 0

Maigi
Maigi

Reputation: 49

Since the first file already contains email addresses per column, you can import it right away. Take the second file and split the strings containing several addresses. A new array with seperate addresses will be generated.

Judging from your output, you only seek addresses that are within the first csv but not in the second.

Your code could look like this:

$firstFile = Get-Content 'FirstFile.csv'
$secondFile = (Get-Content 'SecondFile.csv').Split(',')

foreach ($item in $firstFile) {
    if ($item -notin $secondFile) {
        $item | Export-Csv output.csv -Append -NoTypeInformation
    }
}

Upvotes: 1

Related Questions