PowerShell Convert CSV to nested Json

Question

I try to create a script that will import data given in a csv file to an API, in order to do this, each line of the CSV has to be converted to Json and then posted to the API.

My base data in the csv looks like this:

Users.Name,Users.Mail,Users.AdditionalInfo.String1,Users.AdditionalInfo.Int1
System.String,System.String,System.String,System.Int32
MyName,my@name.com,Hello,1
YourName,your@name.com,GoodBye,2

The first line contains the Json Information, where "Users" is the API resource the data is written to. "Users" can be any other object accessible via the API also, see my comment below.

The second line is to identify the type of the value. This is for later use and not used right now, beginning with line three is data.

The "." is used to specify the "level" of nesting, so the first "." separates the API Object from the first level in the json, the second "." the first level from the second level.

I want a Json to be returned like this:

{
    "Name": "MyName",
    "Mail": "my@mail.com",
    "AdditionalInfo": {
        "String1": "Hello",
        "Int1": 1
    }
}

I use the following code:

$objects = Import-Csv "test.csv" -Encoding UTF8
$validObjects = $objects | Select-Object -Skip 1
$validObjects

ForEach ($object in $validObjects) {
    
    #$apiObject = ($object.psobject.properties.name).Split(".")[0]
    $jsonObject = @{}

    ForEach ($property in $object.psobject.properties) {
       
       switch($property.Name.Split(".").Count - 1) {
            1 {
                $jsonObject.Add($property.Name.Split(".")[1], $property.Value)        
            }
            2 {
                $tempJsonObject = @{$property.Name.Split(".")[2] =  $property.Value}            
                $jsonObject.Add($property.Name.Split(".")[1], $tempJsonObject)        
            }
        }   
    }

    $jsonObject | ConvertTo-Json
    #@($object.psobject.properties).Count
}

The problem i face now is that since i have two colums starting with "Users.AdditionalInfo" ist will run into an error, because you can add "AdditionalInfo" only as a key once. Is there an easy way around it?

Answer 1

Seems pretty crazy to have it set up this way. Surely XML would be a better format for the needs. That said, here is what I came up with.

Setting up sample file so others can try this themselves.

$tempfile = New-TemporaryFile

@'
Users.Name,Users.Mail,Users.AdditionalInfo.String1,Users.AdditionalInfo.Int1
System.String,System.String,System.String,System.Int32
MyName,my@name.com,Hello,1
YourName,your@name.com,GoodBye,2
'@ | Set-Content $tempfile -Encoding utf8

$csvdata = Import-Csv $tempfile | select -skip 1

Now here's my crazy script

$csvdata | foreach {
    $ht = [ordered]@{}
    ,@($_.psobject.properties) | foreach {
        $subprops,$props = $_.where({$_.name -match 'additionalinfo'},'split')
        $props | foreach {$ht.Add($_.name,$_.value)}
        ,@($subprops) | foreach {
            $ht.Add("AdditionalInfo",(@{
                ($_[0].name.split(".")[2]) = $_[0].value
                ($_[1].name.split(".")[2]) = $_[1].value
            }))
        }
    }
    $ht
} | ConvertTo-Json -OutVariable jsonresults

And the output shown plus stored in $jsonresults

[
    {
        "Users.Name":  "MyName",
        "Users.Mail":  "my@name.com",
        "AdditionalInfo":  {
                               "String1":  "Hello",
                               "Int1":  "1"
                           }
    },
    {
        "Users.Name":  "YourName",
        "Users.Mail":  "your@name.com",
        "AdditionalInfo":  {
                               "String1":  "GoodBye",
                               "Int1":  "2"
                           }
    }
]

A trick I'm sure I picked up from mklement or Mathias that was used twice is

,@(some objects that normally get passed one by one) | foreach

When you add the comma and the array construct, it passes all elements as one vs one at at time. Very helpful in certain times, like these for sure.

The other trick I wanted to highlight is this line

$subprops,$props = $_.where({$_.name -match 'additionalinfo'},'split')

The where method has several modes, one being split. Any that matched additional went to the first variable, the rest went to the second. Hopefully this helps you complete your project.

Edit

Since the items like additionalinfo can be different with varying amount of subproperties, here is a version that will accommodate.

$csvdata | foreach {
    $ht = [ordered]@{}
    ,@($_.psobject.properties) | foreach {
        $subprops,$props = $_.where({($_.name.split("."))[2]},'split')
        $props | foreach {$ht.Add($_.name,$_.value)}
        $subs = $subprops | foreach {$_.name.split(".")[1]} | Select -Unique
        foreach($name in $subs)
        {
            ,@($subprops | where name -match $name) | foreach {
                $oht = [ordered]@{}
                $_ | foreach {$oht[$_.name.split(".")[2]] = $_.value}
                $ht.Add($name,$oht)
            }
        }
    }
    $ht
} | ConvertTo-Json -OutVariable jsonresults -Depth 5

First data set output

[
    {
        "Users.Name":  "MyName",
        "Users.Mail":  "my@name.com",
        "AdditionalInfo":  {
                               "String1":  "Hello",
                               "Int1":  "1"
                           }
    },
    {
        "Users.Name":  "YourName",
        "Users.Mail":  "your@name.com",
        "AdditionalInfo":  {
                               "String1":  "GoodBye",
                               "Int1":  "2"
                           }
    }
]

@'
Users.Name,Users.Mail,Users.AnotherPossibility.String1,Users.AnotherPossibility.Int1,Users.AnotherPossibility.Int2,Users.AdditionalInfo.String1,Users.AdditionalInfo.Int1
System.String,System.String,System.String,System.Int32,System.String,System.Int32
MyName,my@name.com,Hello,1,3,Bonjour,5
YourName,your@name.com,GoodBye,2,4,Adios,6
'@ | Set-Content $tempfile -Encoding utf8

Second data set output

[
    {
        "Users.Name":  "MyName",
        "Users.Mail":  "my@name.com",
        "AnotherPossibility":  {
                                   "String1":  "Hello",
                                   "Int1":  "1",
                                   "Int2":  "3"
                               },
        "AdditionalInfo":  {
                               "String1":  "Bonjour",
                               "Int1":  "5"
                           }
    },
    {
        "Users.Name":  "YourName",
        "Users.Mail":  "your@name.com",
        "AnotherPossibility":  {
                                   "String1":  "GoodBye",
                                   "Int1":  "2",
                                   "Int2":  "4"
                               },
        "AdditionalInfo":  {
                               "String1":  "Adios",
                               "Int1":  "6"
                           }
    }
]