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
 }
 }
}