Problem with my Powershell that finds differences between two .csv files
-
Friday, March 30, 2012 3:53 PM
Hello,
I have a script that is attempting to find the differences between two .csv files. The output is supposed to list the VM Name and the Host the VM lives on.
My input files look like this:
VmmList.csv:
VMName VM1234 VM2345
Billing.csv:
VMName VMHost
VM1234 Host1
VM2345 Host1
VM9876 Host2
$Book1 = Import-Csv "C:\VmmList.csv" $Book2 = Import-Csv "C:\Billing.csv" foreach($Row in $Book2) { Foreach($Row1 in $Book1) { if($Row1.VMName -ne $Row.VMName) { $Host = $Row.VMHost } } $Data = $Row.VMName + "," + $Host Add-Content $Data -Path "C:\Combined.csv" }
My Billing.csv will have more VM's listed that my VmmList.csv. My combined.csv output file should list any VM's that are in Billing.csv and not in VmmList.csv. In the above example, I would expect to see VM9876 on Host2 in my output file.
When I run this powershell, it ouputs everything and not the differences.
Please advise what I've done wrong in this script.
Thank you.
All Replies
-
Friday, March 30, 2012 4:24 PM
Compare-Object $Book1 $Book2-Property VMName -PassThru | ? {$_.SideIndicator -eq '=>'} | Select VMName,VMhost | Export-Csv C:\results.csv -NoType- Proposed As Answer by BigteddyMicrosoft Community Contributor Friday, March 30, 2012 4:31 PM
-
Friday, March 30, 2012 4:49 PM
Although Kazun's suggestions is more concise, I suspect it relies on the VMM names being in the same order. The following code/logic avoids this by comparing each item in one collection with the entire other collection, using only one loop:
$Book1 = Import-Csv "C:\scripts\VmmList.csv" $Book2 = Import-Csv "C:\scripts\Billing.csv" $output = @() foreach ($item in $Book2) {if ($book1.vmname -notcontains $item.vmname) {$output += $item}} $output | Export-Csv 'Differences.csv' -NoTypeInformationGrant Ward, a.k.a. Bigteddy
-
Friday, March 30, 2012 5:44 PM
Thank you Big Teddy and Kazun for the assistance.
Kazun, Big Teddy is correct...I would compare each item in collection.
Big Teddy...I've tried your code, but it's returning everything from my billing.csv file instead of just returning the last line (sine that the VM that is not in the vmmlist.csv). Any ideas what might be wrong?
Thank you.
-
Friday, March 30, 2012 5:48 PM
I don't know why. I tested the code with the sample "csv" files you posted. It may have something to do with a difference between the real csv and the sample you posted.
Grant Ward, a.k.a. Bigteddy
-
Friday, March 30, 2012 5:55 PM
To compare object in same order add param -SyncWindow to the Compare-Object.
PS > $Book1 VMName ------ VM2345 VM1234 PS > $Book2 VMName VMHost ------ ------ VM1234 Host1 VM9876 Host2 VM2345 Host1 PS > Compare-Object $Book1 $Book2 -Property VMName -PassThru | ? {$_.SideIndicator -eq "=>"} | Select VMName,VMHost VMName VMHost ------ ------ VM9876 Host2With Param -SyncWindow
PS > $a=1,2,3 PS > $b=2,3,4 PS > Compare-Object $a $b InputObject SideIndicator ----------- ------------- 4 => 1 <= PS > Compare-Object $a $b -SyncWindow 0 InputObject SideIndicator ----------- ------------- 2 => 1 <= 3 => 2 <= 4 => 3 <= PS > $a=1,2,3 PS > $b=1,2,4 PS > Compare-Object $a $b -SyncWindow 0 InputObject SideIndicator ----------- ------------- 4 => 3 <= -
Friday, March 30, 2012 6:25 PMSorry, Kazun, I stand corrected. I didn't know about the -SyncWindow parameter.
Grant Ward, a.k.a. Bigteddy
-
Friday, March 30, 2012 6:35 PM
That did it Kazun...I've successfully compared both .csv files and my results file lists only the differences.
Much appreciative of your help.
.png)
