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