Can't get data out of ordered dictionary

Question

I'm trying to get data out of my ordered dictionary, and for some reason it's not printing the data in it. I can get to the first item in the data structure, but I'm having trouble iterating over all the rows. I'm using PowerShell 5.1 and visual studio code.

The code queries an mdb file, and returns multiple rows.

Function ProcessHelpMDB{
[cmdletbinding()]
  Param ([string]$mdbLookupError, [string]$mdbFilePath, [string]$mdbPrinterSeries) 
  Process
  {
   $adOpenStatic = 3
   $adLockOptimistic = 3
   $deviceTable = $mdbPrinterSeries + "PP"
   $mdbLookupError -Match '[0-9]*-([0-9]*)'
   $errLookup = $Matches[1]

    $selectQuery = “SELECT [$($deviceTable)].[HelpCode],
    [$($deviceTable)].[ScreenNumber],
    [$($deviceTable)].[TextID],
    [$($deviceTable)].[PictureID]
        FROM [$($deviceTable)]
        WHERE
            [$($deviceTable)].[HelpCode] = $($errLookup)"

        $cn = new-object -comobject ADODB.Connection
        $rs = new-object -comobject ADODB.Recordset
        $cn.Open("Provider = Microsoft.ACE.OLEDB.16.0;Data Source = $mdbFilePath") 

        $rs.Open($selectQuery,
        $cn, $adOpenStatic, $adLockOptimistic)
        $i=0
        $ret = [ordered]@{}
        if($rs.EOF)
        {
            Write-Host "$mdbLookupString not in $mdbFileName...record set returned emtpy for query"
        }#if
        else
        {  
            while($rs.EOF -ne $True)
            {

                $result = [ordered]@{}
                foreach ($field in $rs.Fields)
                {
                    $result[$field.name] = $field.Value
                }#result
                $newObject = [PSCustomObject]$result
                $ret.Add($i,$newObject) ###ordered dictionary needs key to add values
                $i++ 
                $rs.MoveNext()
            } #while
 
            Write-Host "retArr[] $($ret)" #prints retArr[] System.Collections.Specialized.OrderedDictionary
            Write-Host "retArr[0] $($ret[0,"TextID"])" #prints retArr[0]
            Write-Host "retArr[0] $($ret[0])" #prints retArr[0] @{PictureID=HELP_BLAH; TextID=HELP_INFO; HelpCode=9; ScreenNumber=1}
            foreach($row in $ret)
            {
                foreach($item in $row.value) #it's skipping these
                {
                    Write-Host $item #
                    Write-Host $item[0] #
                    Write-Host $item[0].'TextID' #
                    Write-Host $item.'TextID' #
                }#foreach
            }

            $cn.Close()
            return $ret
      } #end Process
    }# End of Function process mdb's

The data looks like this in the mdb table:

HelpCode   ScreenNumber    TextID                PictureID
1000           1           HELP_INFO             HELP_BLAH
1000           2           HELP_INFO2            HELP_BLAH2
...

My question is, how do I print out the data in the table, from the data structure? I'll need to access it later to re-piece it into a string with other data. My problem is that I can't figure out how to access each row. in the data structure, and each item individually in each row. I seem to know how to access the first row, but not other rows.

I've been looking at this most recently: looping over hash table

Update: I got the TextID to print for each row of ret as follows per comments below..thank you!!:

foreach($row in $ret.GetEnumerator()) #this is working for each row, using 
{
    Write-Host $row.TextID #prints nothing
    Write-Host $row.'TextID' #prints nothing
    Write-Host $($row.TextID) #prints nothing
    Write-Host $($row.'TextID') #prints nothing
    Write-Host $($row[0].TextID) #prints nothing
    Write-Host $($row[0].'TextID') #prints nothing
    Write-Host $($row[0].Value.TextID) #prints TextID value of current row
    Write-Host $($row[0].Value.'TextID') #prints TextID value of current row
}

Update2: I'm trying to data structure improvement suggested in the answer below. Thank you!! It's a great simplification.

$ret = [System.Collections.Generic.List[psobject]]::new() #different data struc
if($rs.EOF)
{
    Write-Host "$mdbLookupString not in $mdbFileName...record set returned emtpy for query"
}#if
else
{  
    while($rs.EOF -ne $True)
    {
        $result = [ordered]@{}
        foreach ($field in $rs.Fields)
        {
            $result[$field.name] = $field.Value
        }#result
        $newObject = [PSCustomObject]$result
        $ret.Add($newObject) ###
        $rs.MoveNext()
    } #while
}#else
Write-Host "retArr[] $($ret)" #prints retArr[] 
Write-Host "retArr[0] $($ret[0,"TextID"])" #prints retArr[0]
Write-Host "retArr[0] $($ret[0])" #prints retArr[0] @{PictureID=...; TextID=...; HelpCode=9; ScreenNumber=1}
$i=0
foreach($row in $ret) #goes thru all rows
{
    Write-Host "retArr[] $($ret)" #prints retArr[] 
    Write-Host "retArr[0] $($ret[$i,"TextID"])" #prints retArr[0] @{HelpCode=9; ScreenNumber=1; TextID=...; PictureID=...}
    Write-Host "retArr[0] $($ret[$i].TextID)" #prints retArr[0] HELP_...
    Write-Host "retArr[0] $($ret[$i].'TextID')" #retArr[0] HELP_...
    ####
    Write-Host "retArr[] $($row)" #prints retArr[] @{HelpCode=9; ScreenNumber=1; TextID=...; PictureID=HELP_...}
    Write-Host "retArr[0] $($row[$i,"TextID"])" #prints retArr[0]
    Write-Host "retArr[0] $($row[$i])" #prints retArr[0] @{HelpCode=9; ScreenNumber=1; TextID=HELP_...; PictureID=HELP_...}
    Write-Host "retArr[0] $($row[$i].TextID)" #prints retArr[0] HELP_...
    Write-Host "retArr[0] $($row[$i].'TextID')" #retArr[0] HELP_...
    $i++
}

foreach($row in $ret.GetEnumerator()) #this is working for each row
{
    Write-Host $row.TextID #prints HELP_...
    Write-Host $row.'TextID' #prints HELP_...
    Write-Host $($row.TextID) #prints HELP_...
    Write-Host $($row.'TextID') #prints HELP_...
    Write-Host $($row[0].TextID) #HELP_...
    Write-Host $($row[0].'TextID') #HELP_...
    Write-Host $($row[0].Value.TextID) #prints nothing
    Write-Host $($row[0].Value.'TextID') #prints nothing
}

This works great. Thanks for the help @mathias!

Update3: I'm trying the pipeline idea from @iRon comment, with update2 (optimized data structure):

$ret | ForEach-Object {
        Write-Host TextID= $($_.TextID) #prints TextID= HELP_...
        Write-Host TextID= $($_.'TextID') #prints TextID= HELP_...
        Write-Host TextID= $($_.Value.TextID) #prints TextID=
        Write-Host TextID= $($_.Value.'TextID') #prints TextID=
    }

Thanks iRon! That's a good way to access the data in the data structure. :)

Why not simply using the PowerShell pipeline: drop each pscustomobject on the pipeline and assign it to`$rey` which will give you and (ordered) array which can be enumerated or indexed., See for an example: https://stackoverflow.com/a/60708579/1701026 — iRon, Jan 25 '22 at 16:40
Thank you @iRon! See Update above. I will also try simplifying my data structure or pipeline next. — Michele, Jan 25 '22 at 17:06

Mathias R. Jessen · Accepted Answer · 2022-01-25T16:36:56.477

3

You can loop over each key-value entry in a dictionary by explicitly calling GetEnumerator() on it and passing the resulting enumerator to foreach or a pipeline-enabled cmdlet:

foreach($kvp in $ret.GetEnumerator())
{
  Write-Host "Key: $($kvp.Key) - Value: $($kvp.Value)"
}

That being said, as long as you're using successive integers as the keys, you might as well use a list instead of a dictionary:

# create a list instead of a dictionary
$ret = [System.Collections.Generic.List[psobject]]::new()

# ...
while($rs.EOF -ne $True)
{
    $result = [ordered]@{}
    foreach ($field in $rs.Fields)
    {
        $result[$field.name] = $field.Value
    }

    $newObject = [PSCustomObject]$result
    $ret.Add($newObject) # add object to list, no need to keep track of `$i` here.
    $rs.MoveNext()
} 

# ...

Lists are indexable the same as arrays, so you can do:

$ret[4] # this will get you the 5th row

edited Jan 25 '22 at 16:36

answered Jan 25 '22 at 16:29

Mathias R. Jessen

157,619
12
148
206

I tried the kvp foreach you have above, and it prints the first one only... Key:0 Value: @{HelpCode=9; ScreenNumber=1; TextID=HELP_INFO; PictureID=HELP_Blah}, and then it leaves the loop. But when I look at ret, it looks like it has more in there. I see [0, {TextID=....PictureID=...}] then [1, {TextID=...PictureID=...} and so on. Also, how would I access TextID explicitly, and not the whole {TextID=...PictureID=...} together. – Michele Jan 25 '22 at 16:43
I got it to print the values using GetEnumerator like Update above, but I will try your suggested data structure for ret. I realize it's simpler if I'm not using a hash. – Michele Jan 25 '22 at 17:03
1

@Michele When you use `GetEnumerator()`, the `Value` property will contain the custom object you created earlier, so `$kvp.Value.TextID` will give you just the value of that property, eg.: `Write-Host "The TextID of the object with key $($kvp.Key) is '$($kvp.Value.TextID)'"` – Mathias R. Jessen Jan 25 '22 at 17:07

Can't get data out of ordered dictionary

1 Answers1