CODEWITHSUNDEEP
jsonpowershellcsvparsingscripting
robinhood
robinhood

Reputation: 3

How can I craft a csv from multi level json in powershell?

I am trying to parse a JSON response that looks like

{
"Objects":  [
                     {
                         "Name":  "FirstName",
                         "Type":  "XXXX",
                     },
                     {
                         "Name":  "SecondName",
                         "Type":  "YYYY",
                     },
                     {
                     "Name":  "ThirdName",
                         "Type":  "ZZZZ",
                     },
            ],
 "TotalCount":  127
 }

I want a CSV formatted like

"Name","Type"
"FirstName","XXXX"
"SecondName","YYYY"

and so on. I tried creating a PSCustomObject and using Select-Object with it to generate the CSV but it does not give me the desired output. My code:

$report=$null
foreach($obj in $json){
$item=$obj | ConvertFrom-Json
$report=[pscustomobject]@{
Name=($item.Objects.Name | Out-String).Trim()
Type=($item.Objects.Type | Out-String).Trim()
}
$report | Select-Object Name,Type | Export-Csv "PATH"`

Gives me CSV that looks like:

"Name","Type"
"FirstName
 SecondName
 ThirdName",
"XXXX
 YYYY
 ZZZZ"

Upvotes: 0

Views: 748

Answers (1)

thepip3r
thepip3r

Reputation: 2935

if you really want the total count in the csv as well, here is one thing you could do:

$psobjarray = ($json | convertfrom-json)
$psobjarray.objects | Select-Object Name,Type | Export-Csv $Path
"Total Count: $($psobjarray.totalcount)" | out-file $Path -append

Upvotes: 1

Related Questions