Powershell - Parsing inconsistent Json Data

user_5321769 0
Viewed 292

I got a CSV file with an ID and Json data. I need to parse the data into a CustomerData class.

However, I am having problem accessing the value for a given key

I got this CSV file. The Json might contain some keys in some records and might not be there in the other. Basically just fields that have been updated exists.

ID;CHANGES
713422;[{"key":"zipCode","updatedValue":""},,{"key":"language","updatedValue":"EN"},{"key":"coaddress","updatedValue":""},{"key":"SSN","updatedValue":""},{"key":"msisdn","updatedValue":"1114455789"}]
114365;[{"key":"city","previousValue":"New York","updatedValue":"Palm City"},{"key":"zipcode","previousValue":"100012","updatedValue":"02118"},{"key":"coaddress","updatedValue":""},{"key":"streetaddress","previousValue":"9253 Del Monte Road"updatedValue":"90 Kent Ave"},{"key":"SSN","updatedValue":""},{"key":"companyName","previousValue":"Nutrics","updatedValue":"NutriTiger"}]
114365;[{"key":"zipCode","updatedValue":""},{"key":"coaddress","updatedValue":""},{"key":"SSN","updatedValue":""},{"key":"companyName","previousValue":"NutriTiger","updatedValue":"Nutri-Tiger"}]
713422;[{"key":"zipCode","updatedValue":""},{"key":"coaddress","updatedValue":"Roady Road"},{"key":"SSN","updatedValue":""},{"key":"msisdn","updatedValue":""}]

What I want to do is something like this. There is 8 keys in the Json data that might occur and I need to check if it exists and grab the value if it does.

$city
if (TryParse(Json.city.GetValue, $city )
{
    CustomerData.$city = Json.city.GetValue
}

Here is what I am right now. I am stuck in the If statement, where I try to access the $jsondata in different ways. I've tried $jsonData.city $jSonData.getValue('city') Different kinds of piping the $jsonData and do selects

But I just can't seem to get the value for a given key.

What I eventually need is to build up a new CSV file where I search for the ID and then update the given field, like the city if it has been updated. 

class CustomerData
{
    [int]$Id = 0

    [string]$companyName = ""
    [string]$ssn = ""
    [string]$msisdn = ""
    [string]$language = ""

    [string]$city = ""
    [string]$coaddress = ""
    [string]$streetaddress = ""
    [string]$zipCode = ""
}

$inputdata = ".\Testdata.csv"

$iso8859_1 = [System.Text.Encoding]::GetEncoding('ISO-8859-1')

    $reader = New-Object -TypeName System.IO.StreamReader($inputdata, $iso8859_1)
    [int]$counter = 0

    while ($line = $reader.ReadLine() )
    {   

        if ($counter -gt 0)
        {
            $lines_split = $line.Split(';')
            CustomerData.$Id = $lines[0]
            $jsondata = ConvertFrom-Json $lines[1] 
        }
        $counter++;
    }
    $reader.Close()
powershell parsing json
2 Answers

Try to get some ideas from the following snippet:

$reader = [System.IO.StringReader]::new(@'
ID;CHANGES
713422;[{"key":"zipCode","updatedValue":";"},{"key":"language","updatedValue":"EN"},{"key":"coaddress","updatedValue":""},{"key":"SSN","updatedValue":""},{"key":"msisdn","updatedValue":"1114455789"}]
114365;[{"key":"city","previousValue":"New York","updatedValue":"Palm City"},{"key":"zipcode","previousValue":"100012","updatedValue":"02118"},{"key":"coaddress","updatedValue":""},{"key":"streetaddress","previousValue":"9253 Del Monte Road","updatedValue":"90 Kent Ave"},{"key":"SSN","updatedValue":""},{"key":"companyName","previousValue":"Nutrics","updatedValue":"NutriTiger"}]
114365;[{"key":"zipCode","updatedValue":""},{"key":"coaddress","updatedValue":""},{"key":"SSN","updatedValue":""},{"key":"companyName","previousValue":"NutriTiger","updatedValue":"Nutri-Tiger"}]
713422;[{"key":"zipCode","updatedValue":""},{"key":"coaddress","updatedValue":"Roady Road"},{"key":"SSN","updatedValue":""},{"key":"msisdn","updatedValue":""}]
'@)
class CustomerData
{
  [int]$Id = 0

  [string]$companyName = ""
  [string]$ssn = ""
  [string]$msisdn = ""
  [string]$language = ""

  [string]$city = ""
  [string]$coaddress = ""
  [string]$streetaddress = ""
  [string]$zipCode = ""
}
$props = [CustomerData].GetProperties() | ForEach-Object Name
$counter = 0
while ( $line = $reader.ReadLine() )
{
  if( $counter -gt 0 )
  {
    $cd = [CustomerData]::new()
    $cd.Id, $ch = $line.Split(';')
    $ch = $ch -join ';'
    $jsondata = ConvertFrom-Json $ch
    $jsondata | Where-Object { $props -contains $_.key } |
      ForEach-Object { $cd."$($_.key)" = $_.updatedValue }
    $cd
  }
  $counter++
}
$reader.Close()
user_33504280

Andrei Odegov's answer contains helpful improvements to your code.
As currently posted, the primary problem with your code is a variable-name confusion: You save the fields to array $lines_split, and then mistakenly access a different variable, $lines.

Generally, speaking, at least by default there is no need for something akin to TryParse(): you can simply access a property path, and if it doesn't exist, $null will be returned:

$bar = ('{ "foo": 1 }' | ConvertFrom-Json).bar  # $bar will be $null

If Set-StrictMode -Version 2 or higher is in effect, accessing a nonexistent property does cause a (statement-terminating) error; using try / catch is the simplest way to handle this, which also allows you to specify a default value:

$bar = try { ('{ "foo": 1 }' | ConvertFrom-Json).bar } catch { 0 }  # $bar will be 0

That said, your particular JSON input is structured in a way that the target property names are in JSON property values, which prevents direct access such as $obj.City, so you need to filter the objects.

However, instead of looking for specific values, consider iterating over them:

# Instantiate a new customer-data object.
$customer = [CustomerData]::new()

# Parse the JSON data in custom objects ([pscustomobject]).
# In this case, you'll get a single-element array containing an array
# of [pscustomobject] instances.
$jsondata = ConvertFrom-Json '[{"key":"city","previousValue":"New York","updatedValue":"Palm City"},{"key":"zipcode","previousValue":"100012","updatedValue":"02118"},{"key":"coaddress","updatedValue":""},{"key":"streetaddress","previousValue":"9253 Del Monte Road","updatedValue":"90 Kent Ave"},{"key":"SSN","updatedValue":""},{"key":"companyName","previousValue":"Nutrics","updatedValue":"NutriTiger"}]'


# Loop over all custom objects and update the corresponding
# customer-data properties.
foreach($obj in $jsonData) {
   $propName = $obj.key
   $customer.$propName = $obj.UpdatedValue
}
user_453750
Related