使用PowerShell将JSON转换为CSV

I have a sample JSON-formatted here which converts fine if I use something like: https://konklone.io/json/

我在这里有一个JSON格式的示例,如果我使用类似的东西,它会很好地转换:https://konklone.io/json/

I've tried the following code in PowerShell:

我在PowerShell中尝试了以下代码:

(Get-Content -Path $pathToJsonFile | ConvertFrom-Json) 
| ConvertTo-Csv -NoTypeInformation 
| Set-Content $pathToOutputFile

But the only result I get is this:

但我得到的唯一结果是:

{"totalCount":19,"resultCount":19,"hasMore":false,"results":

How do I go about converting this correctly in PowerShell?

如何在PowerShell中正确转换?

3 个解决方案

#1

By looking at just (Get-Content -Path $pathToJsonFile) | ConvertFrom-Json it looks like the rest of the JSON is going in to a results property so we can get the result I think you want by doing:

通过查看just(Get-Content -Path $ pathToJsonFile)| ConvertFrom-Json它看起来像JSON的其余部分进入结果属性,所以我们可以通过以下方式得到我认为你想要的结果:

((Get-Content -Path $pathToJsonFile) | ConvertFrom-Json).results |
    ConvertTo-Csv -NoTypeInformation |
    Set-Content $pathToOutputFile

FYI you can do ConvertTo-Csv and Set-Content in one move with Export-CSV:

仅供参考,您可以使用Export-CSV一次性执行ConvertTo-Csv和Set-Content:

((Get-Content -Path $pathToJsonFile) | ConvertFrom-Json).results |
    Export-CSV $pathToOutputFile -NoTypeInformation

#2

You have to select the results property inside your CSV using the Select-Object cmdlet together with the -expand parameter:

您必须使用Select-Object cmdlet和-expand参数在CSV中选择results属性:

Get-Content -Path $pathToJsonFile  | 
    ConvertFrom-Json | 
    Select-Object -expand results | 
    ConvertTo-Csv -NoTypeInformation |
    Set-Content $pathToOutputFile

#3

I was getting my json from a REST web api and found that the following worked:

我从REST web api获取了json并发现以下工作:

Invoke-WebRequest -method GET -uri $RemoteHost -Headers $headers 
 | ConvertFrom-Json 
 | Select-Object -ExpandProperty  <Name of object in json>
 | ConvertTo-Csv -NoTypeInformation 
 | Set-Content $pathToOutputFile

I end up with a perfectly formatted csv file

#1