Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
ConvertTo-DataTable
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)) }
}
@Jasonthurston

This comment has been minimized.

Copy link

@Jasonthurston Jasonthurston commented Feb 15, 2017

Shouldn't this code:
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)>"
}

Be outside of and before the "if ($first) {" conditional block?
Otherwise`how can "$dataType" get set for the lower section where it creates the actual rows.

image

@iRon7

This comment has been minimized.

Copy link

@iRon7 iRon7 commented Jan 2, 2018

@(
	New-Object PSCustomObject -Property @{Name = "a"; Value = 123}
	New-Object PSCustomObject -Property @{Name = "b"; Value = 123456789012}
) | ConvertTo-DataTable

Fails with:

WARNING: Exception setting "Item": "Exception calling "set_Item" with "2" argument(s): "Value was either too large or too
small for an Int32.Couldn't store <123456789012> in Value Column. Expected type is Int32.""

Because the column type is presumed on the first row (Int32).
Also if the first row has an empty value ($Null), the whole column is presumed to be a string type.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.