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

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?

CodePudding user response：

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.

CodePudding user response：

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.$_}

I hope this helps.