none
Problem with my Powershell that finds differences between two .csv files

    Question

  • 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.

    Friday, March 30, 2012 3:53 PM

Answers

  • 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                                                      Host2

    With 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 <=
    

    • Edited by KazunMVP Friday, March 30, 2012 6:01 PM
    • Marked as answer by greavette Friday, March 30, 2012 6:34 PM
    Friday, March 30, 2012 5:55 PM

All replies

  • Compare-Object $Book1  $Book2-Property VMName -PassThru | ? {$_.SideIndicator -eq '=>'} | Select VMName,VMhost | Export-Csv C:\results.csv -NoType

    • Proposed as answer by Bigteddy Friday, March 30, 2012 4:31 PM
    Friday, March 30, 2012 4:24 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' -NoTypeInformation


    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Friday, March 30, 2012 4:49 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:44 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

    What's new in Powershell 3.0 (Technet Wiki)

    Friday, March 30, 2012 5:48 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                                                      Host2

    With 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 <=
    

    • Edited by KazunMVP Friday, March 30, 2012 6:01 PM
    • Marked as answer by greavette Friday, March 30, 2012 6:34 PM
    Friday, March 30, 2012 5:55 PM
  • Sorry, Kazun, I stand corrected.  I didn't know about the -SyncWindow parameter.

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Friday, March 30, 2012 6:25 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.

    Friday, March 30, 2012 6:35 PM