Comparing two csv tables AND writing to one of them in the same time

Question

I'm having a logical problem that I cannot get behind myself.

I want to compare 2 csv tables, if a value from tableA is NOT found in tableB, the script should inform me by mail and write the according value to tableB for one-time notification.

TableA:

ID	Due_Date
2	26.09.2021 23:02:00
3	29.08.2021 03:52:00
4	10.11.2022 11:02:00
5	18.06.2021 02:48:00
6	09.03.2023 02:48:00
7	10.07.2021 02:48:00

TableB (initially empty):

ID
2
3
4

If I run the script for the first time, it should see, that TableB does not contain any or all of the data from TableA and so writes them to TableB. If I run the script for another time, the script should notice that there TableB now contains all data that TableA has and thus not write anything to it, because there's no difference at the moment (which could e.g. change 1 day later, when new entries will be added to TableA).

I have the problem, that when I execute the script for the second time, the data from TableA will still be added to TableB, although they already exist in it. I need this functionality to let the script send me an email x-days before the "date" data and only do it once, even if the script will be executed for another time. The main part of my code is this here:

    $CheckDate = Get-Date -f 'dd.MM.yyyy HH:mm' #get current date and reformat it, to compare later
    $TableA = Import-Csv -Path $TableA_path -Delimiter ";" #import tableA
    $TableB = Import-Csv -Path $TableB_path -Delimiter ";" #import tableB (can be empty yet or not)
            
        foreach ($row in $TableA)  {
        $due_Date = $row.date #Put the tabledate in a variable
        $Time_Difference = (New-Timespan -Start $CheckDate -End $due_Date).Days

        If (($Time_Difference -ge 0) -and ($Time_Difference -le 30)) { #if due date is 30 days or less from now

            :outer
            foreach ($id in $TableB)  {
                    if($id.id -ne $row.id){ #if the id from TableB is NOT in TableA
                    $row.id + ";" |Add-Content -Path $TableB #Add entry from TableA to TableB

                    $msg.subject = "Due date for id $id.id is close!" #Set Mail Subject
                    $msg.body = "Please take a look" #Set Mail Body
                    $smtp.Send($msg) #Send Mail
                    break :outer #break loop
                    }
                    else{
                    Write-Host("Everything fine")
                    }
                }                    
        }

}

My script is not only writing all the data from TableA to TableB AGAIN instead of just the difference, but also multiplying the amount of rows with every run.

I hope someone can help me out with this nested problem.

Answer 1

It doesn't look like there's any reason to do an action for each row, so I would recommend this instead:

$TableA = Import-Csv -Path $TableA_path | 
  Select *,@{l='Date';e={[datetime]$_.Due_Date}}
$TableB = Import-Csv -Path $TableB_path 
$Now = Get-Date

# if due date is 30 days or less from now
$Due_Soon = $TableA | Where {
  ($_.date -gt $Now) -and 
  ($_.date -lt $Now.AddDays(30))
}

# and not in tableB
$Adding = $Due_Soon | Where {$_.ID -Notin $TableB.ID}

# Add to tableB
$Adding | Export-Csv $TableB_path -Append

# And create notification
$msg.subject = "Due date(s) for id(s) $($Due_Soon.ID) soon!"

In your script, I don't see any reason to break, since you'll only return the first ID. You also want to keep better track of which rows are getting compared:

# simplified:
foreach ($A in $TableA) {
  foreach ($B in $TableB) {
    if ($B.id -ne $A.id) { 
      Add A to B
      # Here you were adding A if ANY row in B did NOT match.
}}}