How to retrieve associated value from second CSV in Foreach

Question

How would I go about obtaining the value of a secondary field in a CSV, while foreaching results in another CSV?

Ex: List of computers, then search for serial in list, then get response.

CSV1 has list of computer names, serials ,and vendor IDs.
CSV2 has list of vendor IDS, and responsible party

Sample code:

$systems = import-csv -path "K:\systems.csv"
$vendors = import-csv -path "K:\vendors.csv"

$vendortable= @{}
$vendors | ForEach-Object {
  $vendortable[$_.vendorid] = New-Object -Type PSCustomObject -Property @{
    'ID' = $_.vendorid
    'Managed' = $_.Managed
  }
  }
  Foreach ($computer in $systems){
    $host = $system.Host
      if ($vendortable.key -match $system.vendorid){
          $xvendor = $vendortable.Managed
      }


        $vendorobj = New-Object PSObject -property @{
        'Host'=$System.host
        'Mgr=$xvendor

}
    $vendorobj |Select-object "Host","Mgr" |Export /////////

This returns an object of All values System.Object[] in the vendor table, not just one.

CSV1

  Host,Serial,VendorID
  A15,gtjk123,9001
  C15,gtjk456,6402
  T15,gtjk678,2301
  S15,gtjk103,0101

CSV2

  VendorID,Managed
  9001,Trancom
  6402,Stratus
  2301,Psycorp
  0101,Dell

See [what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026) for simular reusable solutions, e.g.: `$systems | Join $vendors -On VendorID` — iRon, Sep 24 '20 at 09:32
Why are you using -match to test for equality? Try using -eq instead. — Walter Mitty, Sep 24 '20 at 11:28

score 0 · Accepted Answer · answered Sep 24 '20 at 04:39

Let's break this down into steps.

You want to iterate over the entire vendor list.
You want to retrieve the associated host from CSV1 and combine with manager (based on your select statement)

What you can do is make a lookup table of CSV1 using Group-Object and the -AsHashTable parameter specifying the "key" as vendorid.

$csv1 = @'
Host,Serial,VendorID
A15,gtjk123,9001
C15,gtjk456,6402
T15,gtjk678,2301
S15,gtjk103,0101
'@ | ConvertFrom-Csv | Group-Object -Property vendorid -AsHashTable

Now you can run through each item of CSV2 and extract the required info.

$csv2 = @'
VendorID,Managed
9001,Trancom
6402,Stratus
2301,Psycorp
0101,Dell
'@ | ConvertFrom-Csv

$csv2 | foreach {
    [PSCustomObject]@{
        Host    = $csv1[$_.vendorid].host
        Managed = $_.managed
    }
}

Host Managed
---- -------
A15  Trancom
C15  Stratus
T15  Psycorp
S15  Dell

If you wanted to get all the properties

$csv2 | foreach {
    [PSCustomObject]@{
        Host     = $csv1[$_.vendorid].host
        Managed  = $_.managed
        VendorID = $_.vendorid
        Serial   = $csv1[$_.vendorid].serial
    }
}

Host Managed VendorID Serial 
---- ------- -------- ------ 
A15  Trancom 9001     gtjk123
C15  Stratus 6402     gtjk456
T15  Psycorp 2301     gtjk678
S15  Dell    0101     gtjk103

This works great. Made a few changes, and I have been able to load some more data. Thank you! — Akt, Sep 24 '20 at 06:37

How to retrieve associated value from second CSV in Foreach

CSV1

CSV2

1 Answers1