Trying to convert CSV into a particular JSON format

Question

I need to convert a CSV to a particular JSON format but having trouble.

I currently have created the below powershell code which takes a CSV file with multiple columns and data for each column

enter code here $csvcontent = get-content "C:\tmp\vmfile.csv" | select -Skip 1
$Json =foreach($line in $csvcontent){

$obj = [PSCustomObject]@{
    description = ($line -split ",")[0] -replace "`""
    requestedFor = ($line -split ",")[1] -replace "`""
    VMs = @{
    vmType = $(($line -split ",")[5] -replace "`"");
    environment = $(($line -split ",")[6] -replace "`"");
    vmdescription = $(($line -split ",")[7] -replace "`"");
    function = $(($line -split ",")[8] -replace "`"");
    datacenter = $(($line -split ",")[9] -replace "`"");
    Size = $(($line -split ",")[10] -replace "`"");
    adDomain = $(($line -split ",")[11] -replace "`"");
    Hostname = $(($line -split ",")[12] -replace "`"")
    }
    ExtraDisks = @{
    VolumeName = $(($line -split ",")[14] -replace "`"");
    VolumeLetter = $(($line -split ",")[15] -replace "`"");
    Size = $(($line -split ",")[16] -replace "`"")
    }
}

$obj | ConvertTo-Json

} 

$json -replace '(?<=:\s+){','[ {' -replace '(?<="\s+)}','} ]'

This then generates the following json file which is not what i need as i want it all to come under the VM brackets not have a separate one for each VM

enter code here

{
"requestedFor":  "John Doe",
"VMs":  {
            "Size":  "Medium",
            "datacenter":  "DC1",
            "environment":  "dev",
            "adDomain":  "mydomain.com",
            "vmType":  "Windows Server",
            "vmdescription":  "VM Build1",
            "function":  "app",
            "Hostname":  "VMBuild1"
        },
"ExtraDisks":  {
                   "VolumeLetter":  "G",
                   "Size":  "10",
                   "VolumeName":  "Logs"
               }
  }
 {
   "requestedFor":  "John Doe",
   "VMs":  {
            "Size":  "Medium",
            "datacenter":  "DC2",
            "environment":  "prod",
            "adDomain":  "mydomain.com",
            "vmType":  "Windows Server",
            "vmdescription":  "VM Build2",
            "function":  "app",
            "Hostname":  "VMBuild2"
        },
"ExtraDisks":  {
                   "VolumeLetter":  "E",
                   "Size":  "50",
                   "VolumeName":  "Data"
               }

}

but what i need it to look like this

enter code here 
{
"requestedFor":  "John Doe",
"VMs": [ {
    "vmType": "Windows Server",
    "environment": "dev",
    "description":  "VMBuild1",
    "function": "app",
    "datacenter": "DC1",
    "size": "Medium",
    "adDomain": "mydomain.com",
    "Hostname": "VMBuild1",
            "ExtraDisks": [ {
        "VolumeName": "Logs",
        "VolumeLetter": "G",
        "VolumeSize": 10
        }
    ]
    },
    {
    "vmType": "Windows Server",
    "environment": "prod",
    "description":  "VMBuild2",
    "function": "app",
    "datacenter": "DC2",
    "size": "Medium",
    "adDomain": "mydomain.com",
    "Hostname": "VMBuild2",
            "ExtraDisks": [ {
        "VolumeName": "Data",
        "VolumeLetter": "E",
        "VolumeSize": 50
        }
    ]
    }
    ]
    }

Here is the CSV file contents

     vmType environment description function    datacenter  Size    adDomain    Hostname    VolumeName  VolumeLetter    VolumeSize
     Windows Server dev VMBuild1    app DC1 Medium  mydomain.com    VMBUILD1    Logs    G   10
     Windows Server prod    VMBuild2    app DC2 Medium  mydomain.com    VMBUILD2    Data    E   50

Please show us the first 3 or 4 lines of the `C:\tmp\vmfile.csv` **AS-IS**. That means open it in Notepad, copy the first couple of lines and paste that in your question as [Formatted](https://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks/22189#22189) text. — Theo, Jul 18 '20 at 13:25
@Theo, thank you for your reply, i have added the CSV contents to original question — likwid786, Jul 19 '20 at 18:02
Please could you also add this csv example as TEXT instead of an image? — Theo, Jul 19 '20 at 18:09

score 0 · Answer 1 · answered Jul 17 '20 at 03:40

You don't need to parse the csv yourself. That's what ConvertFrom-Csv / Import-CSV are for.

Here's how I'd do it.

$CSVObj = get-content "C:\tmp\vmfile.csv" -Raw | ConvertFrom-Csv
$CSVObj | ConvertTo-Json | Set-Content "C:\tmp\vmfile.json"

That's all !

But let's go further. There was no CSV sample in your question so one might assume that the output JSON might still be incorrect. How would you make sure to have the format you want ?

By creating a brand new object structure from the imported object and then exporting it.

Here's a simple expression of what that might look like.


$CSVObj = get-content "C:\tmp\vmfile.csv" -Raw | ConvertFrom-Csv

# Create a new object from $csvObj that you will then export to csv
$Output = foreach ($item in $CSVObj) {
    [PSCustomObject]@{
        Requester = $item.requestedFor
        VMs       = $item.VMs
        Count     = $item.VMs.Count
    }
}

$output | ConvertTo-Json | Set-Content "C:\tmp\vmfile.json"

You would then have successfully modified the json to be output to fit your needs.

Hi Sage, thank you for your reply, i have uploaded the csv i am using [link](https://www.mediafire.com/file/wkk5c0nqqsd5ja5/vmfile1.csv/file). The output i get running your code does not provide me with all the VMs on each line coming in-between the code "VMs": [ { — likwid786, Jul 17 '20 at 11:38
please see image showing output [link](https://www.mediafire.com/view/1huj1avljryrrun/JsonOutput.PNG/file). As you can see for each VM it starts a new VMs": [{. i need all the VMs to come under just one — likwid786, Jul 17 '20 at 12:14

score 0 · Accepted Answer · answered Jul 20 '20 at 10:02

Although your example CSV doesn't show it (copy/paste from Excel), I'm assuming it looks like this when opened in Notepad:

"vmType","environment","description","function","datacenter","Size","adDomain","Hostname","VolumeName","VolumeLetter","VolumeSize"
"Windows Server","dev","VMBuild1","app","DC1","Medium","mydomain.com","VMBUILD1","Logs","G","10"
"Windows Server","prod","VMBuild2","app","DC2","Medium","mydomain.com","VMBUILD2","Data","E","50"
"Windows Server","dev","VMBuild1","app","DC1","Medium","mydomain.com","VMBUILD1","Scripts","H","25"

The CSV does not have a column for RequestedFor, so the code below uses that as hardcoded variable.

Instead of reading the csv as string array and doing a lot of splitting and removing quote characters, you need to use Import-Csv.

After that, the only thing left to do is the way you want the final JSON formatted.

$requestor = 'John Doe'
$csvData   = Import-Csv -Path 'D:\Test\vmfile.csv'

# get an array of PSObjects
# we use 'Group-Object Hostname' here to allow VMs with multiple extra disks
$allVMs = $csvData | Group-Object Hostname | ForEach-Object {
    $disks = $_.Group | Select-Object VolumeName, VolumeLetter, VolumeSize
    $vm = $_.Group[0] | Select-Object * -ExcludeProperty VolumeName, VolumeLetter, VolumeSize
    $vm | Add-Member -MemberType NoteProperty -Name 'ExtraDisks' -Value @($disks)
    # output the VM object
    $vm
}

# combine the requestor, main element 'VMs' and the objects 
# gathered above into a new object and convert that to JSON
[PsCustomObject]@{
    RequestedFor = $requestor
    VMs          = @($allVMs)
} | ConvertTo-Json -Depth 4

Output:

{
    "RequestedFor":  "John Doe",
    "VMs":  [
                {
                    "vmType":  "Windows Server",
                    "environment":  "dev",
                    "description":  "VMBuild1",
                    "function":  "app",
                    "datacenter":  "DC1",
                    "Size":  "Medium",
                    "adDomain":  "mydomain.com",
                    "Hostname":  "VMBUILD1",
                    "ExtraDisks":  [
                                       {
                                           "VolumeName":  "Logs",
                                           "VolumeLetter":  "G",
                                           "VolumeSize":  "10"
                                       },
                                       {
                                           "VolumeName":  "Scripts",
                                           "VolumeLetter":  "H",
                                           "VolumeSize":  "25"
                                       }
                                   ]
                },
                {
                    "vmType":  "Windows Server",
                    "environment":  "prod",
                    "description":  "VMBuild2",
                    "function":  "app",
                    "datacenter":  "DC2",
                    "Size":  "Medium",
                    "adDomain":  "mydomain.com",
                    "Hostname":  "VMBUILD2",
                    "ExtraDisks":  [
                                       {
                                           "VolumeName":  "Data",
                                           "VolumeLetter":  "E",
                                           "VolumeSize":  "50"
                                       }
                                   ]
                }
            ]
}

Of course, you can save this in a json file, by appending | Set-Content -Path 'TheOutputFile.json' to it.

P.S. PowerShell does not produce 'pretty' json. If you need to convert it to properly spaced json, see my function Format-Json

Thank you @Theo that is exactly what i needed – likwid786 Jul 20 '20 at 16:29 — likwid786, Jul 20 '20 at 16:29

Trying to convert CSV into a particular JSON format

2 Answers2