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?
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
((Get-Content -Path $pathToJsonFile -Raw) | ConvertFrom-Json) | Export-CSV $pathToOutputFile -NoTypeInformation PowerShell version 4 if it matters.
.results from the one-liner.
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
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
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
$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.$json). I'll update my answer.