Select-Object of multiple properties

Question

I am trying to find an elegant way to put the metadata of a table of type System.Data.DataTable into a multi-dimensional array for easy reference in my program. My approach to the issue so far seems tedious.

Assuming $DataTable being the DataTable in question

What I tried to do so far was:

$Types = $DataTable.Columns | Select-Object -Property DataType
$Columns= $DataTable.Columns | Select-Object -Property ColumnName
$Index = $DataTable.Columns | Select-Object -Property ordinal
$AllowNull  = $DataTable.Columns | Select-Object -Property AllowDbNull

Then painfully going through each array, pick up individual items and put them in my multi-dimensional array $TableMetaData.

I read in the documentation of Select-Object and it seems to me that only 1 property can be selected at 1 time? I think I should be able to do all the above more elegantly and store the information in $TableMetaData.

Is there a way to easily pick up multiple properties and put them in a multi-dimensional array in 1 swoop?

What do you mean by multi-dimensional array? A jagged array, or an actual multi-dimensional array? Please show your code for creating $TableMetaData — Mathias R. Jessen
– Mathias R. Jessen, Commented May 23, 2017 at 18:50
@mathia: like $TableMetaData[,,,] or something like $TableMetaData[][][] Is it possible? — user1205746
– user1205746, Commented May 23, 2017 at 18:52

Mathias R. Jessen · Accepted Answer · 2017-05-23 19:27:59Z

21

I read the documentation of Select-Object and it seems to me that only 1 property can be selected at 1 time?

This is not true, Select-Object can take any number of arguments to the -Property parameter

$ColumnInfo = $DataTable.Columns | Select-Object -Property DataType,ColumnName,ordinal,AllowDbNull

Now $ColumnInfo will contain one object for each column, having all 4 properties.

Rather than using a multi-dimensional array, you should consider using a hashtable (@{}, an unordered dictionary):

$ColumnInfo = $DataTable.Columns | ForEach-Object -Begin { $ht = @{} } -Process {
    $ht[$_.ColumnName] = $_
} -End { return $ht }

Here, we create an empty hashtable $ht (the -Begin block runs just once), then store each column object in $ht using the ColumnName as the key, and finally return $ht, storing it in $ColumnInfo.

Now you can reference metadata about each column by Name:

$ColumnInfo.Column2
# or 
$ColumnInfo["Column2"]

edited May 23, 2017 at 19:27

answered May 23, 2017 at 18:47

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user1205746 Over a year ago

hmm.. I thought I did the same thing and got an error.. I must have used -expandproperty then.

Mathias R. Jessen Over a year ago

@user1205746 -ExpandProperty takes only one string as it's argument, so that makes sense

user1205746 Over a year ago

Is it possible to convert that multi-dimensional array to something dictionary like? For example: Currently, if I would like to access columnName2 I have to get $ColumnInfo[1]. Is there a way to frame the output so that if I want to access the column, I only need to issue $ColumnInfo.ColumnName2?

user1205746 Over a year ago

That's awesome! This is exactly what I wanted! I have hard time trying to access the multi-dimension array. It does not work like C where I can use $TableInfo[0,1] to get item in row 1 column 2.

Mathias R. Jessen Over a year ago

See the first example again, $ht[$_.ColumnName]= $_ |Select-Object DataType,ColumnName,Ordinal,AllowDbNull :-)

|

Victor Ashiedu · Accepted Answer · 2022-05-23 20:53:32Z

3

One easy way to do this is to create an "empty" variable with Select-Object. Here is a sample command:

$DataTableReport = "" | Select-Object -Property DataType, ColumnName, ordinal,  AllowDbNull

Then, link the $DataTableReport to the $Types, $Columns, $Index, and the $AllowNull properties as shown below:

$DataTableReport.Types = $DataTable.DataType
$DataTableReport.Columns = $DataTable.ColumnName 
$DataTableReport.Index = $DataTable.ordinal 
$DataTableReport.AllowNull  = $DataTable.AllowDbNull

Finally, call the DataTableReport variable.

$DataTableReport # will display all the results in a tabular form.

answered May 23, 2022 at 20:53

Victor Ashiedu

393 bronze badges

Collectives™ on Stack Overflow

Select-Object of multiple properties

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related