Convert JSON to CSV using PowerShell

Question

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

I've tried the following code in 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?

Solution 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:

((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:

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

Solution 2

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

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

Solution 3

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

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

Comments

[Dany Gauthier]

For some reason, on my end, the array contained in source json file is converted to the "SyncRoot" property of the results. So I just had to replace .results with .SyncRoot.

[Roman]

@DanyGauthier that is because .results is the name of JSON object. I guess you have a name called "SyncRoot". I have a name called "Weights". That messed with me for a while, but once I figured it out it worked :)

[Piemol]

I specifically had to leave out the ".results" (and added -Raw as my json was 'formatted' for readability: ((Get-Content -Path $pathToJsonFile -Raw) | ConvertFrom-Json) | Export-CSV $pathToOutputFile -NoTypeInformation PowerShell version 4 if it matters.

[MDMoore313]

Upvoted, I also had to strip out the .results from the one-liner.

[Santiago Squarzon]

You are over complicating it. A simple function like the one shown on this answer can solve this problem and is more efficient.

[dougp]

Thanks. If you can make that work for the example in my answer, I'd love to see it. I can't make it work. Plus, that function is 8 lines. it would replace maybe 5 lines of my code? Not sure what I'm missing here since I'm new to PowerShell.

[Santiago Squarzon]

Using the function from that answer an assuming you have the Json converted as an object it would be as simple as $json | UnifyProperties | Export-Csv .... And yes, that code is more efficient because, first, it's using just 1 loop where yours is using 2 and second, you're adding properties to the objects with Add-Member which is highly inefficient.

[dougp]

Thanks. That is easy. The usage instructions weren't clear to me. I was trying to use it to combine pairs of objects from my array rather than just processing the entire array ($json). I'll update my answer.

[ChumKui]

and me - no need for .results in my script