Last active
September 5, 2023 11:31
-
-
Save gravejester/3052e0e54e35bebc3257 to your computer and use it in GitHub Desktop.
ConvertTo-DataTable
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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)) } | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
$datatype will be left as the type of the last column in the first row, so columns that are not in a compatible type in subsequent rows will fail.