Only write over data to csv line without losing previous data?

I have a starting csv with sample data like so:

Underlying,AllBlue,AllRed
AUD,1/5/2024,
BRR,,
GBP, 3/10/2024,
CAD,,

Today, 2024/04/29, via another process, a new CSV is produced, like this for example

Underlying,AllBlue,AllRed
AUD,,
BRR,4/29/2024,
GBP,4/29/2024,
CAD,,4/29/2024

and I'm looking to append any new data found in columns 'AllBlue' and 'AllRed' only, to the original csv. Desired outcome would be

Underlying,AllBlue,AllRed
AUD,1/5/2024,
BRR ,4/29/2024,
GBP,4/29/2024,
CAD,,4/29/2024

Notice how the new data from 2024/04/29 csv is overwriting to original. Also, how the GBP/AllBlue cell is updated with the new date.


#sandbox testing
$CSVFiles = Get-ChildItem -Path "C:\Sandbox\test" -Filter "*.csv"
 
#put into array
$CSVData = @()

ForEach ($CSVFile in $CSVFiles) {
    $CSVContent = Import-Csv -Path $CSVFile.FullName 
    $CSVData += $CSVContent 
}
 
#output
$CSVData | Export-Csv -Path "C:\Sandbox\testoutput\colors.csv" -NoTypeInformation -Append

This is what I'm currently getting back though.

"Underlying","AllBlue","AllRed"
"AUD","1/5/2024",""
"BRR","",""
"GBP","3/10/2024",""
"CAD","",""
"AUD","",""
"BRR","4/29/2024",""
"GBP","4/29/2024",""
"CAD","","4/29/2024"

This appends the data, but doesn't overwrite the columns?. I've tried multiple parameters clobber, append.

Solution

This is one way you can get that desired output. Inline comments should help with the thought process.

# Import-Csv here instead
$sourceCsv = ConvertFrom-Csv @'
Underlying,AllBlue,AllRed
AUD,1/5/2024,
BRR,,
GBP, 3/10/2024,
CAD,,
'@

# Import-Csv here instead
$newCsv = ConvertFrom-Csv @'
Underlying,AllBlue,AllRed
AUD,,
BRR,4/29/2024,
GBP,4/29/2024,
CAD,,4/29/2024
ThisIsALineToAppend,,4/29/2024
'@

# create a hashtable using the Underlying property as keys
$map = $sourceCsv | Group-Object Underlying -AsHashTable

$linesToAppend = foreach ($line in $newCsv) {
    # if the Underlying value of the new Csv exists in the old CSV
    if ($map.ContainsKey($line.Underlying)) {
        # get the object having the same Underlying value of the old CSV
        $value = $map[$line.Underlying][0]
        # if the AllBlue of the new CSV is not null
        if (-not [string]::IsNullOrWhiteSpace($line.AllBlue)) {
            # update the object of the old CSV with this new value
            $value.AllBlue = $line.AllBlue
        }
        # if the AllRed of the new CSV is not null
        if (-not [string]::IsNullOrWhiteSpace($line.AllRed)) {
            # update the object of the old CSV with this new value
            $value.AllRed = $line.AllRed
        }

        # go to the next iteration
        continue
    }

    # else, this Underlying doesn't exist in the old CSV
    # so its gotta be appended
    $line
}

$sourceCsv + $linesToAppend | ConvertTo-Csv