PowerShell Tip: Specify Select Column List (Attributes/Properties) Dynamically

As we pipe stuff through a pipeline, we may have the need to add/remove/rename columns. These three operations are easy to do.

Remove/Add/Rename

Remove:

Here we remove all attributes returned from Get-Process except Handles and ProcessName by not selecting them

Get-Process |
   Select-Object -First 2 |
   Select-Object Handles, ProcessName

Handles ProcessName
------- -----------
218 AppleMobileDeviceService
226 ApplePhotoStreams

Alternatively, you could use the ExcludeProperty parameter if you have to select everything except a few properties

Get-Process |
   Select-Object -First 2 |
   Select-Object * -ExcludeProperty ProcessName, Handles, HandleCount

Add:

Here we add a new attribute name “NewAttribute” to the existing two attributes Handles and ProcessName.

Get-Process |
   Select-Object -First 2 |
   Select-Object Handles, ProcessName |
   Select-Object *, @{Label="NewAttribute";Expression={"New value"}}

Handles ProcessName NewAttribute
------- ----------- ------------
218 AppleMobileDeviceService New value
226 ApplePhotoStreams New value

Rename:

Get-Process |
   Select-Object -First 2 |
   Select-Object Handles, ProcessName |
   Select-Object @{Label='HandlesRenamed'; Expression={$_.Handles}},
      @{Label='ProcessNameRenamed'; Expression={$_.ProcessName}}

HandlesRenamed ProcessNameRenamed
-------------- ------------------
218 AppleMobileDeviceService
226 ApplePhotoStreams

Dynamic SELECT list: Simply pass an array!

To cut a long story short – Just pass in an string array containing the properties you want to SELECT.

Recently, I had a need to only select the properties that I knew already existed in the database and leave out properties that did not exist in the database. For examples, enhancements on the PowerShell side may have led objects to have more or less properties than what is in the database. If I needed to save the results to the database, the call will fail. However, if I selected just the properties from the object list that I know are in the database, then the save operation is bound to succeed.

It turns out that it is quite simple. You could specify an array with the properties you want to select.

Get-Process |
   Select-Object -First 2 |
   Select-Object @('Handles', 'ProcessName')

In the above code, if you notice, it only selects Handles and ProcessName properties. I could have also done it this way. Both produce the same results:

$columnsToSelect = @('Handles', 'ProcessName')

Get-Process |
   Select-Object -First 2 |
   Select-Object $columnsToSelect

Handles ProcessName
------- -----------
218 AppleMobileDeviceService
226 ApplePhotoStreams

In case, you were wondering about the code I used to select just the columns from the object that existed in my database table, here it is:

$stepName = "Select only the columns already in the target table and ignore extra columns (if table exists): [{0}]" -f $saveToTableName
#--------------------------------------------
#This will ensure that the function has the most compatibility when there are slight column variations in SQL statements 

$sql = "SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('{0}.{1}') " -f $saveToSchemaName, $saveToTableName

#Splat inputs (except SQL) and run the sql
$invokeParams = @{
                    ServerInstance = $saveToInstanceName
                    Database = $saveToDatabaseName
                    QueryTimeout = $QueryTimeout
                    ConnectionTimeout = $ConnectionTimeout
                    As = "PSObject"
                }

$dataTable = Invoke-DBASqlcmd @invokeParams -Query $sql

if ($dataTable -ne $NULL)
{
    $stepName = "Convert objects and table-specific properties to DataTable"
    $colsAlreadyInTable = ($dataTable | Select-Object -ExpandProperty name)

    $dataTableWAddlCols = $resultsWAddlCols |
                        Select-Object $colsAlreadyInTable |
                        Out-DbaDataTable `
                            -WarningAction: SilentlyContinue #Supress warnings about columns whose datatypes cannot be converted
}
else
{
    $stepName = "Convert objects and all of their properties to DataTable"
    #--------------------------------------------
    $dataTableWAddlCols = $resultsWAddlCols |
                        Out-DbaDataTable `
                            -WarningAction: SilentlyContinue #Supress warnings about columns whose datatypes cannot be converted

}

The above code is a snippet from my PowerShell function Export-QueryToSQLTable which allows you to export data from a set of queries across instances/databases to tables.

There is nothing fancy about this tip but I hope that it will be useful to someone, someday!