Where-Object not finding match but it should

Question

I have Code that imports and excel spreadsheet like this, and I've used this for other excel files fine:

$importedExcel = Import-Excel -Path $errorCodeListFilePath -StartRow $startRow
return $importedExcel

The returned map is stored like this:

[Object[178]]
...
[2]:
Contents:"some string"
ErrorCode Upper: 12
ErrorCode Lower: "3A"
Error Item: "some more info"
Recovery: "important recovery info"
Recovery Method Info: "A12"
...

Where sometimes ErrorCode Upper is a string and sometimes it's a number, and same for Lower.

$deviceErrDescMap = Process_ErrorDescMap -errorCodeListFilePath $errorCodeListFile

And the data looks as expected, shown above (excel returned in code snippet above).

For this excel file, I think it's confused because some data is an int and some is a string, so I think my where-object is missing the match. For this one, I had to split a hex number into the first two and second two digits, because the spreadsheet splits them across two columns.

                  $sdkNum = "0x123A"
                  $sdkNumArr = @($sdkNum -split 'x') 
                  $sdkNumArr1 =  $sdkNumArr[1] -split ''
                  $sdkNumUpper = "$($sdkNumArr1[1])$($sdkNumArr1[2])" #12 ..naming is counter-intuitive
                  $sdkNumLower = "$($sdkNumArr1[3])$($sdkNumArr1[4])" #3A
                  $deviceErrDescRow = $deviceErrDescMap | Where-Object {([string]'ErrorCode Upper' -eq $sdkNumUpper)} #returns no results

When I hover over $sdkNumUpper" I see "12", so it looks like a string. Can't I cast the spreadsheet/map content to string like I'm showing? I'm having trouble finding this info with an internet search. But something must be wrong with my Where-Object, because even though I see the row is there, it's returning null for $deviceErrDescRow.

I'm guessing you need to use the `-NoNumberConversion 'ErrorCode Upper'` parameter with `Export-Excel` when exporting the file so that when you import it it's interpreted as a string and not a number — Santiago Squarzon, Apr 01 '22 at 15:23
I don't export the spreadsheet. It's just a file in my directory. — Michele, Apr 01 '22 at 15:25
I tried selecting both rows of the spreadsheet and changing the format to text and it's still not getting the match with where-object when I run the script — Michele, Apr 01 '22 at 15:32
I tried forcing the spreadsheet row to a string with a single quote in front of the number, and removed [string] from my where-object, and it's still not matching the row — Michele, Apr 01 '22 at 15:44

mklement0 · Accepted Answer · 2022-04-01T18:18:52.723

The problem is the syntax of your Where-Object call:

... | Where-Object {([string]'ErrorCode Upper' -eq $sdkNumUpper)}

You're comparing the string literal 'ErrorCode Upper' to $sdkNumUpper, not the value of the .ErrorCode Upper property.

If you're using Where-Object with a script block ({ ... }), you need to refer to the input object at hand via the automatic $_ variable

... | Where-Object { $sdkNumUpper -eq $_.'ErrorCode Upper' }

Note that by placing the string-valued $sdkNumUpper on the LHS, the property value on the RHS is implicitly converted to a string too, if needed.

By contrast, when you use the syntactically easier simplified syntax, the property access is always the LHS, and only the property name must be specified, which is implicitly applied to the input object object at hand (in other words: application to $_ is implied).

# Note: 'ErrorCode Upper' binds to parameter -Property, $sdkNumUpper to -Value
# Equivalent to:
#   ... | Where-Object { $_.'ErrorCode Upper' -eq $sdkNumUpper }
... | Where-Object 'ErrorCode Upper' -eq $sdkNumUpper

Thank you! I'm not sure why this worked for all the other import-excel where-object statements but this one! Is it just because I'm using {} this time? — Michele, Apr 01 '22 at 16:29
@Michele, yes; in short: if you transition from simplified syntax to using a script block, you must place `$_.` before the property name. — mklement0, Apr 01 '22 at 16:40

Where-Object not finding match but it should

1 Answers1