powershell replace entire value if character exists-CodePudding

would need assistance on my Powershell task.

Im working on updating a column value in a csv file based on a reference table in a different csv file. For example, below is how the table is laid out;

file1

environment | team

storage prod, (empty)

storage test, (empty)

master prod, (empty)

master mds, (empty)

file2

environment | team

storage, geodata

master, fsd

desired file output

environment | team

storage prod, geodata

storage test, geodata

master prod, fsd

master mds, fsd

Atm, my current method is only replacing the first character, but the character that follows remains. Example; storage prod, geodata prod. Please assist.

This is my working,

foreach ($team1 in $report){
$matched = $false
    foreach ($team2 in $reference){
    $obj = "" | select "Team","Environment"
    if(($team1.'Environment') -eq ($team2.'Environment'){
        $matchCounter  
        $matched = $true
        $obj.'Team' = $team2.'Team'
        $obj.'Environment' = $team1.'Environment'
        $obj | Export-Csv -Path "C:\Output" -Append -NoTypeInformation
      }
   }
}

CodePudding user response：

You can import the data from file1, then loop over the rows in file2 and where there is a match found in the environment column, update the data with the value of team like below:

$data = Import-Csv -Path 'D:\Test\file1.csv'
foreach ($row in (Import-Csv -Path 'D:\Test\file2.csv')) {
    $data | Where-Object { $_.environment -match [regex]::Escape($row.environment) } | 
            ForEach-Object {  $_.team = $row.team }
}

# show on screen
$data | Format-Table -AutoSize

# output to new csv
$data | Export-Csv -Path 'C:\Output\combinedData.csv' -NoTypeInformation

Using [regex]::Escape($row.environment) is not needed in your example data, but in real life, that value could contain characters that have special meaning in regex (-match uses regex). If you don't want to use regular expressions, the same can be achieved with
Where-Object { $_.environment -like "*$($row.environment)*" }

Output on screen:

environment  team   
-----------  ----   
storage prod geodata
storage test geodata
master prod  fsd    
master mds   fsd

CodePudding user response：

Follow up from this, forgot to mention that perhaps some of the cells in file1 might not match with file2. Therefore need help for this as well. For example,

file1 

environment  team   
-----------  ----   
wgs prod 
wgs test 

file2

environment  team   
-----------  ----   
(no wgs's team reference)

So instead I would like go through each row, and have the existed one replace, then as one with no reference, will simply have empty cell. The output then look something like this;

environment  team   
-----------  ----   
storage prod geodata
storage test geodata
wgs prod
wgs test
master prod  fsd    
master mds   fsd