Create-Table function – working with Data Tables in PowerShell

In this article, I will share with you one of the PowerShell modules I wrote for myself which I use every day for daily tasks and in my other scripts to simplify working with data and presenting them in the script or export to CSV, Excel, etc.

The module is based on .NET Framework class “System.Data”. To create and work with a table containing a data you can use a PSObject but creating a data table using a .NET class you can modify much more settings and properties of table, column or row.

You can specify a data type for a column, for example, if you want to store a date and time in the column “Date” you do not need to present the data as a string to PSObject and re-convert this to the DateTime type if you want to reuse the data.

Using this function you can create a table of any size with columns with different data types (String, Array, DateTime, TimeSpan, etc.)

Using:

Create-Table -TableName {see description below} -ColumnNames {see description below}

  • TableName: you need to provide a table name you will use later as a name of the variable that contains data table.
  • ColumnNames: you need to provide column names in order that you want to have it in the table, separated by a comma (“,”).
    To set a data type for a column you need to use a “/?” after the column name and then specify a data type.
    Default data type is “String”.
Example:

Create-Table -TableName NameOfTheTable -ColumnNames Column1,Column2/?DataType1,Column3/?DataType2

Output configuration:

  • Column1 – Data Type is String
  • Column2 – Data Type is DataType1
  • Column3 – Data Type is DataType2

Tip:
You can provide column names as a variable. (String or Array)
(as a String): $ColumnNames = “Column1,Column2/?DataType1,Column3/?DataType2”
(as an Array): $ColumnNames = @(“Column1″,”Column2/?DataType1″,”Column3/?DataType2”)

Real life example:
Create-Table
Create-Table
Adding and modifying rows:

$NameOfTheTable.Rows.Add() – to add empty row
$NameOfTheTable.Rows.Add(ValueToColumn1,ValueToColumn2,ValueToColumn3) – to add the values to the table when creating the row you need to provide a values in appropriate data type separated by comma (“,”)
$NameOfTheTable.Rows[0].Column1 = “ValueToColumn1 – to modify the value of Column1 in first row

Full code of the function/module:
#region INFO
	#Version : 1.2
	#Author : Lipinski, Grzegorz
	#Date : August 3, 2017
#endregion
#region function Create-Table
	function Create-Table {
		#region Parameters
			param(
				[Parameter(Mandatory=$true)]
				[string]$TableName,
				[Parameter(Mandatory=$true)]
				$ColumnNames
			)
		#endregion
		#region Validate ColumnNames data type
			if ($ColumnNames.GetType().Name -eq "String") {
				$ColumnNames = $ColumnNames -split "," #convert provided string to array
			} elseif ($ColumnNames.GetType().BaseType.Name -ne "Array") {
				Write-Error "ColumnNames parameters accepts only String or Array value."
				break
			}
		#endregion
		#region Set variables
			$TempTable = New-Object System.Data.DataTable
			$Count = 0
		#endregion
		#region Temp Table construction
			if ($ColumnNames.count -ne 0) {
				do {
					Remove-Variable -Name datatype -ErrorAction SilentlyContinue -WarningAction SilentlyContinue
					$TempTable.Columns.Add() | Out-Null #add a column to the Temp Table
					#region if data type specified for current column
						if ($ColumnNames[$Count] -like "*/?*") {
							$datatype = $ColumnNames[$Count].Substring($ColumnNames[$Count].IndexOf("/?")+2)
							$ColumnNames[$Count] = $ColumnNames[$Count].Substring(0,$ColumnNames[$Count].IndexOf("/?"))
							if ($datatype -notlike "System.*") {
								$datatype = "System."+$datatype
							}
							$TempTable.Columns[$Count].DataType = $datatype
						}
					#endregion
					$TempTable.Columns[$Count].ColumnName = $ColumnNames[$Count] #set Temp Table empty column Name
					$TempTable.Columns[$Count].Caption = $ColumnNames[$Count] #set Temp Table empty column Caption
					$Count++ #change Count + 1 to select next Column Name to add into the Temp Table
				} until ($Count -eq $ColumnNames.Count)
			}
		#endregion
		#region Copy created Temp Table to the table with a name created by user and remove Temp Table
			Set-Variable -Name $TableName -Scope Global -Value (New-Object System.Data.DataTable)
			Set-Variable -Name $TableName -Scope Global -Value $TempTable
			Remove-Variable -Name TempTable -ErrorAction SilentlyContinue -WarningAction SilentlyContinue
		#endregion
    }
#endregion
	Export-ModuleMember -Function Create-Table #use only if you create a module (".psm1")

You will probably see more advanced real life usage in my next posts.
In case you have any questions, ideas or doubts feel free to leave a comment or contact me by contact form 🙂

I hope you enjoyed it 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.