Solving problems invented by others...
Build a custom table with PowerShell

Build a custom table with PowerShell

While programming PowerShell i often came across that i need some kind of custom table to store some data in. Because it is not always possible to format the output of one Cmdlet in a manner that i can have data from two different sources in one row.

I always use the DataTable object from the .NET Framework for this.

 

First, you start with creating the DataTable object and give it a name.

$table = New-Object system.Data.DataTable "MyTestTable"

This creates a new object of type „System.Data.DataTable“ and assign it to the variable $table. I never found a reason why it should be necessary to give a table a name. You have a variable to access it, why should this thing have a name? Call it a personal preference of mine to give every thing a name, when possible. 😉

 

Secondly, you define some rows and their data format.

$col1 = New-Object system.Data.DataColumn Name,([string])
$col2 = New-Object system.Data.DataColumn ZIPCode,([decimal])
$col3 = New-Object system.Data.DataColumn DateOfBirth,([datetime])

This creates the structure of the table and should be considered wisely. Wrong data types allocates unnecessary amount of data in memory. So if you are going to create big tables with thousands or millions of rows, spent some minutes think about the data types you use.

A list of possible data types can be found here:
https://msdn.microsoft.com/en-us/library/system.data.datacolumn.datatype(v=vs.110).aspx

 

Thirdly, you add these rows to your table.

$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)

This adds the previously defined structure to your existing table object. From this point on, your table object know how many fields and their data type a row must have.

 

Fourthly, you start creating your first row.

$row = $table.NewRow()

This ist done by simply telling the table object to create a new row for you. (Because the table „knows“ its structure.)

 

Fifthly, you put some date in the fields of your row.

$row.Name = „Michael“
$row.ZIPCode = 12345
$row.DateOfBirth = Get-Date -Year 1900 -Month 1 -Day 1 -Hour 0 -Minute 0 -Second 0

This fills the fields in your row. For the DateOfBirth field, i used the Get-Date cmdlet with parameters to create a datetime object for the date 1/1/1900 0:00:00.

 

Sixthly, you add your freshly created row to your table.

$table.Rows.Add($row)

This adds your row object as a new row to your table.
Now you can start creating new rows and add them to your table.

Leave a Reply

Your email address will not be published.

nine + one =