How to have a Powershell Function return a table?

Question

I love using tables in my powershell scripts. Since it is always a lot of repeating code to create them if I need different tables, I decided to create a function that returns a fully functional table to me.

My try thus far looks like this:

Function MakeTable ($btab, $TableName, $ColumnArray) { $btab = New-Object System.Data.DataTable("$TableName") foreach($Col in $ColumnArray) { $MCol = New-Object System.Data.DataColumn $Col; $btab.Columns.Add($MCol) } } $acol = @("bob","wob","trop") $atab = $null MakeTable $atab "Test" $acol

Alternatively I tried:

Function MakeTable ($TableName, $ColumnArray) { $btab = New-Object System.Data.DataTable("$TableName") foreach($Col in $ColumnArray) { $MCol = New-Object System.Data.DataColumn $Col; $btab.Columns.Add($MCol) } return $btab } $acol = @("bob","wob","trop") $atab = MakeTable "Test" $acol

I tested both versions with the same code:

$aRow = $atab.NewRow() $aRow["bob"] = "t1" $aRow["wob"] = "t2" $aRow["trop"] = "t3" $atab.Rows.Add($aRow) $atab

Both sadly didn't do what I expected.

You cannot call a method on a null-valued expression. At line:13 char:1 + $aRow = $atab.NewRow() + ~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull

Can you help me?

EDIT:

$Global:atab = New-Object System.Data.DataTable("") $Global:btab = New-Object System.Data.DataTable("") Function MakeTable ($x, $TableName, $ColumnArray) { if($x -eq 1) { $xtab = $Global:atab } elseif($x -eq 2) { $xtab = $Global:btab } $xTab.TableName = $TableName foreach($Col in $ColumnArray) { $MCol = New-Object System.Data.DataColumn $Col; $xTab.Columns.Add($MCol) } } $acol = @("bob","wob","trop") MakeTable 1 "Test" $acol $aRow = $Global:atab.NewRow() $aRow["bob"] = "t1" $aRow["wob"] = "t1" $aRow["trop"] = "t1" $Global:atab.Rows.Add($aRow) $Global:atab

This is doing what I want, but not really. I think there is a much better way.

Whilst this may not be the actual problem, there are nevertheless a couple of errors. In the first function you don't return anything or appropriately create a reference so I don't see how $atab gets assigned. In the second, you return from within the foreach so only the first column will be added. — arco444
– arco444, Commented Jul 14, 2015 at 14:14
Hi, the return in the second one was actually not meant to be in the foreach loop. I fixed it - thank you. For the first issue: I thought I might give the function $atab as reference, thus it would maybe use it aliased as $btab, make it a table and so alter the $object outside the function. I tried it this way as I read somewhere that returning in Powershell is weird. — Asharon
– Asharon, Commented Jul 14, 2015 at 14:21

Fabian · Accepted Answer · 2015-07-14 15:05:32Z

To make your code work just add a comma after the return according to this solution.

"return , $btab" instead of "return $btab"

By default it is returning the enumerable contents of the DataTable, the DataRows, null in this case as no DataRows have been created yet.

The comma before the DataTable object ($btab) implies an array where $btab itself is an element. But nothing is supplied for the first element (to the left of the comma) so out of the pipe comes the only other element: the DataTable itself.

 Function MakeTable ($TableName, $ColumnArray) { $btab = New-Object System.Data.DataTable("$TableName") foreach($Col in $ColumnArray) { $MCol = New-Object System.Data.DataColumn $Col; $btab.Columns.Add($MCol) } return , $btab } $acol = @("bob","wob","trop") $atab = MakeTable "Test" $acol $aRow = $atab.NewRow() $aRow["bob"] = "t1" $aRow["wob"] = "t2" $aRow["trop"] = "t3" $atab.Rows.Add($aRow) $atab

Yes that did the trick, thank you a lot I didn't know about this behaviour with the return. This will be very helpful in the future :)

Dave Sexton · Accepted Answer · 2015-07-14 14:43:02Z

If the object of the exercise is to make things simple then it would be far easier to use a PSObject instead of a DataTable. Something like this:

function MakeTable ($ColumnArray) { $o = New-Object PSObject $ColumnArray | % {$o | Add-Member -MemberType NoteProperty -Name $_ -Value $null} $o } $atab = @() $atab += MakeTable(@('bob','wob','trop')) | % { $_.bob = 't1' $_.wob = 't2' $_.trop = 't3' $_ } $atab

Thank you this is indeed interesting. However Fabian gave me the solution I was searching for :) Thanks non the less.

Collectives™ on Stack Overflow

How to have a Powershell Function return a table?

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related