Parse JSON output into a CSV file using Powershell

Question

I'm using PowerShell to extract data via an API and would like to parse the JSON into a CSV file. How would I parse each of the JSON results into a CSV structure like this:

$Date, $app, $pagename, $range1, $range1_value
$Date, $app, $pagename, $range2, $range2_value
$Date, $app, $pagename, $range3, $range3_value

The JSON looks like this:

{
"fields":  [
               {
                   "label":  "app",
                   "field":  "app",
                   "type":  "string"
               },
               {
                   "label":  "pagename",
                   "field":  "pagename",
                   "type":  "string"
               },
               {
                   "label":  "range1",
                   "field":  "count(*)",
                   "type":  "integer",
                   "aggregation":  "filter"
               },
               {
                   "label":  "range2",
                   "field":  "count(*)",
                   "type":  "integer",
                   "aggregation":  "filter"
               },
               {
                   "label":  "range3",
                   "field":  "count(*)",
                   "type":  "integer",
                   "aggregation":  "filter"
               }
           ],
"results":  [
                [
                    "application1",
                    "loginpage",
                    41425,
                    41266,
                    18869
                ],
                [
                    "application2",
                    "loginpage",
                    7424,
                    7113,
                    2905
                ]
            ],
"moreData":  false,
"schema":  "record"
}

I've tried various methods (e.g. Convertto-JSON and Convertfrom-JSON) but I don't seem to be able to connect the 'fields' and 'results' together into a hashtable. I was hoping I could create it as a $JSON object and then iterate through each result like $JSON[0..1].

Mathias R. Jessen · Accepted Answer · 2020-05-27 16:14:37Z

1

Let's start by parsing your input data!

Use a for loop to iterate over the individual array items in the results values, then use the index to resolve the type and label name from the fields list:

# Convert from json
$data = $jsonString |ConvertFrom-Json

# Set up a type table for easy conversion
$typeTable = @{'integer' = [int]}

# Iterate over each row in the results
$results = foreach($values in $data.results){

  # Create dictionary to hold property values for the row
  $Properties = [ordered]@{}
  for($index = 0; $index -lt $data.fields.Count; $index++){
    # Resolve field metadata by index
    $field = $data.fields[$index]

    # Take type mappings into account and write to $Properties dictionary
    if($typeTable.ContainsKey($field.type)){
      $Properties[$field.label] = $values[$index] -as $typeTable[$field.type]
    }
    else{
      $Properties[$field.label] = $values[$index]
    }
  }

  # Output structured object
  [PSCustomObject]$Properties
}

Now that we have nice objects we can work with, we can use Select-Object and Export-Csv to create the desired output format:

$results |Select-Object @{Name='Date';Expression={Get-Date -Format yyyyMMdd}},app,pagename,@{Name='2000';Expression={'2000'}},range3 |Export-Csv -Path .\path\to\output.csv -NoTypeInformation

answered May 27, 2020 at 16:14

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cyclemonkey Over a year ago

That's great, thank you so much @Mathias R. Jessen. I just noticed that I didn't format my output format accurately. How would I list the "range name" and "range value" for each one? I've updated the example in my OP.

Collectives™ on Stack Overflow

Parse JSON output into a CSV file using Powershell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related