Comparing 2 csvs to get the difference

Welcome Forums General PowerShell Q&A Comparing 2 csvs to get the difference

This topic contains 6 replies, has 3 voices, and was last updated by

 
Participant
2 years, 5 months ago.

  • Author
    Posts
  • #42443

    Participant
    Points: 0
    Rank: Member

    I have two csvs that I am using to get the difference in status

    my first csv is something like
    past.csv
    STIG, VKEY, Status
    Windows 7, V-12345, Closed
    Windows 7, V-12345, Closed
    Unix, V-6789, Open

    my second csv is something like
    current.csv
    STIG, VKEY, Status
    Windows 7, V-12345, Open
    Windows 7, V-12345, Closed
    Unix, V-6789, Closed

    I am trying to create a code that reads both and output in a separate csv the lines that show a change from open to close and close to open.

    So my new csv will be
    STIG, VKEY, Status
    Windows 7, V-12345, Open
    Unix, V-6789, Closed

    This is my code where I got stuck

    It is currently displaying all the lines like:
    STIG , Vkey , Status
    Windows 7, V-12345, {@{Status=Closed....
    Windows 7, V-12345, {@{Status=Closed....
    Unix, V-6789, {@{Status=Open....

  • #42452

    Participant
    Points: 0
    Rank: Member

    -changed my code a bit

  • #42454

    Participant
    Points: 0
    Rank: Member

    You have some logic issues here

        $Keymatch = $Current| where {$_.vkey -eq $vkey}
        $statusdifference = $current | where {$_.status -eq $status}
        $currentstatus = $Current | where {$_.status} | select status
    

    Your $Keymatch is good because it's finding the object or objects in the current file set that matches the current item being evaluated in the Past file

    Your $statusdifference is a problem, however. Rather than looking at the one object that matches your past object, it's looking at all object in the current file that have the same status as your past object. This is going to build a collection of object, many of which are not related to the current past object, but do have the same status.

    Your $currentstatus is also a problem. Rather than just getting the status of the object from the current file, your are getting all objects from the current file that have the current file that have the same status as past object currently being evaluated.

    It should look more like this

    #Find object in the current file that matches vkey of object from the past file
        $Keymatch = $Current| where {$_.vkey -eq $vkey}
    
    #Check if the object found in the current file has the same status as the object found in the past file
        $statusdifference = $keymatch.Status -eq $key.Status
    
    #Get the status of the object found in the current file
        $currentstatus = $keymatch.Status
    

    You are going to still have some challenges if the sample data is correct and you have duplicate vkeys, but this should at least put you on the right track.

  • #42466

    Participant
    Points: 0
    Rank: Member

    I changed up my code a bit can you look at the post below my original... the site wouldn't allow me to modify my original

  • #42472

    Participant
    Points: 10
    Rank: Member

    If I am understanding your issue, this should work.

    $past = import-csv .\past.csv
    $current = import-csv .\current.csv
    
    Compare-Object -ReferenceObject $past -DifferenceObject $current -Property Status -PassThru | 
    Where-Object {$_.SideIndicator -eq '=>'} |Select-Object STIG,VKEY,Status |export-csv .\new.csv -NoTypeInformation
    
  • #42474

    Participant
    Points: 0
    Rank: Member

    my results were empty

  • #42494

    Participant
    Points: 0
    Rank: Member

    Hi Long,
    Your second posting has logic issues as well. There first thing that needs to be answered is, is the sample data correct? Do you in fact have two entries in the same file with the same vkey? If so, you cannot use this as your primary key for lookup on a one to one comparison. If that is the case, should it be done on a line by line comparison? Does line 1 in Past always have the same record as line 1 in Current, same for line 2 in past and current, etc?

The topic ‘Comparing 2 csvs to get the difference’ is closed to new replies.