Comparing 2 csvs to get the difference

This topic contains 6 replies, has 3 voices, and was last updated by Profile photo of Curtis Smith Curtis Smith 6 months ago.

  • Author
    Posts
  • #42443
    Profile photo of Long S
    Long S
    Participant

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

    • This topic was modified 6 months ago by Profile photo of Long S Long S.
    • This topic was modified 6 months ago by Profile photo of Long S Long S.
  • #42452
    Profile photo of Long S
    Long S
    Participant

    -changed my code a bit

  • #42454
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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
    Profile photo of Long S
    Long S
    Participant

    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
    Profile photo of random commandline
    random commandline
    Participant

    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
    Profile photo of Long S
    Long S
    Participant

    my results were empty

  • #42494
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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?

You must be logged in to reply to this topic.