Extract differences of CSV files into a seperate file

Question

I have a CSV file (with headers) filled with assortment data. The file will be updated once every day. I need to find the differences in those files (the old and the new one) and extract them into a separate file.

For instance: in the old file there could be a price of "18,50" and now it's an updated one of "17,90". The script should now extract this row into a new file.

So far, I was able to import both CSV files (via Import-Csv) but my current solution is to compare each row by findstr.

The problems are:

In 9 of 10 cases the strings are too long to compare.
What if a new row will be inserted - I guess the comparison wouldn't work any longer if the row isn't inserted at the end of the file.

My current code is:

foreach ($oldData in (Import-Csv $PSScriptRoot\old.csv -Delimiter ";" -Encoding "default")) {
    foreach ($newData in (Import-Csv $PSScriptRoot\new.csv -Delimiter ";" -Encoding "default")) {
        findstr.exe /v /c:$oldData $newData > $PSScriptRoot\diff.txt
    }
}

And some sample data. ie. is there an identifying column (product # for example)? — Mathias R. Jessen, Sep 06 '17 at 14:07
Yes there is an identifying column called "idArtikel" which is the unique product-id — Razorfen, Sep 06 '17 at 14:28
You could read both files into hash tables (https://social.technet.microsoft.com/Forums/ie/en-US/7064dc22-0611-4f4c-a846-dc24ed344389/comparing-hashtables?forum=winserverpowershell), and then compare the hash tables (https://social.technet.microsoft.com/Forums/ie/en-US/7064dc22-0611-4f4c-a846-dc24ed344389/comparing-hashtables?forum=winserverpowershell) — Tim Kennedy, Sep 06 '17 at 14:37
Another information: one line could contain 3000 characters or more (thats why I got problems with `findstr`) — Razorfen, Sep 06 '17 at 14:42

score 1 · Accepted Answer · answered Sep 06 '17 at 14:41

Read both files into separate variables and use Compare-Object for the comparison:

$fields = 'idArtikel', 'Preis', ...

$csv1 = Import-Csv $PSScriptRoot\old.csv -Delimiter ';'
$csv2 = Import-Csv $PSScriptRoot\new.csv -Delimiter ';'

Compare-Object -ReferenceObject $csv1 -DifferenceObject $csv2 -Property $fields -PassThru | Where-Object {
    $_.SideIndicator -eq '=>'
} | Select-Object $fields | Export-Csv 'C:\path\to\diff.csv' -Delimiter ';'

score 0 · Answer 2 · answered Sep 06 '17 at 15:49

0

$csv1 | Join $csv2 idArtikel -Merge {$Right.$_} | Export-CSV 'C:\path\to\diff.csv' -Delimiter ';'

For details on Join (Join-Object), see: https://stackoverflow.com/a/45483110/1701026

answered Sep 06 '17 at 15:49

iRon

20,463
10
53
79

Extract differences of CSV files into a seperate file

2 Answers2