Exporting Powershell REST API results as CSV

Question

I'm currently use the following Powershell script as an API call using REST to retrieve table data from a vendor, which appears to be in JSON format:

$uri = "https://www.SomeWebsite.com/api/orders"
$headers = @{
    'Content-Type' = 'application/json'
    'Authorization' = 'Bearer <MyTokenID>'
    'Accept'= 'application/json'
}
  
Invoke-RestMethod -Uri $uri -Method GET -Headers $headers -Body $body

The script works, and output as such in Powershell:

data                                                                                                                                                                    
----                                                                                                                                                                    
{@{type=appointments; id=1234; attributes=; links=; relationships=}, @{type=appointments;     id=1235; attributes=; links=; relationships=}, @{type=appointments; i...

I need the ability to export this as a CSV file. How can I incorporate anexport-CSV CMDLET (something like below) to have it work with the above syntax? (Preferably ALL Columns/Headers)

Select ID, Status | Export-Csv -Path "filename.csv" -NoTypeInformation

Invoke-RestMethod -Uri $uri -Method GET -Headers $headers -Body $body | select -expand data | select id,status | export-csv filename.csv -notype — AdminOfThings
– AdminOfThings, Commented Nov 11, 2021 at 19:12
Thanks ! Seems to work! If you include this as an answer, I'll mark it as solved! — Depth of Field
– Depth of Field, Commented Nov 12, 2021 at 3:46

AdminOfThings · Accepted Answer · 2021-11-12 13:40:50Z

2

Your Invoke-RestMethod outputs an object with a data property. The data property contains the objects you want to reference. So you must expand that property first.

Invoke-RestMethod -Uri $uri -Method GET -Headers $headers -Body $body |
    Select-Object -ExpandProperty data |
        Select-Object id,status |
            Export-Csv filename.csv -NoTypeInformation

answered Nov 12, 2021 at 13:40

AdminOfThings

25.2k4 gold badges22 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Exporting Powershell REST API results as CSV

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related