Author Posts

June 9, 2016 at 2:49 pm

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 2 years, 3 months ago by  Long S.
  • This topic was modified 2 years, 3 months ago by  Long S.

June 9, 2016 at 3:20 pm

-changed my code a bit

June 9, 2016 at 3:41 pm

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.

  • This reply was modified 2 years, 3 months ago by  Curtis Smith.

June 9, 2016 at 3:50 pm

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

June 9, 2016 at 4:53 pm

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

June 9, 2016 at 5:25 pm

my results were empty

June 9, 2016 at 9:01 pm

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?