PowerShell script to convert CSV file format to Complex Nested JSON

Frequently, we get raw data in the form of excel or csv format and for writing test cases or for testing APIs, we need to convert that data to JSON format. Doing that manually is both error prone & tedious task. So, recently I was working on creating a PowerShell script that can convert CSV data to Complex Nested JSON as per requirements.

In this post, I will be sharing how we can convert CSV to Complex Nested JSON using a PowerShell script.

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

Step 1: Sample Input CSV Data

SampleDataCarInfo.csv

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

CSVToJson.ps1 PowerShell script content:

$inputCSVFile = "D:\CSV to Json Sample\SampleDataCarInfo.csv"
$outputJSONFile = "D:\CSV to Json Sample\SampleDataCarInfo.json"

$toJSON = import-csv $inputCSVFile | Group-Object -Property Make, MakeDescription | ForEach-Object { 
  $make = $_
  [pscustomobject]@{
    Name = $_.Values[0]
        Desc = $_.Values[1]
    Models = @( 
      $_.Group | Group-Object Model, ModelDescription, ModelBodyType, ModelRating | ForEach-Object { 
                $model = $_
        [pscustomobject]@{
          Name = ($make.Values[0] + ' ' + $_.Values[0])
          Desc = $_.Values[1]
                    BodyType = $_.Values[2]
                    Rating = [float]$_.Values[3]
                    Variants = @( 
                  $_.Group | Group-Object Variant, VariantDescription, Price, Transmission, Fuel | ForEach-Object { 
                    [pscustomobject]@{
                      Name = ($make.Values[0] + ' ' + $model.Values[0] + ' ' + $_.Values[0])
                      Desc = $_.Values[1]
                                Price = [float]$_.Values[2]
                                Transmission = $_.Values[3]
                                Fuel = $_.Values[4]
                    }
                  }
                    )
        }
      }
        )
    }
} | ConvertTo-Json -Depth 5 | % { [System.Text.RegularExpressions.Regex]::Unescape($_) } | Out-File $outputJSONFile -Force

Here:

$inputCSVFile = “D:\CSV to Json Sample\SampleDataCarInfo.csv”

$outputJSONFile = “D:\CSV to Json Sample\SampleDataCarInfo.json”

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

SampleDataCarInfo.json content:

[
    {
        "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"
                                            }
                                        ]
                       }
                   ]
    }
]

That’s it !!!