API columns to SQL server

Welcome Forums General PowerShell Q&A API columns to SQL server

Viewing 4 reply threads
  • Author
    Posts
    • #232522
      Participant
      Topics: 1
      Replies: 2
      Points: 17
      Rank: Member

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

    • #232624
      Participant
      Topics: 12
      Replies: 1642
      Points: 2,660
      Helping Hand
      Rank: Community Hero

      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
      
    • #233032
      Participant
      Topics: 1
      Replies: 2
      Points: 17
      Rank: Member

      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

    • #233113
      Participant
      Topics: 12
      Replies: 1642
      Points: 2,660
      Helping Hand
      Rank: Community Hero

      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
      
    • #233131
      Participant
      Topics: 1
      Replies: 2
      Points: 17
      Rank: Member

      Perfect! thank you!

Viewing 4 reply threads
  • You must be logged in to reply to this topic.