I have used Chad Millers (@cmille19) Out-DataTable function on numerous occasions. It is used to convert a regular PowerShell object to a DataTable object. It had it quirks though, so I decided to rewrite it to try to fix some of them.
Most notably I have changed it so, in addition to arrays, also collections will be added as XML. I (think) I have also fixed the null/dbnull issue. Let me know if that is not the case though!
Here is the code, check it out and let me know what you think.
This file contains hidden or 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)) } | |
| } |
First I’d like to thank you for that code. But I have difficutlies converting data containing zeros. The zeros are just not in the table, the fields are empty 😦
LikeLike
It’s interpreting 0 as empty, and thus the type of the value will be DBNull in the resulting datatable. I’ll see if I can come up with a quick fix for this 🙂
LikeLike
Published a fix for this bug. Thanks for reporting it! 🙂
LikeLike