PowerShell - Update Variable Value from another Variable

Question

I am trying to update a number of values in $Table1 from another $Table2.

Say I have $Table1 (in this case an imported CSV file):

Model   ModelID   Blah
abc     0         Blah
ghi     0         Blah
mno     0         Blah

and I have $Table2 (in this case, obtained from a data source):

name    id
abc     11
def     12
ghi     13
jkl     14
mno     15
pqr     16
etc.

I am trying to update the values in $Table1."ModelID" from $Table2."id"

WHERE $Table1."Model" = $Table2."name"

In SQL, I would do something like:

UPDATE $Table1
SET ModelID = $Table2."id"
WHERE $Table1."Model" = $Table2."name"

How do I do conditional updates based on joins on columns in a Variable in PowerShell?

I was looking at:

-replace... (I can't seem to do conditional replaces based on joins)

Add-Member -MemberType NoteProperty "modelID" -Value ... (again, I can't seem to set the value based on joins)

foreach($item in $Table1)
{
    $Table1."ModelID" = $Table2."id" 
    where ?????
}.. (again, I can't seem to set the value based on joins)

Am I over-egging the pudding here?

Drew · Accepted Answer · 2018-11-23 04:49:25Z

1

This is VERY messy but it seems to get the job done.

$Table2 = import-csv C:\temp\test.csv
$Table1 = import-csv C:\temp\Test55.csv

Foreach($item in $Table1){
    Foreach($tab2 in $Table2){
        If($tab2.name -match $item.model){
            $item.ModelID = $tab2.id
        }
    }
}

answered Nov 23, 2018 at 4:49

Drew

4,0682 gold badges13 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Simon Over a year ago

Thank you SO much @Drew. Worked a treat.

Larry Song · Accepted Answer · 2018-11-23 04:55:03Z

1

Here is the code can help out I believe.

foreach($i in $t1)
{
    foreach($j in $t2)
    {
        if($i.'model' -eq $j.'id')
        {
            $i.'modelid' = $j.'name'
            break
        }
    }
}

For every item in table1, looking for the pattern in table2, if find a match, change the value in table1.

edited Nov 23, 2018 at 4:55

answered Nov 23, 2018 at 4:49

Larry Song

1,16410 silver badges17 bronze badges

Comments

user6811411 · Accepted Answer · 2018-11-23 11:41:21Z

1

A less dirty variant using a hash table to lookup the ID from Model.
Using here strings as source.

## Q:\Test\2018\11\23\SO_53440594.ps1
$table1 = @"
Model,ModelID,Blah
abc,0,Blah
ghi,0,Blah
mno,0,Blah
"@ | ConvertFrom-Csv

$Hashtable2 = @{}
@"
Name,Id
abc,11
def,12
ghi,13
jkl,14
mno,15
pqr,16
"@ | ConvertFrom-Csv | ForEach-Object {$Hashtable2.Add($_.Name,$_.Id)}

ForEach ($Row in $table1){
  if($Hashtable2.Containskey($Row.Model)){
    $Row.ModelID = $Hashtable2[$Row.Model]
  } else {
    "Model {0} not present in `$table2" -f $Row.Model
  }
}
$table1

Sample output:

Model ModelID Blah
----- ------- ----
abc   11      Blah
ghi   13      Blah
mno   15      Blah

answered Nov 23, 2018 at 11:41

user6811411

Comments

Lee_Dailey · Accepted Answer · 2018-11-23 17:31:52Z

here's an alternate method. it uses the "operate against the collection" technique [introduced in v4, i think]. a hashtable is still the fastest way to do this when the lookup list is large, tho. [grin]

$OneTable = @'
Model, ModelID, Blah
abc, 0, Blah
ghi, 0, Blah
mno, 0, Blah
'@ | ConvertFrom-Csv

# removed one line [ghi, 13] to allow for "no match" error test
$TwoTable = @'
Name, ID
abc, 11
def, 12
jkl, 14
mno, 15
pqr, 16
'@ | ConvertFrom-Csv

foreach ($OT_Item in $OneTable)
    {
    $Lookup = $TwoTable -match $OT_Item.Model
    if ($Lookup)
        {
        $OT_Item.ModelID = $Lookup.ID
        }
        else
        {
        Write-Warning ('No matching Model was found for [ {0} ].' -f $OT_Item.Model)
        }
    }

$OneTable

output ...

WARNING: No matching Model was found for [ ghi ].

Model ModelID Blah
----- ------- ----
abc   11      Blah
ghi   0       Blah
mno   15      Blah

Buxmaniak · Accepted Answer · 2018-11-23 19:27:52Z

1

What about this way ...

$Data = @"
Model,ModelID,Blah
abc,0,Blah
ghi,0,Blah
mno,0,Blah
"@ | ConvertFrom-Csv

$Reference = @"
Name,Id
abc,11
def,12
ghi,13
jkl,14
mno,15
pqr,16
"@ | ConvertFrom-Csv

$Data | ForEach-Object {
    $Local:ThisModelKey = $_.Model
    if ($Reference.Name -contains $ThisModelKey) {
    $_.ModelID = (@($Reference | Where-Object { $_.Name -like $ThisModelKey } ))[0].ID
    }
}

The result is ...

$Data

Model ModelID Blah
----- ------- ----
abc   11      Blah
ghi   13      Blah
mno   15      Blah

answered Nov 23, 2018 at 19:27

Buxmaniak

4782 silver badges5 bronze badges

Collectives™ on Stack Overflow

PowerShell - Update Variable Value from another Variable

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related