Convert Json input to CSV output file using powerShell

Question

I have a json file as given below.

{
    "count":  100,
     "value":  [
                  {
                  "id":  264871,
                  "release":  {
                                  "id":  36803,
                                  "name":  "Test_1020_SP_1",
                                  "url":  "https://vsrm.dev.azure.com/TestProject/6d203219-63b3-4b1c-b4fe-fa9172d74fb1/_apis/Release/releases/36803",
                                  "artifacts":  [
                                                    {
                                                        "sourceId":  "6d203219-63b3-4b1c-b4fe-fa9172d74fb1/efa62ff5-7dc3-4b5a-b7e1-29a319550b28:12e54cbd-881f-43de-8442-4454ffba61fb",
                                                        "type":  "PackageManagement",
                                                        "alias":  "_Project.scripts.release",
                                                        "definitionReference":  {
                                                                                    "definition":  {
                                                                                                       "id":  "12e54cbd-881f-43de-8442-4454ffba61fb",
                                                                                                       "name":  "Project.scripts.release"
                                                                                                   },
                                                                                    "feed":  {
                                                                                                 "id":  "6d203219-63b3-4b1c-b4fe-fa9172d74fb1/efa62ff5-7dc3-4b5a-b7e1-29a319550b28",
                                                                                                 "name":  "Project_release_scripts"
                                                                                             },
                                                                                    "files":  {
                                                                                                  "id":  "**",
                                                                                                  "name":  "**"
                                                                                              },
                                                                                    "packageType":  {
                                                                                                        "id":  "nuget",
                                                                                                        "name":  "NuGet"
                                                                                                    },
                                                                                    "skipextract":  {
                                                                                                        "id":  "",
                                                                                                        "name":  ""
                                                                                                    },
                                                                                    "version":  {
                                                                                                    "id":  "2022.10.11.1",
                                                                                                    "name":  "2022.10.11.1"
                                                                                                },
                                                                                    "view":  {
                                                                                                 "id":  "",
                                                                                                 "name":  ""
                                                                                             }
                                                                                },
                                                        "isPrimary":  true,
                                                        "isRetained":  false
                                                    }
                                                ],
                                  "webAccessUri":  "https://dev.azure.com/TestProject/6d203219-63b3-4b1c-b4fe-fa9172d74fb1/_release?releaseId=36803\u0026_a=release-summary",
                                  "_links":  {
                                                 "self":  {
                                                              "href":  "https://vsrm.dev.azure.com/TestProject/6d203219-63b3-4b1c-b4fe-fa9172d74fb1/_apis/Release/releases/36803"
                                                          },
                                                 "web":  {
                                                             "href":  "https://dev.azure.com/TestProject/6d203219-63b3-4b1c-b4fe-fa9172d74fb1/_release?releaseId=36803\u0026_a=release-summary"
                                                         }
                                             }
                              },
                  "releaseDefinition":  {
                                            "id":  6777,
                                            "name":  "Test_Deploy",
                                            "path":  "\\Test",
                                            "projectReference":  {
                                                                     "id":  "6d203219-63b3-4b1c-b4fe-fa9172d74fb1",
                                                                     "name":  null
                                                                 },
                                            "url":  "https://vsrm.dev.azure.com/TestProject/6d203219-63b3-4b1c-b4fe-fa9172d74fb1/_apis/Release/definitions/6777",
                                            "_links":  {
                                                           "self":  {
                                                                        "href":  "https://vsrm.dev.azure.com/TestProject/6d203219-63b3-4b1c-b4fe-fa9172d74fb1/_apis/Release/definitions/6777"
                                                                    },
                                                           "web":  {
                                                                       "href":  "https://dev.azure.com/TestProject/6d203219-63b3-4b1c-b4fe-fa9172d74fb1/_release?definitionId=6777"
                                                                   }
                                                       }
                                        }
              },
              {
                  "id":  264870,
                  "release":  {
                                  "id":  36800,
                                  "name":  "Test_2698_SP_1",
                                  "url":  "https://vsrm.dev.azure.com/TestProject/6d203219-63b3-4b1c-b4fe-fa9172d74fb1/_apis/Release/releases/36800",
                                  "artifacts":  [
                                                    {
                                                        "sourceId":  "6d203219-63b3-4b1c-b4fe-fa9172d74fb1/efa62ff5-7dc3-4b5a-b7e1-29a319550b28:12e54cbd-881f-43de-8442-4454ffba61fb",
                                                        "type":  "PackageManagement",
                                                        "alias":  "_Project.scripts.release",
                                                        "definitionReference":  {
                                                                                    "definition":  {
                                                                                                       "id":  "12e54cbd-881f-43de-8442-4454ffba61fb",
                                                                                                       "name":  "Project.scripts.release"
                                                                                                   },
                                                                                    "feed":  {
                                                                                                 "id":  "6d203219-63b3-4b1c-b4fe-fa9172d74fb1/efa62ff5-7dc3-4b5a-b7e1-29a319550b28",
                                                                                                 "name":  "Project_release_scripts"
                                                                                             },
                                                                                    "files":  {
                                                                                                  "id":  "**",
                                                                                                  "name":  "**"
                                                                                              },
                                                                                    "packageType":  {
                                                                                                        "id":  "nuget",
                                                                                                        "name":  "NuGet"
                                                                                                    },
                                                                                    "skipextract":  {
                                                                                                        "id":  "",
                                                                                                        "name":  ""
                                                                                                    },
                                                                                    "version":  {
                                                                                                    "id":  "2022.10.11.1",
                                                                                                    "name":  "2022.10.11.1"
                                                                                                },
                                                                                    "view":  {
                                                                                                 "id":  "",
                                                                                                 "name":  ""
                                                                                             }
                                                                                },
                                                        "isPrimary":  true,
                                                        "isRetained":  false
                                                    }
                                                ],
                                  "webAccessUri":  "https://dev.azure.com/TestProject/6d203219-63b3-4b1c-b4fe-fa9172d74fb1/_release?releaseId=36800\u0026_a=release-summary",
                                  "_links":  {
                                                 "self":  {
                                                              "href":  "https://vsrm.dev.azure.com/TestProject/6d203219-63b3-4b1c-b4fe-fa9172d74fb1/_apis/Release/releases/36800"
                                                          },
                                                 "web":  {
                                                             "href":  "https://dev.azure.com/TestProject/6d203219-63b3-4b1c-b4fe-fa9172d74fb1/_release?releaseId=36800\u0026_a=release-summary"
                                                         }
                                             }
                              },
                  "releaseDefinition":  {
                                            "id":  6777,
                                            "name":  "Test_Deploy",
                                            "path":  "\\Test",
                                            "projectReference":  {
                                                                     "id":  "6d203219-63b3-4b1c-b4fe-fa9172d74fb1",
                                                                     "name":  null
                                                                 },
                                            "url":  "https://vsrm.dev.azure.com/TestProject/6d203219-63b3-4b1c-b4fe-fa9172d74fb1/_apis/Release/definitions/6777",
                                            "_links":  {
                                                           "self":  {
                                                                        "href":  "https://vsrm.dev.azure.com/TestProject/6d203219-63b3-4b1c-b4fe-fa9172d74fb1/_apis/Release/definitions/6777"
                                                                    },
                                                           "web":  {
                                                                       "href":  "https://dev.azure.com/TestProject/6d203219-63b3-4b1c-b4fe-fa9172d74fb1/_release?definitionId=6777"
                                                                   }
                                                       }
                                        }
              }
          ]

}

