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?

score 29 · Accepted Answer · answered Apr 24 '17 at 18:23

29

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

answered Apr 24 '17 at 18:23

Mark Wragg

22,105
7
39
68

1

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. – Dany Gauthier Nov 21 '17 at 16:29
1

@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 :) – Roman Mar 08 '18 at 17:53
8

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. – Piemol Jan 30 '19 at 07:23
Upvoted, I also had to strip out the `.results` from the one-liner. – MDMoore313 Oct 01 '20 at 15:41
and me - no need for .results in my script – ChumKui Jan 13 '22 at 15:04

score 8 · Answer 2 · answered Apr 24 '17 at 18:23

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

score 6 · Answer 3 · answered Apr 04 '18 at 15:02

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

dougp · Answer 4 · 2022-01-05T00:26:28.393

Trying to use Mark Wrang's answer failed for me. While Piemol's comment from Jan 30 '19 solved a basic problem with Mark Wrang's answer, it also didn't work for me.

JSON strings do not always represent rectangular data sets. They may contain ragged data. For example, the Power BI activities log outputs JSON that contains different members depending on variables like what activities occurred in the requested data or what features were available at the time.

Using Piemol's comment, I processed this JSON:

[
    {
        "a":  "Value 1",
        "b":  20,
        "g":  "Arizona"
    },
    {
        "a":  "Value 2",
        "b":  40,
        "c":  "2022-01-01T11:00:00Z"
    },
    {
        "a":  "Value 3",
        "d":  "omicron",
        "c":  "2022-01-01T12:00:00Z"
    },
    {
        "a":  "Value 4",
        "b":  60,
        "d":  "delta",
        "e":  14,
        "c":  "2022-01-01T13:00:00Z"
    }
]

The script produced this CSV:

"a","b","g"
"Value 1","20","Arizona"
"Value 2","40",
"Value 3",,
"Value 4","60",

Notice that columns c, d, and e are missing. It appears that Export-CSV uses the first object passed to determine the schema for the CSV to output.

To handle this, use the UnifyProperties function:

function UnifyProperties {
  $Names = [System.Collections.Generic.HashSet[string]]::new([StringComparer]::OrdinalIgnoreCase)
  $InputCollected = @($Input)
  $InputCollected.ForEach({ 
    foreach ($Name in $_.psobject.Properties.Name) { $Null = $Names.Add($Name) }
  })
  $inputCollected | Select-Object @($Names)
}

$pathToInputFolder = (New-Object -ComObject Shell.Application).NameSpace('shell:Downloads').Self.Path + "\" + "PowerBIActivities\combined\"
$pathToInputFile = $pathToInputFolder + "Activities.json"
$pathToOutputFile = $pathToInputFolder + "Activities.csv"

$content = Get-Content -Path $pathToInputFile -Raw
$psObj = ConvertFrom-Json -InputObject $content

$psObj | UnifyProperties | Export-CSV $pathToOutputFile -NoTypeInformation

You are over complicating it. A simple function like the one shown on [this answer](https://stackoverflow.com/a/44429084/15339544) can solve this problem and is more efficient. — Santiago Squarzon, Jan 04 '22 at 23:49
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. — dougp, Jan 05 '22 at 00:08
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. — Santiago Squarzon, Jan 05 '22 at 00:14
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. — dougp, Jan 05 '22 at 00:23

Convert JSON to CSV using PowerShell

4 Answers4

Linked