# API columns to SQL server
**URL:** https://forums.powershell.org/t/api-columns-to-sql-server/14462
**Category:** PowerShell Help
**Created:** [June 1, 2020, 9:56am UTC](https://forums.powershell.org/t/api-columns-to-sql-server/14462 "2020-06-01T09:56:57Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@huslayer](https://forums.powershell.org/u/huslayer)
#### Post date: [June 1, 2020, 9:56am UTC](https://forums.powershell.org/t/api-columns-to-sql-server/14462/1 "2020-06-01T09:56:57Z")
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](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
$D = $j.features
---
### Author:  [@rob-simmers](https://forums.powershell.org/u/rob-simmers)
#### Post date: [June 1, 2020, 4:36pm UTC](https://forums.powershell.org/t/api-columns-to-sql-server/14462/2 "2020-06-01T16:36:09Z")
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’' |
ConvertFrom-Json
$results = $response.features.attributes
$results
```
---
### Author:  [@huslayer](https://forums.powershell.org/u/huslayer)
#### Post date: [June 3, 2020, 7:34am UTC](https://forums.powershell.org/t/api-columns-to-sql-server/14462/3 "2020-06-03T07:34:29Z")
``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
`
---
### Author:  [@rob-simmers](https://forums.powershell.org/u/rob-simmers)
#### Post date: [June 3, 2020, 9:54am UTC](https://forums.powershell.org/t/api-columns-to-sql-server/14462/4 "2020-06-03T09:54:23Z")
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
```
---
### Author:  [@huslayer](https://forums.powershell.org/u/huslayer)
#### Post date: [June 3, 2020, 10:56am UTC](https://forums.powershell.org/t/api-columns-to-sql-server/14462/5 "2020-06-03T10:56:39Z")
Perfect! thank you!
---
### Author:  [@dotnVo](https://forums.powershell.org/u/dotnVo)
#### Post date: [May 16, 2024, 8:29pm UTC](https://forums.powershell.org/t/api-columns-to-sql-server/14462/6 "2024-05-16T20:29:26Z")