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)) } | |
} |
This comment has been minimized.
This comment has been minimized.
Fails with:
Because the column type is presumed on the first row (Int32). |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
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.