convert to csv with incomplete json data

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

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
1 week ago.

  • 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: 1085
    Points: 3,651
    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.

     

You must be logged in to reply to this topic.