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.
| 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