[FIXED] How to merge 2 x CSVs with the same column but overwrite not append?

Issue

I've got this one that has been baffling me all day, and I can't seem to find any search results that match exactly what I am trying to do.

I have 2 CSV files, both of which have the same columns and headers. They look like this (shortened for the purpose of this post):

"plate","labid","well"
"1013740016604537004556","none46","F006"
"1013740016604537004556","none47","G006"
"1013740016604537004556","none48","H006"
"1013740016604537004556","3835265","A007"
"1013740016604537004556","3835269","B007"
"1013740016604537004556","3835271","C007"

Each of the 2 CSVs only have some actual Lab IDs, and the 'nonexx' are just fillers for the importing software. There is no duplication ie each 'well' is only referenced once across the 2 files.

What I need to do is merge the 2 CSVs, for example the second CSV might have a Lab ID for well H006 but the first will not. I need the lab ID from the second CSV imported into the first, overwriting the 'nonexx' currently in that column.

Here is my current code:

            $CSVB = Import-CSV "$RootDir\SymphonyOutputPending\$plateID`A_Header.csv"
            Import-CSV "$RootDir\SymphonyOutputPending\$plateID`_Header.csv" | ForEach-Object {
                $CSVData = [PSCustomObject]@{
                    labid = $_.labid
                    well = $_.well
                }
                
                If ($CSVB.well -match $CSVData.wellID) {
                    write-host "I MATCH"
                    ($CSVB | Where-Object {$_.well -eq $CSVData.well}).labid = $CSVData.labid
            }
            $CSVB | Export-CSV "$RootDir\SymphonyOutputPending\$plateID`_final.csv" -NoTypeInformation
        }

The code runs but doesn't 'merge' the data, the final CSV output is just a replication of the first input file. I am definitely getting a match as the string "I MATCH" appears several times when debugging as expected.

Solution

Based on the responses in the comments of your question, I believe this is what you are looking for. This assumes that the both CSVs contain the exact same data with labid being the only difference.

There is no need to modify csv2 if we are just grabbing the labid to overwrite the row in csv1.

$csv1 = Import-Csv C:\temp\LabCSV1.csv
$csv2 = Import-Csv C:\temp\LabCSV2.csv

# Loop through csv1 rows
Foreach($line in $csv1) {
    # If Labid contains "none"
    If($line.labid -like "none*") {
        # Set rows labid to the labid from csv2 row that matches plate/well
        # May be able to remove the plate section if well is a unique value
        $line.labid = ($csv2 | Where {$_.well -eq $line.well -and $_.plate -eq $line.plate}).labid
    }
}
# Export to CSV - not overwrite - to confirm results
$csv1 | export-csv C:\Temp\LabCSV1Adjusted.csv -NoTypeInformation

Answered By - Drew

Answer Checked By - Willingham (PHPFixing Volunteer)