Dyamic JSON Output - Test For Values

PowerShell Help
1

I need to filter out items in my CSV that have a null or blank value, they cannot be included within the JSON body. Looking around the interwebs, this looks like a common issue to which I’ve found a few solutions that suggest populating a new object and then calling this within the powershell block. I am having trouble with the JSON nesting required in this body to map the necessary objects.

Here’s a two row CSV sample to help demonstrate what I am dealing with

Id,FirstName,MiddleName,LastName,Segment__c
001,First,Middle,Last,Operations
002,First2,Middle2,Last2,

Here’s the current result set


Row1

{
    "ID":  "001",
    "FirstName":  "First",
    "MiddleName":  "Middle",
    "LastName":  "Last",
	"OwnerCode":  "Operations",
}

Row2


{
    "ID":  "002",
    "FirstName":  "First2",
    "MiddleName":  "Middle2",
    "LastName":  "Last2",
	"OwnerCode":  null,
}

Here’s what I need to achieve

Row1

{
    "ID":  "001",
    "FirstName":  "First",
    "MiddleName":  "Middle",
    "LastName":  "Last",
    "OwnerCode":  "Operations"
}

Row2


{
    "ID":  "002",
    "FirstName":  "First2",
    "MiddleName":  "Middle2",
    "LastName":  "Last2"
	
}

How can I convert this to be dynamic in body, excluding the the value pair for OwnerCode if the value is null in the CSV? And retaining the nested structure of the JSON. Thank you!

#Import The Contacts for Updates
$ContactList = import-csv  "C:\ContactResults.csv"


Start  API Update Loop for Contacts
ForEach ($row in $ContactList) {

                                $body3 = @{
                                            value = @( #this structure is whats tripping me up
                                                        @{
                                                            ContactID = $row.ContactID__c
                                                            FirstName = $row.FirstName
                                                            MiddleName = $row.MiddleName
                                                            LastName = $row.LastName
                                                            OwnerCode = $row.Segment__C #example - if null or blank remove this key pair
                                                         }
                                                     )  
                                } | ConvertTo-Json

$body3

try {
    $response2 = Invoke-RestMethod 'http://api/odata/v1/Contacts' -Method 'PATCH' -Headers $headers2 -Body $body3
    $users = $response2.value
    $users | Export-Csv -Path   "C:\Log.csv" -NoTypeInformation -Append -Force
}

catch { 
   $erroutput = ParseErrorForResponseBody($_)  
   $erroutput | Out-File "C:\ErrorLog.csv" -Append
}

}

...
2

Hi Ian, welcome to the forum. :wave:t4:

I think it might be more beneficial to show a sanitized example of the input CSV and an example of what your expected output should look like.

1 Like
3

Thank you krzydoug, I have updated my post. I’m unsure how to attach a sample CSV file.

4

Simply post a few (sanitized) lines of it formatted as code as well, please. :wink:

1 Like