+ Reply to Thread
Results 1 to 4 of 4

Parse / flatten nested JSON string in VBA

  1. #1
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Parse / flatten nested JSON string in VBA

    Hi all,

    I've been stumped on this for a while now and don't really know any good solution.
    I'm parsing JSON in VBA via Jscript (the simple Set JSON = Script.Eval("(" + http.responsetext + ")") method) - which puts the http json response into an object). This works fine - IF you're working with a relatively flat JSON reply.

    When I encounter a very nested / leveled JSON reply, I don't really know how to go around it - see below for a screenshot of what my 'parsed' JSON would look like in the locals window.
    json.jpg

    Now I know some of you might suggest me to use VBA-JSON, but that would still require me to know the structure and key names - whereas I just want it to be parsed without knowing that information.
    I wrote/tried the code below, and while it almost does what I want, it messes up the columns where it puts stuff.

    Please Login or Register  to view this content.
    This uses a global script control and 're' integer to determine whether to add a column or not.
    While this -almost- works, I believe this is not the smartest way to do it, so would love any pointers.
    Thanks,

    Jasper

  2. #2
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Parse / flatten nested JSON string in VBA

    I see I am not alone in not knowing a solution to this
    Bumping it up to hopefully spot Kyle123 eating christmas treats in a corner, while parsing some JSON through VBA


    Would it in theory be possible to add javascript that translates json into a csv into VBA?
    For example - this one: https://github.com/zemirco/json2csv

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,198

    Re: Parse / flatten nested JSON string in VBA

    Hi Jasper, what would the output look like? This is hierarchical so I'm not really sure how you'd want it displayed in a grid.

    Any change you could post the actual json?

    Is this an elastic search result?

  4. #4
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Parse / flatten nested JSON string in VBA

    Hi Kyle,

    thanks for your reply! It is company confidential, but I'll try to obfuscate the sensitive data and send to you over mail. Bear with me.
    Thanks and merry christmas!

    Jasper

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Convert Object to JSON String
    By amartinez988 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 12-01-2016, 03:41 PM
  2. Is there a native way to encode an object as a JSON string?
    By XmisterIS in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-01-2014, 05:32 AM
  3. Parse String and Fill list box with Each Found Sub String
    By bdb1974 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2011, 05:56 PM
  4. How to Parse a String?
    By KroogerFx in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-14-2009, 11:07 AM
  5. Parse string
    By loopoo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2006, 05:14 AM
  6. Q: parse string
    By JIM.H. in forum Excel General
    Replies: 3
    Last Post: 10-21-2005, 09:05 PM
  7. Parse String
    By Geoff Murley in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-02-2005, 10:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1