How to manipulate data returned from Invoke-RestMethod

1
votes

I am trying take a table of data returned from invoke-restmethod and insert it into a database. I am struggling with being able to select specific data columns.

When I use the Invoke-RestMethod I get data returned in the format below.

Col1,Col2,Col3,Col4
data,aaa,bbb,ccc
data,ddd,eee,fff
data,ggg,hhh,iii

However I cannot use Select or expandproperty to only grab specific rows. IE the commands below.

$response = Invoke-RestMethod -Uri $URL -Method Get -Headers $headers 
$response | Select col1, col2 | Sort-Object -property col2 -Descending

I have also tried to out-file the data however it looks like it is joining it all as one string.

Col1,Col2,Col3,Col4data,aaa,bbb,cccdata,ddd,eee,fffdata,ggg,hhh,iii

Any help is appreciated. Thanks!

2
powershellcsv
Does the data come back in JSON format? Can you edit your question to show exactly what format the Invoke-RestMethod gives you? It should return JSON. - Jason Shave
Allow me to give you the standard advice to newcomers: If an answer solves your problem, please accept it by clicking the large check mark (✓) next to it and optionally also up-vote it (up-voting requires 15 or more reputation points). If you found other answers helpful, up-vote them. Accepting (for which you'll gain 2 reputation points) and up-voting help future readers. See this article for more information. If your question isn't fully answered yet, please provide feedback or self-answer. - mklement0

2 Answers

1
votes

In order to convert text containing CSV data to custom objects (of type [pscustomobject]) that reflect the CSV data rows and whose properties represent the column values, use the ConvertFrom-Csv cmdlet.

The resulting objects can then be used with cmdlets such as Select-Object - for extraction of properties of interest - and Sort-Object.

Here's a simplified example:

# Simulate the outcome of your Invoke-RestMethod call with a here-string:
$response = @'
Col1,Col2,Col3,Col4
data,aaa,bbb,ccc
data,ddd,eee,fff
data,ggg,hhh,iii
'@

$response | ConvertFrom-Csv | Select-Object Col1, Col2 | Sort-Object Col2 -Descending

The above yields the following 3 [pscustomobject] instances, sorted in descending order by their .Col2 property values:

Col1 Col2
---- ----
data ggg
data ddd
data aaa
0
votes

If you're invoking a REST API call, the data coming back could be in JSON format. You can convert it as follows:

$jsonResponse = Inovke-RestMethod -Uri $uri -Method Get -Headers $headers
$newObject = $jsonResponse | ConvertFrom-Json

-or

$newObject = $Invoke-RestMethod -Uri $uri -Method Get -Headers $headers | ConvertFrom-Json

This means $jsonResponse is:

{
    "FirstName" : "Tom",
    "LastName" : "Crooze"
}

Converting to newObject it would look like this:

FirstName      LastName
---------      --------
      Tom        Crooze