In Powershell, how can I replace a value with its matching value from another csv?

user_2607624 0
Viewed 32

I have two csv files. Acts.csv contains the values I want to change:

Activities,personId
132137,35030;20001
132138,17776
132139,13780
132140,37209
132141,30067;5124;35030;17776;13780;20001;15545;37209;17190
132142,30067;5124;35030;17776;
132187,17776
132188,5124
132189,30067;5124;35030;17776;13780;20001

I want to change the personId to the corresponding value in the rand column of the file rands.csv below:

rand,personId
24830,30067
4557,5124
30795,35030
19711,17776
15481,13780
42181,20001
17331,15545
32468,37209
39411,17190

So, the output (first four lines anyway) should look like this:

Activities,personId
132137,30795;42181
132138,19711
132139,15481
132140,32468

This answer looks like a good start, but do I need to put some kind of loop in the find string?

[regex]::Replace($appConfigFile, "{{(\w*)}}",{param($match) $dictionaryObject[$($match.Groups[1].Value)]})
powershell-5.0 csv powershell replace
1 Answers

Yes, you need a loop for first result and some kind of search to look for replacements.

$acts = convertfrom-csv "Activities,personId
132137,35030;20001
132138,17776
132139,13780
132140,37209
132141,30067;5124;35030;17776;13780;20001;15545;37209;17190
132142,30067;5124;35030;17776;
132187,17776
132188,5124
132189,30067;5124;35030;17776;13780;20001" -Delimiter ','    

$rand = convertfrom-csv "rand,personId
24830,30067
4557,5124
30795,35030
19711,17776
15481,13780
42181,20001
17331,15545
32468,37209
39411,17190" -Delimiter ','

$acts | select -PipelineVariable act | foreach {
  # split personids in each input object
  $act.personid -split ';' | foreach {
    $rand | where personid -eq $_ # find object in rand by personid
  } | Join-String -Property rand -Separator ';' # join replacements back to a string
} | select @{n='Activities';e={$act.Activities}}, @{n='NewPerson';e={$_}}, @{n='OldPerson';e={$act.PersonId}}

Please note that order of values in new string is not guaranteed, and errors are not raised if there is no matching value.

user_19802280
Related