0

I am creating a CSV/array that extracts the difference between two other CSVs. After that, I need to compare the values in the new CSV/array to another CSV; if there are matching values between the third CSV and a fourth, I need mark whether the date associated to an ID is before or after the current date.

As an example:

File 3:
Column1, ID
XXXX, A1,
XXXX, B4,
XXXX, Z8

File 4:
ID, Date
A1, 12/1/2022
Z8, 1/1/2022

Output:
Column1, ID, Late?
XXXX, A1, Late
XXXX, B4, [Empty]
XXXX, Z8, Not-Late

A snippet of my code

$Previous = Import-CSV .\PreviousJobs.csv | Group-Object -AsHashTable -AsString -Property 'Job'
$Today = Import-CSV .\AllJobs.csv | Group-Object -AsHashTable -AsString -Property 'Job'
$Date = Import-CSV C:\Scripts\LTO\LTO.csv | Group-Object -AsHashTable -AsString -Property 'ID', 'Date'

## The following adds jobs into a new array to export to csv
$OnlyInToday = @()
ForEach ($Job in $Today.Values) 
{
    if (!$Previous[$Job.Job]) 
    {
        $OnlyInToday += $Job
    }
}

##The following SHOULD compare the part numbers stored in the new array to the LTO list, 
##if the numbers are in both lists, then compare the date in the LTO to the current date, and return either a late/on-time value
ForEach ($Job in $OnlyInToday.Values) 
{
    if  ($LTO[$Job.Part_Number])
        {
        if ($LTO[$Job.LTO] -lt (Get-Date).ToString("MM-dd-yyyy")) 
            {
            $Job | Add-Member -Name 'Late?' -Value "Not Late" -MemberType NoteProperty
            }
        if ($LTO[$Job.LTO] -gt (Get-Date).ToString("MM-dd-yyyy")) 
            {
            $Job | Add-Member -Name 'Late?' -Value "Late" -MemberType NoteProperty
            }
        }
}

$OnlyInToday | Export-CSV -NoTypeInformation NewJobs.csv
Paniom
  • 25
  • 7
  • Using this [`Join-Object script`](https://www.powershellgallery.com/packages/Join)/[`Join-Object Module`](https://www.powershellgallery.com/packages/JoinModule) (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `Import-Csv .\3.csv |FullJoin (Import-Csv .\4.csv) -On ID` and than do your "late" expression, e.g.: `... |Select-Object Column1, ID, @{ n='Late?'; e={ if ($_.Date) { if ((Get-Date) -lt $_.Date) { 'late' } else { 'not late' } } } }` – iRon Sep 08 '22 at 14:49

0 Answers0