CODEWITHSUNDEEP
powershellcsv
Cyclemonkey
Cyclemonkey

Reputation: 97

Convert JSON to Csv columns in Powershell

I would like to convert my JSON into the below CSV output. I'm struggling to parse the "results" data into two columns.

CSV output:

TIMESTAMP, VALUE,
1581292800000, 270,
1581292860000, 347

My JSON output is as follows: 

$JSON =
        {
        "value":  [
                      {
                          "fields":  [
                                         "@{label=DateTime; field=eventTimestamp; type=date; aggregation=series}",
                                         "@{label=Count; field=metrics.End User Response Time (ms); type=integer; aggregation=count}"
                                     ],
                          "results":  [
                                          "1581292800000 270",
                                          "1581292860000 347",
                                          "1581292920000 348",
                                          "1581292980000 401",
                                          "1581293040000 435",
                                          "1581293100000 413",
                                          "1581293160000 466",
                                          "1581293220000 445",
                                          "1581293280000 450",
                                          "1581293340000 488",
                                          "1581293400000 470",
                                          "1581293460000 450",
                                          "1581293520000 440",
                                          "1581293580000 435",
                                          "1581293640000 403",
                                          "1581293700000 472",
                                          "1581293760000 392",
                                          "1581293820000 398",
                                          "1581293880000 357",
                                          "1581293940000 356",
                                          "1581294000000 361",
                                          "1581294060000 339",
                                          "1581294120000 373",
                                          "1581294180000 340",
                                          "1581294240000 329",
                                          "1581294300000 327",
                                          "1581294360000 307",
                                          "1581294420000 282",
                                          "1581294480000 315"
                                      ],
                          "moreData":  false,
                          "schema":  "browserrecord"
                      }
                  ],
        "Count":  1
    }

The following code extracts the results, however the TIMESTAMP and VALUE are both in the same column.

$Output1 = ConvertTo-Json $JSON
$Output2 = ConvertFrom-Json $Outage2 | Select -ExpandProperty 'value' |  Select -ExpandProperty 'results'

Any help would be much appreciated.

Cheers!

Upvotes: 1

Views: 1444

Answers (3)

js2010
js2010

Reputation: 27423

Split the results. You can pipe that to export-csv.

'1581292800000 270',
'1581292860000 347',
'1581292920000 348' | foreach { 
  $timestamp,$value = -split $_ 
  [pscustomobject]@{Timestamp = $timestamp
                        Value = $value} 
}


Timestamp     Value
---------     -----
1581292800000 270
1581292860000 347
1581292920000 348

Upvotes: 1

Paweł Dyl
Paweł Dyl

Reputation: 9143

Just split the values by space:

(ConvertFrom-Json $JSON).value.results | `
  % { $pair = $_ -split ' '; [PSCustomObject]@{Timestamp=$pair[0]; Value=$pair[1]}} `
  | ConvertTo-Csv -NoTypeInformation

Upvotes: 1

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174485

So far you've managed to create a string array $output2 with all the space-separated string values from the json document - so far, so good!

We can now split the two "columns" into separate strings with the -split operator:

$results = $output2 |ForEach-Object {
    $ts,$value = -split $_

    [pscustomobject]@{
        TIMESTAMP = $ts
        VALUE = $value
    }
}

Now we've got an array of objects with the two values separated into appropriately named properties TIMESTAMP and VALUE, we can use Export-Csv:

$results |Export-Csv .\results.csv -NoTypeInformation

Upvotes: 3

Related Questions