How to take a file with 2 columns and compare the first column to a CSV and update the CSV to the second column data in PowerShell?

Question

There may be another method to accomplish this, but here are my current thoughts. I want to replace specific values in a column of a CSV file. The CSV has unneeded text in front of some values, capitalization is inconsistent, so I want to clean it up.

I created a User.txt file with "OldData","Built By" as a header line. I figure this method would be cleanest. When new employees are added, or old ones leave, I can just edit this User.txt file instead of updating the PowerShell directly.

Example User.txt

"OldData","Built By"
"Group-FirstName1","Initials1"
"firstname2","Initials2"
"First Name3","Initials3"
"RandomText-FirstName4","Initials4"

Data.csv is generated automatically with 9 columns.

"Column1","Column2","Column3","Column4","Column5","Column6","Column7","Column8","Built By"

The Built By column will have a value equal to OldData in my User.txt file. I would like to replace OldData to Built By in Data.csv.

I've used the Import-CSV command in various ways, but I don't have a specific example using a separate .txt file with the User information.

The code below is removing everything in the Built By column.

$Users = Import-Csv -Path "D:\Users.txt"
$Data = Import-Csv "D:\Data.csv"
ForEach($Record in $Data){
    $MatchedValue = (Compare-Object $Users $Record -Property "OldData" -IncludeEqual -ExcludeDifferent -PassThru).value
    $Record = Add-Member -InputObject $Record -Type NoteProperty -Name "Built By" -Value $MatchedValue -Force
}
$Data|Export-Csv "D:\Data-Updated.csv" -NoTypeInformation

Please let me know if you need additional information. This is my first time posting to a forum like this!

Thanks, Adam

Answer 1

The way to approach this kind of problem is usually with a dictionary type, a hash table in this case, that you can use as lookup table or correlation table.

$map = @{}
# create a dictionary that will be used later on for replacements
foreach ($row in Import-Csv User.txt) {
    $map[$row.OldData] = $row.'Built By'
}

Import-Csv 'Data.csv' | ForEach-Object {
    # if the value of Built By exists in User.txt -> OldData
    if ($map.ContainsKey($_.'Built By')) {
        # assign the Built By value from User.txt
        $_.'Built By' = $map[$_.'Built By']
    }
    else {
        # the value doesn't exist in User.txt, so assign "Missing"
        $_.'Built By' = 'MISSING'
    }

    # output the object (the row)
    $_
} | Export-Csv 'Data-Updated.csv' -NoTypeInformation