Parsing Nested Arrays using VBA and JSON -


i have json trying parse in vba. have parsed out array of "offers". inside array of "offers" array "prices""usd".

the problem not every "offers" object has "usd" array. trying create object can make table/sheet can't objects print in debug mode. works fails because not every dict offerdetails contains "usd" object.

what able print string , if "usd" object missing skip , print ones have "usd". have tried ismissing (in code) fails when hits missing "usd" object.

any idea how can string "usd" values? note "usd" array , contains several objects, don't know how address them either. ideally parse out "usd" same way did "offers". totally lost not in vba

this working script valid web json.

  sub getjsonep_lib_working()       'need jsonconverter found here https://github.com/vba-tools/vba-json       'need microsoft scripting runtime         dim parsed dictionary       dim item dictionary       dim offerdetails dictionary       dim price dictionary       dim usd dictionary                url = "http://wraymac.com/json/example1.json"               url2 = "[{" & """mpn""" & ":" & """41202""" & "}]"                set myrequest = createobject("winhttp.winhttprequest.5.1")               myrequest.open "get", url               myrequest.send                jsonstring = myrequest.responsetext                dim json object               set json = jsonconverter.parsejson(jsonstring)         set parsed = jsonconverter.parsejson(myrequest.responsetext)           each item in parsed("results")(1)("items")            each offerdetails in item("offers")         'i tried doesn't work, fails when finds non existent "usd"             if not ismissing(offerdetails("prices")("usd")(1)(1))             debug.print offerdetails("prices")("usd")(1)(1)             else             debug.print "missing"             end if         x = item("mpn") & "   " & "sku" & " - " & offerdetails("sku") & "," & "uid" & " - " & offerdetails("seller")("uid") & "   " & offerdetails("moq") & "packaging" & " = " & offerdetails("packaging") & "  " & offerdetails("seller")("name") & "  " & item("manufacturer")("name")       debug.print x        'this works fails because not every dict offerdetails contains "usd" object       'x = item("mpn") & "   " & "sku" & " - " & offerdetails("sku") & "," & "uid" & " - " & offerdetails("seller")("uid") & "   " & offerdetails("moq") & "packaging" & " = " & offerdetails("packaging") & "  " & offerdetails("seller")("name") & "  " & item("manufacturer")("name")& "  "&offerdetails("prices")("usd")(1)(1)         next offerdetails            next        end sub

you want use dictionary's exists method:

set parsed = jsonconverter.parsejson(myrequest.responsetext)  each item in parsed("results")(1)("items")     each offerdetails in item("offers")              if offerdetails("prices").exists("usd")                 debug.print offerdetails("prices")("usd").count & " items:"                 debug.print "-----------------"                 each x in offerdetails("prices")("usd")                     debug.print x(1), x(2)                 next x                 debug.print "-----------------"             else                 debug.print "no usd"             end if       next offerdetails  next

the converter parses objects ({}) dictionaries, , arrays ([]) collections, can use count determine number of items in each of types of object.


-

Comments

Post a Comment

Popular posts from this blog

java - SSE Emitter : Manage timeouts and complete() -

i writing web app multiple listeners(evcentsource sse clients js) connected server . want store sse emitter per connected listener : can done in memory or other means assigning id each client able achieve far now question ; how send response/event specific client connected web app ? while doing sseemmiters stored either getting completed or timed out. how prevent ? how keep sseemmiter available open infinite time (till client closes) , send events selectively .
Read more

jquery - uncaught exception: DataTables Editor - remote hosting of code not allowed -

i'm trying implement crud operation using datatableseditor using datatables.but i'm getting error messages: 1)uncaught exception: datatables editor - remote hosting of code not allowed. please see http://editor.datatables.net details on how purchase editor license 2)typeerror: a.editor undefined 3) $.fn.datatable.editor not constructor" what reason? here configuration: <%@ page language="java" contenttype="text/html; charset=iso-8859-1" pageencoding="iso-8859-1"%> <!doctype html public "-//w3c//dtd html 4.01 transitional//en" "http://www.w3.org/tr/html4/loose.dtd"> <html> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1"> <title>insert title here</title> <link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.12/css/jquery.datatables.min.css"> <link re...
Read more

java - How to resolve error - package com.squareup.okhttp3 doesn't exist? -

i trying convert json java object use of gson in maven, following youtube video guidance - https://www.youtube.com/watch?v=vqgghm9pwe0 , theres error occurs when in main class error - package com.squareup.okhttp3 doesn't exist. code below: java package com.codebeasty.json; import com.squareup.okhttp3.okhttpclient; public class main { private static okhttpclient client = new okhttpclient(); public static void main (string [] args) { } } i put in dependency in pom.xml: <dependencies> <dependency> <groupid>com.squareup.okhttp3</groupid> <artifactid>okhttp</artifactid> <version>3.4.2</version> </dependency> <dependency> <groupid>com.google.code.gson</groupid> <artifactid>gson</artifactid> <version>2.8.0</version> </dependency> </dependencies> i dont understand why doesn't recognize com.squareup. there may need download? have downl...
Read more