How to transform powershell json response to csv

Question

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:

This is what I need to produce from export-csv:

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!

That's not json. $output looks like an object converted to a string by write-host. — js2010
– js2010, Commented Dec 22, 2020 at 14:03

AdminOfThings · Accepted Answer · 2020-12-22 13:49:46Z

2

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

edited Dec 22, 2020 at 13:49

answered Dec 22, 2020 at 13:43

AdminOfThings

25.2k4 gold badges23 silver badges31 bronze badges

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

1 Comment

BennKingy Over a year ago

Thank you so much. This works and I will research into Select-Object now <3

Collectives™ on Stack Overflow

How to transform powershell json response to csv

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