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

3

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

edited Jul 14, 2015 at 15:05

answered Jul 14, 2015 at 14:48

Fabian

2,08615 silver badges13 bronze badges

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

1 Comment

Asharon Over a year ago

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

1

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

answered Jul 14, 2015 at 14:43

Dave Sexton

11.2k4 gold badges46 silver badges57 bronze badges

1 Comment

Asharon Over a year ago

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

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related