Powershell: Compare 2 CSV files for equalities, then output equalities and another row

Question

I'm trying to sort trough a recent report of PCs users don't seem to be using, for that ive got a CSV file called report, and a CVS file of all our PC's called data.

Report and Data only share 1 column which is the users full name, how can i get a result which provides both the users full name and the PC which is only from report?

So far i have the following code which works for getting the users full name, but I'm unsure how to get the device

$report = Import-Csv "C:\Temp\\report.CSV" -Delimiter ";"
$data = Import-Csv  "C:\Temp\\data.CSV" -Delimiter ";"
$UserOutput = @()

    ForEach ($name in $report)
    {
        $userMatch = $data | where {$_.FullName -like $name.FullName}
        If($userMatch)
        {
        $UserOutput += New-Object PsObject -Property @{UserName =$name.FullName;Device=$userMatch.Device}
        }
        else
        {
        $UserOutput += New-Object PsObject -Property @{UserName =$name.FullName;Device ="NA"}
        }
    }
$UserOutput | ft

This gives a nice list, but i cant find the devices so it ends up looking like this

Device UserName

------ --------

NA Anders Aadal Jensen

NA Andr�s Kov�cs

NA Anette Dahnke

Use format-table for debugging which will give all the property names : $UserList | Format-List — jdweng, Dec 07 '22 at 12:25
To get better answers, please add some sample data in your question. Its hard to guess and provide support. — Dilly B, Dec 07 '22 at 12:33
is the `Device` property populated in `$data` are you sure? If you do `$data.Device` do you see them? — Santiago Squarzon, Dec 07 '22 at 12:51
Using this [`Join-Object script`](https://www.powershellgallery.com/packages/Join)/[`Join-Object Module`](https://www.powershellgallery.com/packages/JoinModule) (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `Import-Csv .\report.CSV |Join (Import-Csv .\data.Csv) -on FullName -Name Report,Data` — iRon, Dec 07 '22 at 13:06
@SantiagoSquarzon Yes i get the full list of devices from $data — Emil Johansen, Dec 07 '22 at 13:21
As an aside: [try avoid using the increase assignment operator (`+=`) to create a collection](https://stackoverflow.com/a/60708579/1701026) as it is exponentially expensive. Besides, for performance reasons you probably want to create an hashtable as described in several answers of nearly duplicated question [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026). — iRon, Dec 07 '22 at 15:49

Santiago Squarzon · Answer 1 · 2023-02-21T03:17:51.920

You can use a Group-Object -AsHashtable to correlate the FullName property on both arrays of objects. This assumes that the property values are an exact match when correlated.

$data = Import-Csv  "C:\Temp\data.CSV" -Delimiter ";" |
    Group-Object FullName -AsHashTable -AsString

Import-Csv "C:\Temp\report.CSV" -Delimiter ";" | ForEach-Object {
    $device = 'NA'
    if($data.ContainsKey($_.FullName)) {
        $device = $data[$_.FullName].Device
    }

    [pscustomobject]@{
        UserName = $_.FullName
        Device   = $device
    }
} | Format-Table

score 0 · Accepted Answer · answered Dec 07 '22 at 12:59

Assuming that the details provided by you available in both the csv file, here is a sample for you.

$CSV1 = Import-Csv "C:\Temp\\report.CSV" -Delimiter ";"
$CSV2 = Import-Csv  "C:\Temp\\data.CSV" -Delimiter ";"

$Count = $CSV2.Count
$Results = For ($i = 0; $i -lt $Count; $i++) {
    If ($CSV2[$i].FullName -eq $CSV1[$i].FullName) {
        $Match = "Match"
    } 
    Else {
        $Match = "No Match found"
    }
    [PSCustomObject]@{
        UserName = $CSV2[$i].FullName
        Device = $CSV2[$i].Device
        Results = $Match
    }
}
$Results | Export-Csv -Path "C:\Temp\results.csv" -NoTypeInformation

Two CSV files sharing a column with the same values doesn't necessarily mean that the rows in the two files correspond _one to one_ with respect to the columns' values, which is what your answer assumes without saying so. While you may have solved the _OP's_ specific problem, the question is ambiguous, and future readers who find this post may have different requirements. — mklement0, Feb 20 '23 at 23:22

score 0 · Answer 3 · answered Dec 07 '22 at 15:17

The accepted answer looks to assume entry count on the CSVs are a one for one match. Since it has one loop comparing the same CSV array element numbers.

From your description, you mention Report.csv is a list of infrequently used PCs and the Data.csv is an "All PC" list. If your entry counts are not one-for-one, may need two loops like below.

$Results = 0..$($REPORT.Count - 1) | ForEach-Object { 
    $i = $_ 
    $($DATA | ForEach-Object { if($_.Fullname -eq $REPORT[$i].Fullname) { <# DATA PSCUSOMOBJECT#> }})
}

Powershell: Compare 2 CSV files for equalities, then output equalities and another row

3 Answers3

Linked