Passing $_ item from a string collection into a function called from Select-Object

Question

Okay... so I have a System.Data.DataRow object. I've gotten the column names out into a string collection. Then I want to get the corresponding values into a second collection.

I'm sure I'm just doing something dumb here, but I cannot for the life of me figure this out. Take the following code...

$cols = $drFrom.Table.Columns
$colNames = $cols | Where-Object { $_.ColumnName -ne "ROWID" } | Select-Object $_.ColumnName
Write-Host $colNames
$colValues = $colNames | Select-Object $drFrom.Item($_).ToString()

Note that the Write-Host is showing that $colNames contains the expected column names, and outputs this in the console:

CLIENTID MESSAGE AMOUNT PAIDBY PAIDBYEMAIL ACTIVE

So the intention then is to pipe $colNames into Select-Object, and for each column name, get the value from the DataRow. But for some reason, when it's running through the Select-Object, $_ seems to be returning as empty, instead of the string values that are known to exist in the $colNames collection. When it runs the 4th line, to do the piping, it throws this exception:

Exception getting "Item": "Exception calling "get_Item" with "1" argument(s): "'column' argument cannot be null.
Parameter name: column""
At D:\MyScripts\MyScript.ps1:145 char:5
+     $colValues = $colNames | Select-Object $drFrom.Item($_).ToString( ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], GetValueInvocationException
    + FullyQualifiedErrorId : CatchFromBaseParameterizedPropertyAdapterGetValue

I've also tried embedding the iteration variable in quotes, like so...

$colValues = $colNames | Select-Object $drFrom.Item("$_").ToString()

Then the exception changes accordingly, but still reflecting a value of empty for $_:

Exception getting "Item": "Exception calling "get_Item" with "1" argument(s): "Column '' does not belong to table .""
+     $colValues = $colNames | Select-Object $drFrom.Item("$_").ToStrin ...

How do I get the iteration variable to actually reflect the string values that are in $colNames when piping?

score 2 · Accepted Answer · answered May 28 '22 at 00:07

To dynamically define properties via Select-Object, you must use calculated properties, which involve a hashtable that defines a property name and a script block ({ ... }) that defines the property value, in which you can refer to the pipeline input object at hand via the automatic $_ variable.

However, you're not looking to define a calculated property, you're looking to output a calculated value, and for that you need to use a call to ForEach-Object:

$colNames = $cols | 
              Where-Object ColumnName -ne ROWID |
                ForEach-Object ColumnName

$colValues = $colNames |
               ForEach-Object { $drFrom.Item($_).ToString() }

Note that the first pipeline uses simplified syntax, both for the Where-Object and the ForEach-Object call.

Switching from `Select-Object` to `ForEach-Object` was the ticket; thanks so much! — eidylon, May 31 '22 at 15:17

jimhark · Answer 2 · 2022-05-28T20:46:05.280

You say

I want to get the corresponding values into a second collection.

And you have:

$cols = $drFrom.Table.Columns

$colNames = $cols | 
    Where-Object { $_.ColumnName -ne "ROWID" } | 
    Select-Object $_.ColumnName

Which works (but could be simplified).

Now there are 2 things you want to do:

Select columns
Convert columns to strings

It's much simpler to do these separately.

First select the properties you want:

$colValues = $drFrom |
    Select-Object $colNames

Now you have the columns you want in a PSCustomObject. This is important because we can use the hidden PSObject property:

$colValues.PSObject.Properties.Name |
    ForEach {$colValues.$_ = [string]$colValues.$_}

Here I've switched from calling .ToString() to using the cast operator, [string], because it handles $null without error.

I think that will give you what you want.

Your calculation of column names could be simplified to

$colNames = drFrom.Table.Columns.ColumnName | 
    Where-Object {$_ -ne "ROWID"}

If there is any possibility drFrom is a list, then:

$colNames = drFrom[0].Table.Columns.ColumnName | 
    Where-Object {$_ -ne "ROWID"}

When you just want to eliminate columns, which seems to be the case, you can simplify to:

$colValues = $drFrom |
    Select-Object * -ExcludeProperty "ROWID"

And convert remaining properties to strings:

$colValues.PSObject.Properties.Name |
    ForEach {$colValues.$_ = [string]$colValues.$_}

When you write:

I want to get the corresponding values into a second collection.

It's not clear in what form you want that collection. Above I generated an object with named properties because I find that's more usable in PowerShell. The code you provided seemed to be attempting to pull out a list of strings. If that's what you want, then replace the last code line above with:

$stringList = $colValues.PSObject.Properties.Name |
    ForEach {[string]$colValues.$_}

I hope this helps.

Passing $_ item from a string collection into a function called from Select-Object

2 Answers2