Update record file 2 based upon info from file 1

HI
during the offboarding of users I get the original request stating that user with employeeID 1 is leaving on 30-04-2023, this is stored in file 1.it might very well happen that after a little while I get an update that user with employeeID 1 is now leaving on 30-05-2023. this is stored in file 2
What would be the best scenario to update file 1 lastworkdate with the lastworkdate from file 2.
I was thinking of using the foreach or even get file content compare and replace.
The foreach option will get the user where the lastworkdate is different but I would now need to replace it with the updated value in in file 1.

$update = import-csv "C:\Users\onboarding_it.gen\Documents\temp\Updateleaver_db.csv"
$db = import-csv "C:\Users\onboarding_it.gen\Documents\temp\offboarding_db.csv"

$test = foreach($UpdateRow in $Update){
    $UpdatePn = $UpdateRow.Pn
    $UpdateLastWorkdate = $UpdateRow.Lastworkdate
    if($UpdatePn -in $Db.pn -and $UpdateLastWorkdate -ne $db.Lastworkdate){
        [PSCustomObject]@{
            PN = $UpdateRow.Pn 
            Name = $UpdateRow.Name
            samaccountname = $UpdateRow.samaccountname
            Email = $UpdateRow.Email
            Manager = $UpdateRow.Manager
            OfficeLocation = $UpdateRow.OfficeLocation
            Lastworkdate = $UpdateLastWorkdate
            RequestID = $UpdateRow.RequestID
            MsgID = $UpdateRow.msgID
            Treated = $UpdateRow.treated
            treatmentdate = $today
            }
    }

}
$test |export-csv "C:\Users\onboarding_it.gen\Documents\temp\offboarding_db_chk.csv" -NoTypeInformation 

I did put the differences in a seperate file at the moment but need to incorporate the updated value in the db file

Hello,
Firstly, I will use hashtables as comparing is much more efficient
Then I will loop on my target data $db because … this is the data we want to update

$db = import-csv "C:\Users\onboarding_it.gen\Documents\temp\offboarding_db.csv"
$update = import-csv "C:\Users\onboarding_it.gen\Documents\temp\Updateleaver_db.csv" | -Property pn -AsHashTable

$test = foreach ($row in $db){
    $Target = $update[$row.pn]
    if ($Target -and $Target.Lastworkdate -notlike $row.Lastworkdate) {
      write-host Updating $row.pn to $Target.Lastworkdate
      $row.Lastworkdate = $Target.Lastworkdate
    }
}
$db |export-csv "C:\Users\onboarding_it.gen\Documents\temp\offboarding_db_updated.csv" -NoTypeInformation 

I know this was not your question and I am certainly not suggestion you do not learn how to do what you are asking about, just offering some food for thought.

Has your organization considered a proper identity management tool? Something like Microsoft Identity Manager (MIM) or similar? These types of tools automate the management of the life cycle of an ID and are ideal for handling changes like you are trying to account for. They do require a lot of work to setup, but they really do pay for them self. There is a lot of repetition in managing IDs, and these tools make life easier by doing that work for you, once configured to your business’s needs.

Hi Matt,
thanks for the info will take a look at that
best regards
Paul

Hi Zamoth,
thanks for your reaction, when I try your code I get this error message
-Property : The term ‘-Property’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
please advise
Paul

I’ve modified your code slightly which is now looking like this

$db = import-csv "C:\Users\onboarding_it.gen\Documents\temp\test\offboarding_db.csv"
$update = import-csv "C:\Users\onboarding_it.gen\Documents\temp\test\Updateleaver_db.csv" | Group-Object PN -AsHashTable


$test = foreach ($row in $db){
    $Target = $update[$row.pn]
    if ($Target -and $Target.Lastworkdate -notlike $row.Lastworkdate) {
      write-host Updating $row.pn to $Target.Lastworkdate
      $row.Lastworkdate = $Target.Lastworkdate
    }
}
$db|export-csv "C:\Users\onboarding_it.gen\Documents\temp\offboarding_db_updated.csv" -NoTypeInformation 

now I can see that the if statement is working but it doesn’t write the changes to the db file for some reason
and shouldn’t $test be piped into the export-csv?