Iterating Through JSON file with objects missing tags

PowerShell Help
1

Hi .

I’m trying to make a powershell script that iterates through a json file and populates a mysql databse. Unfortunately , some of the objects in the json file are missing attributes that other objects have (Ex: ip address) so the end result in the mysql table ends up being misaligned .

My code:

$json  = Get-Content -Raw -Path C:\Users\test\Desktop\API\TOTALPRINTERS.json
$jsonserial = New-Object -TypeName System.Web.Script.Serialization.JavaScriptSerializer
$jsonserial.MaxJsonLength = [int]::MaxValue
$Obj = $jsonserial.DeserializeObject($json)
$Totalprinters= $obj.cis.properties.global_id.count   #Total printers count 
$limit = $obj.cis.properties.global_id.count
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$ConnectionString = "server=187.15.43.434;uid=test;pwd=CM123$;database=test;pooling=false"
$Connection.ConnectionString = $ConnectionString
$Connection.Open() 
$datasend = For ($i = 0; $i -lt $limit; $i++) 
{
    $nameofprinter = $obj.cis.properties.name[$i]
    $ipofprinter = $obj.cis.properties.primary_ip_address[$i]
    $manufacturerofprinter=$obj.cis.properties.discovered_vendor[$i]
    $Query = "INSERT INTO printers (name,ip,manufacturer, Divison) VALUES ('$nameofprinter','$ipofprinter','$manufacturerofprinter',' ')" 
    $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
    $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
    $DataSet = New-Object System.Data.DataSet
    $RecordCount = $dataAdapter.Fill($dataSet, "data") 
}

JSON File:

{
    "cis": [ 
        {
            "udbId" : "40000c5e6b84af5588aa066a1569b17e",
            "globalId" : "40000c5e6b84af5588aa066a1569b17e",
            "type" : "netprinter",
            "properties" : {
                "discovered_vendor" : "Fuji Xerox",
                "name" : "xrx9c9344544e19ffa5",
                "global_id" : "40000c5e6b84af5588aa066a1569b17e",
                "primary_ip_address" : "182.19.323.23"
            }
        }, 
        {
            "udbId" : "400050e64747c97b80a41358b7810c29",
            "globalId" : "400050e64747c97b80a41358b7810c29",
            "type" : "netprinter",
            "properties" : {
                "discovered_vendor" : "Fuji Xerox",
                "name" : "phaser 4510454n",
                "global_id" : "400050e64747c97b80a41358b7810c29",
                "primary_ip_address" : "182.19.323.21"
            }
        }, 
        {
            "udbId" : "4ffc72d7151c94ae850837effb45f3c2",
            "globalId" : "4ffc72d7151c94ae850837effb45f3c2",
            "type" : "netprinter",
            "properties" : {
                "discovered_vendor" : "Fuji Xerox",
                "name" : "espl0045452",
                "global_id" : "4ffc72d7151c94ae850837effb45f3c2",
            }
        } 
    ],
    "relations" : null
}

 

 

 

2

Hello kod9995,

I have formatted your post for better visibility, and next time when you post, please use PRE tags.

And please DO NOT post any sensitive information in the post.

Coming to your query, please check the value before formating the query (you can use if statement)

Thank you.