convert to csv with incomplete json data

Welcome Forums General PowerShell Q&A convert to csv with incomplete json data

Viewing 2 reply threads
  • Author
    Posts
    • #181914
      Participant
      Topics: 1
      Replies: 1
      Points: 14
      Rank: Member

      I am having trouble converting json data to csv.

      I have the following json data tha is pulled from a web api and loaded into $EnergyData:

      {
          "energyDetails": {
              "timeUnit": "WEEK",
              "unit": "Wh",
              "meters": [
                  {
                      "type": "Production",
                      "values": [
                          {
                              "date": "2015-11-02 00:00:00"
                          },
                          {
                              "date": "2015-11-09 00:00:00"
                          },
                          {
                              "date": "2015-11-16 00:00:00",
                              "value": 2953
                          }
                      ]
                  }
              ]
          }
      }
      

      If I run this code I only get the date column in the csv

      $EnergyData = (Invoke-WebRequest "$EnergyUrl").Content | ConvertFrom-Json
      $Data = $EnergyData.energyDetails.meters.values | ConvertTo-Csv -Delimiter "," -NoTypeInformation
      If($Data -ne $null) { [io.file]::WriteAllLines("$PSScriptRoot\$FileName",$Data) }
      

      How do I get value 2953 into the csv, the other date entries need to be in there too but with a null or "" value.

    • #182190
      Senior Moderator
      Topics: 8
      Replies: 1136
      Points: 3,903
      Helping Hand
      Rank: Community Hero

      you could use Select-Object cmdlet here. It will read the Value property for the existing and create it with empty value for the others.

      $EnergyData.energyDetails.meters.values | Select-Object -Property Date,Value | ConvertTo-Csv -Delimiter "," -NoTypeInformation
      
      
      
      		
      	
    • #182544
      Participant
      Topics: 1
      Replies: 1
      Points: 14
      Rank: Member

      Thank you, this works perfectly, I tried to loop through the values and set any nulls to "0" but I failed. Also I have no idea why Select-Object -Property Date,Value forces the value column to be output to csv but my learning continues. Thans so much for the help. My project moves forward.

       

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