Function to Convert PowerShell Objects to DataTables

ConvertTo-DataTable
Standard

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

EXAMPLE:

$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

CODE:

function ConvertTo-DataTable
{
 <#
 .EXAMPLE
 $DataTable = ConvertTo-DataTable $Source
 .PARAMETER Source
 An array that needs converted to a DataTable object
 #>
[CmdLetBinding(DefaultParameterSetName="None")]
param(
 [Parameter(Position=0,Mandatory=$true)][System.Array]$Source,
 [Parameter(Position=1,ParameterSetName='Like')][String]$Match=".+",
 [Parameter(Position=2,ParameterSetName='NotLike')][String]$NotMatch=".+"
)
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}
 }
 $DataTable.Rows.Add($Row)
}
#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"
}
else{ 
 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.
 else{
 Return ,$DataTable
 }
 }
}

 

 

3 thoughts on “Function to Convert PowerShell Objects to DataTables

  1. I realize this is an older post but it is pretty much exactly what I am looking for. I am new to powershell and having difficulty making your script work. If you can help diagnose this I would very much appreciate it. I am using Powershell vs2
    My source script is $Users = Get-ADUser -Filter{Surname -eq ’employee name here’}
    The error I get is as follows.
    Unable to index into an object of type System.Data.DataRow.
    At line:32 char:7
    + $Row[ <<<< "$($Column)"] = if($Entry.$Column -ne $null){($Entry | Select-Object -ExpandProperty $Column) -join ', '}
    else{$null}
    + CategoryInfo : InvalidOperation: (:String) [], RuntimeException
    + FullyQualifiedErrorId : CannotIndex

    Like

Leave a Reply to Scott McGraw Cancel reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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.