CODEWITHSUNDEEP
jsonpowershellcsv
Anders Ekelund
Anders Ekelund

Reputation: 183

Convert JSON to CSV using PowerShell

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?

Upvotes: 15

Views: 94647

Answers (4)

dougp
dougp

Reputation: 3089

Trying to use Mark Wragg's answer failed for me. While Piemol's comment from Jan 30 '19 solved a basic problem with Mark Wragg'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

Upvotes: 1

Pat Fahy
Pat Fahy

Reputation: 109

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

Upvotes: 6

Mark Wragg
Mark Wragg

Reputation: 23355

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

Upvotes: 34

Martin Brandl
Martin Brandl

Reputation: 58931

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

Upvotes: 9

Related Questions