CODEWITHSUNDEEP
powershell
PnP
PnP

Reputation: 3185

Convert from PSObject to Datatable

So essentially I'm parsing content from http://www.webcal.fi/en-GB/other_file_formats.php via Invoke-WebRequest and converting this from JSON into a PSObject via the ConvertFrom-Json cmdlet.

Now I need to get that data into SQL, and the most effective way I can think of (with the ability to re-use pre-existing functions I have already) is to get this PSObject converted into a Datatable.

I can't seem to think of any easy way to achieve this while keeping it generic, I don't want to hard-code Column names into my Datatable, but instead pull them from the object itself. Any ideas?

EDIT

MemberType      : Property
Value           : {@{date=2017-01-01; name=New Year's Day; url=https://en.wikipedia.org/wiki/New_Year%27s_Day; description=New Year's Day is observed on January 1, the first day of the year on the modern Gregorian calendar as well 
                  as the Julian calendar used in ancient Rome. With most countries using the Gregorian calendar as their main calendar, New Year's Day is the closest thing to being the world's only truly global public holiday, often 
                  celebrated with fireworks at the stroke of midnight as the new year starts. January 1 on the Julian calendar currently corresponds to January 14 on the Gregorian calendar, and it is on that date that followers of 
                  some of the Eastern Orthodox churches celebrate the New Year.}, @{date=2017-01-02; name=New Year's Day Holiday; url=https://en.wikipedia.org/wiki/New_Year%27s_Day; description=New Year's Day is observed on January 
                  1, the first day of the year on the modern Gregorian calendar as well as the Julian calendar used in ancient Rome. With most countries using the Gregorian calendar as their main calendar, New Year's Day is the 
                  closest thing to being the world's only truly global public holiday, often celebrated with fireworks at the stroke of midnight as the new year starts. January 1 on the Julian calendar currently corresponds to 
                  January 14 on the Gregorian calendar, and it is on that date that followers of some of the Eastern Orthodox churches celebrate the New Year.}, @{date=2017-04-14; name=Good Friday; 
                  url=https://en.wikipedia.org/wiki/Good_Friday; description=Good Friday (from the senses pious, holy of the word "good"), is a religious holiday observed primarily by Christians commemorating the crucifixion of 
                  Jesus Christ and his death at Calvary. The holiday is observed during Holy Week as part of the Paschal Triduum on the Friday preceding Easter Sunday, and may coincide with the Jewish observance of Passover. It is 
                  also known as Black Friday, Holy Friday, Great Friday, or Easter Friday, though the latter normally refers to the Friday in Easter week.}, @{date=2017-04-16; name=Easter; url=https://en.wikipedia.org/wiki/Easter; 
                  description=Easter (Old English: Ēostre; Greek: Πάσχα, Paskha; Aramaic: פֶּסחא‎ Pasḥa; from Hebrew: פֶּסַח‎ Pesaḥ) is the central feast in the Christian liturgical year. According to the Canonical gospels, Jesus 
                  rose from the dead on the third day after his crucifixion. His resurrection is celebrated on Easter Day or Easter Sunday (also Resurrection Day or Resurrection Sunday). The chronology of his death and resurrection 
                  is variously interpreted to have occurred between AD 26 and 36.}...}
IsSettable      : False
IsGettable      : True
TypeNameOfValue : System.Object
Name            : SyncRoot
IsInstance      : True

Upvotes: 1

Views: 10319

Answers (2)

iRon
iRon

Reputation: 23663

The main difference between DataTable and a list of PowerShell objects is that for a DataTable all item properties, including the item type, are the same for each ‘column’ whereas a list of PowerShell objects has all properties defined with each specific item. This means that a list of PowerShell objects could have e.g. a DataTime object and an Int16 in the same column which is not possible in a DataTable.

In this known fact lies also the trap for most of the currently available ConvertTo-DataTable like cmdlets on the internet as they presume the column type of the DataTable on only the first object (row). Therefore a simple custom object as below either causes an error or values to be incorrectly parsed to strings:

@(
    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:

@(
    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 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

ConvertTo-DataTable

See: https://powersnippets.com/convertto-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

$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

Upvotes: 1

briantist
briantist

Reputation: 47792

I don't know what a [datatable] is as I've never used it before. Seems like part of ADO.NET.

But you can programmatically get the properties of your [PSObject] use the hidden .PSObject property and then parsing its .Properties property.

Also, slight nitpick, you can use Invoke-RestMethod against your URI and it will automatically convery the result from JSON, so you won't need Invoke-WebRequest.

Upvotes: 3

Related Questions