ConvertTo-DataTable

I have used Chad Millers (@cmille19) Out-DataTable function on numerous occasions. It is used to convert a regular PowerShell object to a DataTable object. It had it quirks though, so I decided to rewrite it to try to fix some of them.

Most notably I have changed it so, in addition to arrays, also collections will be added as XML. I (think) I have also fixed the null/dbnull issue. Let me know if that is not the case though!

Here is the code, check it out and let me know what you think.

function ConvertTo-DataTable {
<#
.SYNOPSIS
Convert regular PowerShell objects to a DataTable object.
.DESCRIPTION
Convert regular PowerShell objects to a DataTable object.
.EXAMPLE
$myDataTable = $myObject | ConvertTo-DataTable
.NOTES
Name: ConvertTo-DataTable
Author: Øyvind Kallstad @okallstad
Version: 1.1
#>
[CmdletBinding()]
param (
# The object to convert to a DataTable
[Parameter(ValueFromPipeline = $true)]
[PSObject[]] $InputObject,
# Override the default type.
[Parameter()]
[string] $DefaultType = 'System.String'
)
begin {
# create an empty datatable
try {
$dataTable = New-Object TypeName 'System.Data.DataTable'
Write-Verbose Message 'Empty DataTable created'
}
catch {
Write-Warning Message $_.Exception.Message
break
}
# define a boolean to keep track of the first datarow
$first = $true
# define array of supported .NET types
$types = @(
'System.String',
'System.Boolean',
'System.Byte[]',
'System.Byte',
'System.Char',
'System.DateTime',
'System.Decimal',
'System.Double',
'System.Guid',
'System.Int16',
'System.Int32',
'System.Int64',
'System.Single',
'System.UInt16',
'System.UInt32',
'System.UInt64'
)
}
process {
# iterate through each input object
foreach ($object in $InputObject) {
try {
# create a new datarow
$dataRow = $dataTable.NewRow()
Write-Verbose Message 'New DataRow created'
# iterate through each object property
foreach ($property in $object.PSObject.get_properties()) {
# check if we are dealing with the first row or not
if ($first) {
# handle data types
if ($types -contains $property.TypeNameOfValue) {
$dataType = $property.TypeNameOfValue
Write-Verbose Message "$($property.Name): Supported datatype <$($dataType)>"
}
else {
$dataType = $DefaultType
Write-Verbose Message "$($property.Name): Unsupported datatype ($($property.TypeNameOfValue)), using default <$($DefaultType)>"
}
# create a new datacolumn
$dataColumn = New-Object 'System.Data.DataColumn' $property.Name, $dataType
Write-Verbose Message 'Created new DataColumn'
# add column to DataTable
$dataTable.Columns.Add($dataColumn)
Write-Verbose Message 'DataColumn added to DataTable'
}
# add values to column
if ($property.Value -ne $null) {
# if array or collection, add as XML
if (($property.Value.GetType().IsArray) -or ($property.TypeNameOfValue -like '*collection*')) {
$dataRow.Item($property.Name) = $property.Value | ConvertTo-Xml -As 'String' NoTypeInformation Depth 1
Write-Verbose Message 'Value added to row as XML'
}
else{
$dataRow.Item($property.Name) = $property.Value -as $dataType
Write-Verbose Message "Value ($($property.Value)) added to row as $($dataType)"
}
}
}
# add DataRow to DataTable
$dataTable.Rows.Add($dataRow)
Write-Verbose Message 'DataRow added to DataTable'
$first = $false
}
catch {
Write-Warning Message $_.Exception.Message
}
}
}
end { Write-Output (,($dataTable)) }
}

4 comments

  1. First I’d like to thank you for that code. But I have difficutlies converting data containing zeros. The zeros are just not in the table, the fields are empty 😦

    Like

    1. It’s interpreting 0 as empty, and thus the type of the value will be DBNull in the resulting datatable. I’ll see if I can come up with a quick fix for this 🙂

      Like

Leave a Reply to Øyvind Kallstad Cancel reply

Fill in your details below or click an icon to log in:

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