Function to Convert PowerShell Objects to DataTables


There’s many ways to store data in PowerShell but I find DataTables to be the most useful. The extra capabilities available are very flexible. I’ve made this function to quickly convert arrays, hashes, and other objects to DataTables quickly. I’ll often use this in combination with SQL connections to complete bulk copies of entire tables in one command, or even user that in combination with SQL merge statements to insert and update matched records. Download the script here

Updated 08/19/2014

I’ve updated the script to include parameter sets which allows you to select -Match or -NotMatch. This is used in the script like this; Select * | Get-Member -MemberType NoteProperty | Where-Object {$_.Name -match $Match}  #When you select -NotMatch you get {$_.Name -notlike $NotMatch}

By default the select statement with note properties normally returns what you want but some functions add some undesired extras. Using the -Match or -NotMatch option is so you can specify columns you do not want. Since Match and NotMatch are Regular Expressions you can use partial matches and seperate mutiple names with the pipeline character “|” as seen in the example below.  Check the column names to make sure you got the ones you want; $DataTable.columns.columnnames


$Users = Get-ADUsers -Server fitchaddc02 -Filter *
$DataTable = ConvertTo-DataTable -Source $Users -NotMatch "write|propert|object|psshow"
$DataTable.columns.columnname #To check the returned column names


function ConvertTo-DataTable
 $DataTable = ConvertTo-DataTable $Source
 An array that needs converted to a DataTable object
if ($NotMatch -eq ".+"){
$Columns = $Source[0] | Select * | Get-Member -MemberType NoteProperty | Where-Object {$_.Name -match "($Match)"}
else {
$Columns = $Source[0] | Select * | Get-Member -MemberType NoteProperty | Where-Object {$_.Name -notmatch "($NotMatch)"}
$DataTable = New-Object System.Data.DataTable
foreach ($Column in $Columns.Name)
 $DataTable.Columns.Add("$($Column)") | Out-Null
#For each row (entry) in source, build row and add to DataTable.
foreach ($Entry in $Source)
 $Row = $DataTable.NewRow()
 foreach ($Column in $Columns.Name)
 $Row["$($Column)"] = if($Entry.$Column -ne $null){($Entry | Select-Object -ExpandProperty $Column) -join ', '}else{$null}
#Validate source column and row count to DataTable
if ($Columns.Count -ne $DataTable.Columns.Count){
 throw "Conversion failed: Number of columns in source does not match data table number of columns"
 if($Source.Count -ne $DataTable.Rows.Count){
 throw "Conversion failed: Source row count not equal to data table row count"
 #The use of "Return ," ensures the output from function is of the same data type; otherwise it's returned as an array.
 Return ,$DataTable



Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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