locked
compare 2 csv files, and output to results csv (differences) RRS feed

  • Question

  • Hi,

    I'm trying to create a script to make a delta between values in 2 csv files.
    actually this is their P-drive diskspace per month. 
    In this case csv1 would be eg this : LastMonth, and csv2 would be : ThisMonth


    csv1.csv
    username,MBused
    userA,356
    userD,224
    userB,699


    csv2.csv
    username,MBused
    userA,250
    userD,860
    userE,10
    userB,314

    I've created a part of the script that checks for users that appear in both csv, so I only check the users for which I can compare data.

    I would like to output to a Results.csv looking like this :

    Results.csv
    username,MBUsed
    userA,-106
    userD,636
    userB,-385

    But I don't really get how to search/compare the specific data in the xls

    Wednesday, April 29, 2020 12:14 PM

Answers

  • Well, as long as you don't care about mismatched sets of data, convert the data sets to hashes and voila!

    $csv1 = @{}
    $csv2 = @{}
    @"
    username,MBused
    userA,356
    userD,224
    userB,699
    "@ | ConvertFrom-Csv |
        ForEach-Object{
            $csv1[$_.username]=$_.MBused
    
        }
    @"
    username,MBused
    userA,250
    userD,860
    userE,10
    userB,314
    "@ | ConvertFrom-Csv |
    ForEach-Object{
        $csv2[$_.username]=$_.MBused
    }
    $csv1.GetEnumerator() |
        ForEach-Object{
            if ($csv2.ContainsKey($_.Key)){
                [PSCustomObject]@{
                    username = $_.Key
                    MBused = ($csv2[$_.Key] - $_.Value)
                }
            }
        } | Export-CSV c:\junk\results.csv -NoTypeInformation
    
    "username","MBused"
    "userA","-106"
    "userB","-385"
    "userD","636"
    



    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Proposed as answer by Vector BCO Thursday, April 30, 2020 6:18 AM
    • Marked as answer by Lyncer2013 Wednesday, May 6, 2020 12:08 PM
    Wednesday, April 29, 2020 3:09 PM
  • Lyncer2013, if you also need to get userE you can use code like this  one:

    $Array1 = Import-Csv c:\tmp\test1.csv $Array2 = Import-Csv c:\tmp\test2.csv

    Compare-Object -ReferenceObject $Array1 -DifferenceObject $Array2 -Property UserName -IncludeEqual | Foreach {

        $UserName = $_.UserName
    # switch block added just for write some output but based on this SideIndicator you can build some additional logic
        switch ($_.SideIndicator){
            '==' {
                Write-Host "Username '$UserName' exists in both csv files" -ForegroundColor Green
            }
            '<=' {
                Write-Host "Username '$UserName' exists only in the first csv file" -ForegroundColor Yellow
            }
            '=>' {
                Write-Host "Username '$UserName' exists only in the last csv file" -ForegroundColor Magenta
            }
            default { Write-Host "Unknown operator" }
        }
        $dif = ($Array2 | Where {$_.UserName -eq $UserName}).MBUsed - ($Array1 | Where {$_.UserName -eq $UserName}).MBUsed
        '' | Select @{n = 'Usename'; e= {$UserName}}, @{n = 'Dif'; e= {$dif}}
    } | Sort Usename


    * UserC was added to the first csv file for getting full picture



    The opinion expressed by me is not an official position of Microsoft

    • Marked as answer by Lyncer2013 Wednesday, May 6, 2020 12:08 PM
    Friday, May 1, 2020 8:55 AM

All replies

  • Well, as long as you don't care about mismatched sets of data, convert the data sets to hashes and voila!

    $csv1 = @{}
    $csv2 = @{}
    @"
    username,MBused
    userA,356
    userD,224
    userB,699
    "@ | ConvertFrom-Csv |
        ForEach-Object{
            $csv1[$_.username]=$_.MBused
    
        }
    @"
    username,MBused
    userA,250
    userD,860
    userE,10
    userB,314
    "@ | ConvertFrom-Csv |
    ForEach-Object{
        $csv2[$_.username]=$_.MBused
    }
    $csv1.GetEnumerator() |
        ForEach-Object{
            if ($csv2.ContainsKey($_.Key)){
                [PSCustomObject]@{
                    username = $_.Key
                    MBused = ($csv2[$_.Key] - $_.Value)
                }
            }
        } | Export-CSV c:\junk\results.csv -NoTypeInformation
    
    "username","MBused"
    "userA","-106"
    "userB","-385"
    "userD","636"
    



    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Proposed as answer by Vector BCO Thursday, April 30, 2020 6:18 AM
    • Marked as answer by Lyncer2013 Wednesday, May 6, 2020 12:08 PM
    Wednesday, April 29, 2020 3:09 PM
  • Lyncer2013, if you also need to get userE you can use code like this  one:

    $Array1 = Import-Csv c:\tmp\test1.csv $Array2 = Import-Csv c:\tmp\test2.csv

    Compare-Object -ReferenceObject $Array1 -DifferenceObject $Array2 -Property UserName -IncludeEqual | Foreach {

        $UserName = $_.UserName
    # switch block added just for write some output but based on this SideIndicator you can build some additional logic
        switch ($_.SideIndicator){
            '==' {
                Write-Host "Username '$UserName' exists in both csv files" -ForegroundColor Green
            }
            '<=' {
                Write-Host "Username '$UserName' exists only in the first csv file" -ForegroundColor Yellow
            }
            '=>' {
                Write-Host "Username '$UserName' exists only in the last csv file" -ForegroundColor Magenta
            }
            default { Write-Host "Unknown operator" }
        }
        $dif = ($Array2 | Where {$_.UserName -eq $UserName}).MBUsed - ($Array1 | Where {$_.UserName -eq $UserName}).MBUsed
        '' | Select @{n = 'Usename'; e= {$UserName}}, @{n = 'Dif'; e= {$dif}}
    } | Sort Usename


    * UserC was added to the first csv file for getting full picture



    The opinion expressed by me is not an official position of Microsoft

    • Marked as answer by Lyncer2013 Wednesday, May 6, 2020 12:08 PM
    Friday, May 1, 2020 8:55 AM
  • Super! Thanks guys! Appreciate it!!!!
    Wednesday, May 6, 2020 12:08 PM