API columns to SQL server

PowerShell Help
1

Hello, I need help to parse the below API JSON to columns, I’ve tried a lot but can’t figure out the nested JSON. sorry I’m a BI developer that assuming another 100 rules.

any help or direction is appreciated.

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$j = Invoke-WebRequest ‘Home’ | ConvertFrom-Json
$D = $j.features

2

Close…

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$response = Invoke-WebRequest ‘https://services1.arcgis.com/CY1LXxl9zlJeBuRZ/arcgis/rest/services/Florida_COVID19_Case_Line_Data_NEW/FeatureServer/0/query?where=1%3D1&outFields=*&outSR=4326&f=json&#8217' | 
            ConvertFrom-Json

$results = $response.features.attributes

$results
3

Thank you!!!

now another issue, the dates are coming in EPOCH dates, need to convert it to datetime

 

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 $response = Invoke-WebRequest ‘https://services1.arcgis.com/CY1LXxl9zlJeBuRZ/arcgis/rest/services/Florida_COVID19_Case_Line_Data_NEW/FeatureServer/0/query?where=1%3D1&outFields=*&outSR=4326&f=json’' | ConvertFrom-Json $results = $response.features.attributes $results | Select-Object County,ChartDate, @{Name='ChartDateConverted';Expression= [datetimeoffset]::FromUnixTimeSeconds($_.ChartDate)}} | Format-Table -AutoSize
4

Close, while there were some rogue single qoutes, basically the function FromUnixTimeMilliseconds returns an object containing a lot of date information:

PS C:\Users\rasim> [datetimeoffset]::FromUnixTimeMilliseconds('1587531600000')


DateTime      : 4/22/2020 5:00:00 AM
UtcDateTime   : 4/22/2020 5:00:00 AM
LocalDateTime : 4/22/2020 1:00:00 AM
Date          : 4/22/2020 12:00:00 AM
Day           : 22
DayOfWeek     : Wednesday
DayOfYear     : 113
Hour          : 5
Millisecond   : 0
Minute        : 0
Month         : 4
Offset        : 00:00:00
Second        : 0
Ticks         : 637231284000000000
UtcTicks      : 637231284000000000
TimeOfDay     : 05:00:00
Year          : 2020

Not sure you if you would need local or UTC, but here is an example:

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 

$response = Invoke-WebRequest ‘https://services1.arcgis.com/CY1LXxl9zlJeBuRZ/arcgis/rest/services/Florida_COVID19_Case_Line_Data_NEW/FeatureServer/0/query?where=1%3D1&outFields=*&outSR=4326&f=json' | ConvertFrom-Json

$results = $response.features.attributes | 
           Select-Object County,
                         ChartDate,
                         @{Name= 'ChartDateConvertedUTC';Expression={[datetimeoffset]::FromUnixTimeMilliseconds($_.ChartDate) | Select -ExpandProperty UtcDateTime}},
                         @{Name= 'ChartDateConvertedLocal';Expression={[datetimeoffset]::FromUnixTimeMilliseconds($_.ChartDate) | Select -ExpandProperty LocalDateTime}}

$results
5

Perfect! thank you!