CODEWITHSUNDEEP
jsonpowershellapicsv
BennKingy
BennKingy

Reputation: 1583

How to transform powershell json response to csv

This is my PowerShell which is calling an API and returning a JSON response.

$output = Get-SurveyParticipents `
 -url "https://orxsurveys.limequery.com/admin/remotecontrol" `
 -session $sessionKey `
 -id "519965" `
 -start "0" `
 -limit "2" `
 -unused $False `
 -attributes ["completed", "usesleft"]

($output | Export-Csv -NoTypeInformation ./testtt.csv)

Write-host $output produces:

@{tid=6; token=35ddmyQTlNpzLat; participant_info=} @{tid=7; token=nQ_S838LjYT4mR6; participant_info=}

Export-CSV produces: enter image description here

This is what I need to produce from export-csv: enter image description here

Can anybody please point me in the right direction to transforming the 'participant_info' into valid json for the CSV export? - As you can tell I have little expierence with PowerShell other then using it for SharePoint. Thank you!

Upvotes: 0

Views: 52

Answers (1)

AdminOfThings
AdminOfThings

Reputation: 25001

Your goal is to output an object with a custom set of properties (because it differs from the original object). This can be done with Select-Object and calculated properties.

$output | 
    Select-Object tid,token,
        @{n='Firstname';e={$_.participant_info.Firstname}},
        @{n='Lastname';e={$_.participant_info.Lastname}},
        @{n='Email';e={$_.participant_info.Email}} |
            Export-CSV testtt.csv -NoType

Upvotes: 2

Related Questions