CODEWITHSUNDEEP
csvpowershell
Aaron Tollefsrud
Aaron Tollefsrud

Reputation: 3

Compare two csv files and deduct matches from original

Given two csv files: 

File1.csv
SKU,Description,UPC
101,Saw,101010103
102,Drill,101010102
103,Screw,101010101
104,Nail,101010104

File2.csv
SKU,Description,UPC
100,Light,101010105
101,Saw,101010103
104,Nail,101010104
106,Battery,101010106
108,Bucket,101010114

I'd like to create a new csv file, we'll call UpdatedList.csv, that has every entry from File1.csv minus any rows where the SKU is in both File1.csv and File2.csv. In this case UpdatedList.csv will look like

UpdatedList.csv
"SKU","Description","UPC"
"102","Drill","101010102"
"103","Screw","101010101"

The following code will do what I want but I believe there is a more efficient way. How can I do this without loops? My code is as follows. 

#### Create a third file that has all elements of file 1 minus those in file 2 ###
$FileName1 = Get-FileName "C:\LowInventory"
$FileName2 = Get-FileName "C:\LowInventory"
$f1 = ipcsv $FileName1
$f2 = ipcsv $FileName2
$f3 = ipcsv $FileName1
For($i=0; $i -lt $f1.length; $i++){
For($j=0; $j -lt $f2.length; $j++){
if ($f1[$i].SKU -eq $f2[$j].SKU){$f3[$i].SKU = 0}
}
}
$f3 | Where-Object {$_.SKU -ne "0"} | epcsv "C:\LowInventory\UpdatedList.csv" -NoTypeInformation
Invoke-Item "C:\LowInventory\UpdatedList.csv"
################################

Upvotes: 0

Views: 84

Answers (1)

Jared Windover-Kroes
Jared Windover-Kroes

Reputation: 561

You can do this without loops by taking advantage of the Group-Object cmdlet:

$f1 = ipcsv File1.csv;
$f2 = ipcsv File2.csv;
$f1.ForEach({Add-Member -InputObject $_ 'X' 0}) # So we can select these after
$f1 + $f2                  | # merge our lists
    group SKU              | # group by SKU
    where {$_.Count -eq 1} | # select ones with unique SKU
    select -expand Group   | # ungroup
    where {$_.X -eq 0}       # where from file1

Upvotes: 1

Related Questions