Iterating Through JSON file with objects missing tags

Welcome Forums General PowerShell Q&A Iterating Through JSON file with objects missing tags

Viewing 1 reply thread
  • Author
    Posts
    • #230791
      Participant
      Topics: 1
      Replies: 0
      Points: 11
      Rank: Member

      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
      }

       

       

       

    • #230839
      Moderator
      Topics: 3
      Replies: 223
      Points: 1,221
      Helping Hand
      Rank: Community Hero

      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.

Viewing 1 reply thread
  • You must be logged in to reply to this topic.