PowerShell script to convert Complex Nested JSON to CSV file format

Recently, I was working on a small utility to convert JSON data to CSV format. In this post, I will be sharing how we can use a simple PowerShell script for converting Complex Nested JSON to CSV format.

Refer for PowerShell script to convert CSV file format to Complex Nested JSON

Step 1: Sample Input JSON

[
    {
        "Name":  "Hyundai",
        "Desc":  "Hyundai Sample Desc",
        "Models":  [
                       {
                           "Name":  "Hyundai Creta",
                           "Desc":  "Hyundai Creta Sample Desc",
                           "BodyType":  "SUV",
                           "Rating":  4.5,
                           "Variants":  [
                                            {
                                                "Name":  "Hyundai Creta SX Executive",
                                                "Desc":  "Hyundai Creta SX Executive Sample Desc",
                                                "Price":  13.34,
                                                "Transmission":  "Manual",
                                                "Fuel":  "Petrol"
                                            },
                                            {
                                                "Name":  "Hyundai Creta SX Opt Diesel AT",
                                                "Desc":  "Hyundai Creta SX Opt Diesel AT Sample Desc",
                                                "Price":  15.16,
                                                "Transmission":  "Automatic",
                                                "Fuel":  "Diesel"
                                            }
                                        ]
                       },
                       {
                           "Name":  "Hyundai Verna",
                           "Desc":  "Hyundai Verna Sample Desc",
                           "BodyType":  "Sedan",
                           "Rating":  4.3,
                           "Variants":  [
                                            {
                                                "Name":  "Hyundai Verna S Plus",
                                                "Desc":  "Hyundai Verna S Plus Sample Desc",
                                                "Price":  9.69,
                                                "Transmission":  "Manual",
                                                "Fuel":  "Petrol"
                                            },
                                            {
                                                "Name":  "Hyundai Verna S Plus Diesel",
                                                "Desc":  "Hyundai Verna S Plus Diesel Sample Desc",
                                                "Price":  10.88,
                                                "Transmission":  "Manual",
                                                "Fuel":  "Diesel"
                                            }
                                        ]
                       }
                   ]
    },
    {
        "Name":  "Kia",
        "Desc":  "Kia Sample Desc",
        "Models":  [
                       {
                           "Name":  "Kia Seltos",
                           "Desc":  "Kia Seltos Sample Desc",
                           "BodyType":  "SUV",
                           "Rating":  4.4,
                           "Variants":  [
                                            {
                                                "Name":  "Kia Seltos HTE D",
                                                "Desc":  "Kia Seltos HTE D Sample Desc",
                                                "Price":  10.45,
                                                "Transmission":  "Manual",
                                                "Fuel":  "Diesel"
                                            }
                                        ]
                       }
                   ]
    }
]

Step 2: PowerShell script to convert Complex Nested JSON to CSV Data

JsonToCsv.ps1 PowerShell script content:

$pathToJsonFile = "D:\CSV to Json Sample\SampleDataCarInfo.json"
$pathToOutputFile = "D:\CSV to Json Sample\SampleDataCarInfoOutput.csv"
((Get-Content -Path $pathToJsonFile) | ConvertFrom-Json) | ForEach-Object {
$Make = $_.Name
$MakeDescription = $_.Desc
$Models = $_.Models | ForEach-Object {
    $Model = $_.Name
    $ModelDescription = $_.Desc
    $ModelBodyType = $_.BodyType
    $ModelRating = $_.Rating
    $Variants += $_.Variants | ForEach-Object {
       [pscustomobject] @{
            'Make' = $Make
            'MakeDescription' = $MakeDescription
            'Model' = $Model
            'ModelDescription' = $ModelDescription
            'ModelBodyType' = $ModelBodyType
            'ModelRating' = $ModelRating
            'Variant' = $_.Name
            'VariantDescription' = $_.Desc
            'Price' = $_.Price
            'Transmission' = $_.Transmission
            'Fuel' = $_.Fuel
        }
    }
}
} 

$Variants | Export-CSV $pathToOutputFile -NoTypeInformation

Here:

$pathToJsonFile = “D:\CSV to Json Sample\SampleDataCarInfo.json”
$pathToOutputFile = “D:\CSV to Json Sample\SampleDataCarInfoOutput.csv”

Step 3: Execute PowerShell script to convert Complex Nested JSON to CSV Data

SampleDataCarInfoOutput.csv 

That’s It !!!

Kapil Khandelwal

.NET Professional | Microsoft Certified Professional | DZone’s Most Valuable Blogger | Web Developer | Author | Blogger

Doctorate in Computer Science and Engineering

Microsoft Certified Professional (MCP) with over 12+ years of software industry experience including development, implementation & deployment of applications in the .NET framework

Experienced and skilled Agile Developer with a strong record of excellent teamwork, successful coding & project management. Specialises in problem identification and proposal of alternative solutions. Provided knowledge and individual mentoring to team members as needed

Among top 3% overall in terms of contribution on Stack Overflow (~2.3 million people reached my posts). Part of the top 1% Stack Overflow answerers in ASP.NET technology.

DZone’s Most Valuable Blogger (MVB)

Created and actively maintain the TechCartNow.com tech blog while also editing, writing, and researching topics for publication.

Excellent skills in Application Development using C#/Vb.Net, .NET Framework, ASP.NET, MVC, ADO.NET, WCF, WPF, Web API, SQL Server, jQuery, Angular, React, BackboneJS