Cfrgtkky

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?

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

$Table2 = import-csv C:temptest.csv

$Table1 = import-csv C:tempTest55.csv



Foreach($item in $Table1){

    Foreach($tab2 in $Table2){

        If($tab2.name -match $item.model){

            $item.ModelID = $tab2.id

        }

    }

}

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.

A less dirty variant using a hash table to lookup the ID from Model.

Using here strings as source.

## Q:Test20181123SO_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

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

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