I wan to convert the above json file to csv format with heading as given below

I tried using the below command

(Get-Content -Path "C:\Test.json") | ConvertFrom-Json | Select-Object -expand value | ConvertFrom-Csv |Out-File C:\Test.csv.

But the value is not coming in the expected format and it is coming without the column names

You try to place a hierarchical data structure into a structured display ... that does not work the way you may think it does. ;-) — Olaf, Nov 01 '22 at 09:46
See: [Convert nested JSON array into separate columns in CSV file](https://stackoverflow.com/a/46081131/1701026) — iRon, Nov 01 '22 at 10:10
@iRon I tried that, it is better. But everything is written to single line. I'm expecting a new raw for entries stating with new "id". Also expecting same heading for all rows. Now it is coming like "values.{cout}.heading_name. — mystack, Nov 01 '22 at 11:23
Try: `(Get-Content -Path "C:\Test.json" -Raw | ConvertFrom-Json).Value |Flatten-Object` — iRon, Nov 01 '22 at 12:08

score 0 · Answer 1 · answered Nov 03 '22 at 22:16

This should work, though it will generate a column per property (including inner arrays).

I would not expect this to work if the json had nested arrays.

# function to flatten a PSCustomObject into a hashtable of primitives
# this can probably be cleaned up
function flatten($customObj) {
    $flatHashtable = @{}

    foreach ($entry in @($customObj.PSObject.Properties)) {
        $value = $entry.Value
        if ($value -is [Array]) {
            $valueArray = $value
            $size = $value.Count
            $keyTemplate = '/{0}/'
        } else {
            $valueArray = @($value)
            $size = 1
            $keyTemplate = '/'
        }

        for ($i = 0; $i -lt $size; $i++) {
            $item = $valueArray[$i]
            $keySeparator = $keyTemplate -f $i
            if ($item -is [PSObject]) {
                $subentries = flatten $item # recursive

                foreach ($subentry in $subentries.GetEnumerator()) {
                    $flattenedKey = $entry.Name + $keySeparator + $subentry.Key
                    if (!($flatHashtable.ContainsKey($flattenedKey))) {
                        $flatHashtable.Add($flattenedKey, $subentry.Value)
                    }
                }
            } else {
                if (!($flatHashtable.ContainsKey($entry.Name))) {
                    $flatHashtable.Add($entry.Name, $item)
                }
            }
        }
    }

    return $flatHashtable
}

$jsonText = '<your json here>'
$jsonObj = $jsonText | ConvertFrom-Json

# ignore "count" property
$releases = $jsonObj.value

$rows = @()
$releases | ForEach-Object { $rows += (flatten $_) }
$csvColumns = $rows.Keys | Group-Object | Select-Object -ExpandProperty Name | Sort-Object
$rows | Select-Object -Property $csvColumns | ConvertTo-Csv | Set-Content '<path to csv file>'

Convert Json input to CSV output file using powerShell

1 Answers1