Compare csv-files in powershell

Question

i would like to compare two csv-files, but i do not only want to find new lines and output them to a third csv-file, but changed lines aswell, and thats where i struggle.

My csv-files consist of these Headers:

Item Name,Manufacturer,Model Name,Category,Status,Company,Serial Number,Asset Tag,Notes,Model Number

As an example, I have my first csv-file with this data:

lptest,Dell,Dell XPS 15,Laptop,Ready to Deploy,MyCompany,XXXXX,YYYYYY,Active,empty
lptest2,Dell,Dell XPS 15,Laptop,Ready to Deploy,MyCompany,XXXXX,YYYYYY,Active,empty

My new csv-file has this:

lptest,Dell,Dell XPS 15,Laptop,Ready to Deploy,MyCompany,XXXXX,YYYYYY,archived,empty
lptest2,Dell,Dell XPS 15,Laptop,Ready to Deploy,MyCompany,XXXXX,YYYYYY,Active,empty
lptest3,Dell,Dell XPS 15,Laptop,Ready to Deploy,MyCompany,XXXXX,YYYYYY,Active,empty

I would like the output to be:

lptest,Dell,Dell XPS 15,Laptop,Ready to Deploy,MyCompany,XXXXX,YYYYYY,archived,empty
lptest3,Dell,Dell XPS 15,Laptop,Ready to Deploy,MyCompany,XXXXX,YYYYYY,Active,empty

Because the first line changed, and the last one is new.

Is this possible?

Currently the only thing I can find, is the compare-object method, but that seems to only compare for one header if that changed and that is not what I want.

Would be great, if someone had an idea for the problem

score 0 · Answer 1 · answered Oct 08 '20 at 08:05

0

If you read the csv using import-csv then you would run into that problem. Instead, try reading it as a file using Get-content. Compare object would now give an answer that is close to what you want. Since the first file is your reference object, all changes will be under the SideIndicator =>.

Important thing to note is that in this scenario, the headers are just lines on a text file. So take care to add those before writing the data to a csv file.

$a = Get-Content .\1.csv -Encoding Ascii
$b = Get-Content .\2.csv -Encoding Ascii

$Headers = $a[0]
$Body = (Compare-Object -ReferenceObject $a -DifferenceObject $b | Where-Object {$_.SideIndicator -eq "=>"}).InputObject
$new = @($Headers) + $Body
$new | Out-File .\3.csv -Encoding Ascii

answered Oct 08 '20 at 08:05

Sid

2,586
1
11
22

But note: This will be a literal string compare, so also compares the csv format, and not just values, e.g. a field will be treated as changed if it has quotes, even if the value didn't change. – marsze Oct 08 '20 at 08:20
@marsze I don't think a script that tells me "abc" is the same as `abc` is a good comparison script. – Sid Oct 08 '20 at 08:36
In CSV, those are exactly the same. `first;second` is identical to `"first";"second"`, because that is just a formatting/serialization detail. We are not comparing plain text files but CSV data. But yes, for simple cases, your script will work and it's shorter. – marsze Oct 08 '20 at 08:36

marsze · Answer 2 · 2020-10-08T08:36:15.637

You can use Compare-Object to compare objects by property values as described in this answer.

Also, Powershell requires a header to work with CSV as objects. I am not sure if your file has one, so I created a generic one (which I remove again at the end)

# use a temporary generic header
$tmpheader = 0..9
$delimiter = ","

$oldCsv = @"
lptest,Dell,Dell XPS 15,Laptop,Ready to Deploy,MyCompany,XXXXX,YYYYYY,Active,empty
lptest2,Dell,Dell XPS 15,Laptop,Ready to Deploy,MyCompany,XXXXX,YYYYYY,Active,empty
"@

$newCsv = @"
lptest,Dell,Dell XPS 15,Laptop,Ready to Deploy,MyCompany,XXXXX,YYYYYY,archived,empty
lptest2,Dell,Dell XPS 15,Laptop,Ready to Deploy,MyCompany,XXXXX,YYYYYY,Active,empty
lptest3,Dell,Dell XPS 15,Laptop,Ready to Deploy,MyCompany,XXXXX,YYYYYY,Active,empty
"@

# for your script, replace with Import-Csv
$oldLines = ConvertFrom-Csv $oldCsv -Header $tmpheader -Delimiter $delimiter
$newLines = ConvertFrom-Csv $newCsv -Header $tmpheader -Delimiter $delimiter

# compare
$(foreach ($newLine in $newLines) {
    # check if exists in old csv
    $found = $false
    foreach ($oldLine in $oldLines) {
        # compare values of objects
        $result = Compare-Object $oldLine.PSObject.Properties $newLine.PSObject.Properties
        # if result is null, the objects match
        if ($null -eq $result) {
            $found = $true
            break
        }
    }
    # output if this exact line does not exist in old csv
    if (-not $found) {
        $newLine
    }
}) |
    # convert back to csv
    # (you can also use Export-Csv to write to file directly)
    ConvertTo-Csv -NoTypeInformation -Delimiter $delimiter | 
    # OPTIONAL steps:
    # 1.) remove temp header
    select -skip 1 | 
    # 2.) remove quotes
    foreach {$_ -replace '"',''} |
    # 3.) write to file
    Out-File "result.csv"

Note: This algorithm assumes all lines are unique and that new lines can appear anywhere in the file (not just at the end).

Compare csv-files in powershell

2 Answers2