The main difference between DataTable and a list of PowerShell objects is the fact that for a DataTable all item properties, including the item types, are the same for each ‘column’ were a list of PowerShell objects has all properties, including the type, defined with each specific item. This means that a list of PowerShell objects could have e.g. a DataTime object and a Int16 in the same column which is not possible in a DataTable (unless you convert them both to a string).
In this known fact lies also the trap for most of the currently available ConvertTo-DataTable cmdlets on the internet as they presume the column type of DataTable on only the first object (row). Therefore a simple custom object as below either causes an error or values to be parsed to strings:
|
1 2 3 4 |
@( New-Object PSCustomObject -Property @{Name = "a"; Value = 123} New-Object PSCustomObject -Property @{Name = "b"; Value = 123456789012} ) | ConvertTo-DataTable |
Similar issues occur when the first object contains items that are $Null, where the whole column is often presumed as a String type but should be e.g. a DateTime type:
|
1 2 3 4 |
@( New-Object PSCustomObject -Property @{Name = "a"; Date = $Null} New-Object PSCustomObject -Property @{Name = "b"; Date = Get-Date} ) | ConvertTo-DataTable |
The solution would be to find the best fitted (smallest) common type of all the object in a column which will take time and isn’t always that simple to define. Take e.g. two types like UInt16 and Int16, you can’t take either of the types because one type doesn’t completely contain the other type. The smallest value of a UInt16 is 0 whereas the smallest value of a Int16 is -32768 and the largest value of a UInt16 is 65535 whereas the largest value of a Int16 is 32767. Meaning, in this case the best fitted (smallest) common type is neither of the original types but either: UInt32 or Int32*.
The ConvertTo-DataTable cmdlet below will automatically resolve the best fitted (smallest) common type and used that for the column type.
* The cmdlet purposed here, will take it preference (signed vs. unsigned) based on with type is found first
Cmdlet
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
Function ConvertTo-DataTable { # https://powersnippets.com/convertto-pson/ [CmdletBinding()]Param( # Version 01.00.01, by iRon [Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$Object, [HashTable]$ColumnType = @{} ) $TypeCast = @{ Guid = 'Guid', 'String' DateTime = 'DateTime', 'String' Byte = 'Byte', 'Char', 'Int16', 'Int32', 'Int64', 'UInt16', 'UInt32', 'UInt64', 'Decimal', 'Single', 'Double', 'String', 'Boolean' SByte = 'SByte', 'Int16', 'Int32', 'Int64', 'Decimal', 'Single', 'Double', 'String', 'Boolean' Char = 'Char', 'Int32', 'Int64', 'UInt16', 'UInt32', 'UInt64', 'String' TimeSpan = 'TimeSpan', 'String' Int16 = 'Int16', 'Int32', 'Int64', 'Decimal', 'Single', 'Double', 'String', 'Boolean' Int32 = 'Int32', 'Int64', 'Decimal', 'Single', 'Double', 'String', 'TimeSpan', 'Boolean' Int64 = 'Int64', 'Decimal', 'Single', 'Double', 'String', 'TimeSpan', 'Boolean' UInt16 = 'Int32', 'Int64', 'UInt16', 'UInt32', 'UInt64', 'Decimal', 'Single', 'Double', 'Char', 'String', 'Boolean' UInt32 = 'Int64', 'UInt32', 'UInt64', 'Decimal', 'Single', 'Double', 'String', 'Boolean' UInt64 = 'UInt64', 'Decimal', 'Single', 'Double', 'String', 'Boolean' Decimal = 'Decimal', 'Single', 'Double', 'String', 'Boolean' Single = 'Single', 'Double', 'String', 'Boolean' Double = 'Double', 'Single', 'String', 'Boolean' String = 'String' Boolean = 'Boolean', 'Byte', 'SByte', 'Int16', 'Int32', 'Int64', 'UInt16', 'UInt32', 'UInt64', 'Decimal', 'Single', 'Double', 'String' } $PipeLine = $Input | ForEach {$_}; If ($PipeLine) {$Object = $PipeLine} $DataTable = New-Object Data.DataTable $First = $Object | Select-Object -First 1 $Properties = $First | Get-Member -Type Property; If (!$Properties) {$Properties = $First | Get-Member -Type NoteProperty} $Names = ForEach ($Property in $Properties) {$First.PSObject.Properties | Where {$_.Name -eq $Property.Name -and $_.IsGettable} | Select -Expand "Name"} ForEach($Name in $Names) { If ($ColumnType.ContainsKey($Name)) {$Type = $ColumnType.$Name} Else { $Types = $Object | Where-Object {$Null -ne $_.$Name} | ForEach {$_.$Name.GetType().Name} | Where-Object {$TypeCast.ContainsKey($_)} | Select-Object -Unique $Type = If ($Types.Count -gt 1) { $Cast = $TypeCast[$Types[0]]; ForEach ($Type in ($Types | Select-Object -Skip 1)) {$Cast = $Cast | Where-Object {$TypeCast[$Type] -Contains $_}} If ($Cast) {@($Cast)[0]} Else {'String'} } ElseIf ($Types) {$Types} Else {'String'} } $DataColumn = New-Object Data.DataColumn $DataColumn.ColumnName = $Name $DataColumn.DataType = [System.Type]::GetType("System.$Type") $DataTable.Columns.Add($DataColumn) } ForEach($RowObject in $Object) { $DataRow = $DataTable.NewRow() ForEach($Name in $Names) {$DataRow.Item($Name) = If ($Null -ne $RowObject.$Name) {$RowObject.$Name} Else {[DBNull]::Value}} $DataTable.Rows.Add($DataRow) } Write-Output (,($DataTable)) } Set-Alias ctdt ConvertTo-DataTable -Description "Converts a PowerShell object list to a DataTable" |
Syntax
<Object> | ConvertTo-DataTable [-ColumnType] <HashTable>
Parameters
[-Object] <Object>
The object(s) to be converted to a DataTable.
[-ColumnType] <HashTable>
Type definition of each column by name: @{<ColumnName> = <Type>; ...}
Examples
Converting a custom object to a data table
|
1 2 3 4 |
$MyObject = @( New-Object PSCustomObject -Property @{Name = "a"; Value = 123; Date = $Null} New-Object PSCustomObject -Property @{Name = "b"; Value = 123456789012; Date = $Now} ) |
$DataTable = $MyObject | ConvertTo-DataTable
Defining the column types:
$DataTable = $MyObject | ConvertTo-DataTable -ColumnType @{Name = [String]; Value = [Int64]; Date = [DateTime]}
Convert (PowerShell) process object
$DataTable = Get-Process 'PowerShell' | ConvertTo-DataTable
Convert User Language List
$DataTable = Get-WinUserLanguageList | ConvertTo-DataTable