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

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

CodePudding user response：

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 Device -AsHashTable -AsString

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

CodePudding user response：

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

CodePudding user response：

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#> }})
}

CodePudding user response：

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