VBA Neophyte: How to Save Parsed JSON with indeterminate number of array values (1 Viewer)

[seanst8579]

Hey everyone,

By way of brief introduction, the past day or so has been my first real introduction to VBA (save adding functionality to Access forms here and there). My place of employment really needs the ability to query an API, parse the resulting JSON and pass it all to an Access table for later analysis, but because I'm doubtful the folks assigned to it can get something up and running in a timely fashion, I decided to try my hand at it in my spare time.

With the help of Google and VBA-JSON I'm almost there, but there are a few issues no search engine can seem to resolve. First, let me show you where I ended up:

Public Sub JSONTest()

Dim http As Object, JSON As Object, item As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set http = CreateObject("MSXML2.XMLHTTP")

http.Open "GET", "[place filler URL]", False
http.send

Set db = CurrentDb
Set rs = db.OpenRecordset("tblTest", dbOpenDynaset, dbSeeChanges)

Set JSON = JsonConverter.ParseJson(http.responseText)
For Each item In JSON("products")
rs.AddNew

'Main JSON Node Fields
rs!timestamp = JSON("timestamp")
rs!refillIn = JSON("refillIn")
rs!refillRate = JSON("refillRate")
rs!tokenFlowReduction = JSON("tokenFlowReduction")
rs!tokensLeft = JSON("tokensLeft")

'Product Node Fields
rs!imagesCSV = item("imagesCSV")
rs!hasReviews = item("hasReviews")
rs!ASIN = item("asin")
rs!Title = item("title")
rs!manufacturer = item("manufacturer")
rs!domainId = item("domainId")
rs!trackingSince = item("trackingSince")
rs!lastUpdate = item("lastUpdate")
rs!lastRatingUpdate = item("lastRatingUpdate")
rs!lastPriceChange = item("lastPriceChange")
rs!rootCategory = item("rootCategory")
rs!parentAsin = item("parentAsin")
rs!upc = item("upc")
rs!ean = item("ean")
rs!mpn = item("mpn")
rs!Type = item("type")
rs!Label = item("label")
rs!department = item("department")

'Elements Under csv array
rs![AZSalesRank] = item("csv")(4)(2)
rs![DateTime] = item("csv")(4)(1)

rs.Update
Next
End Sub

Until we get to the stuff in the csv array, everything works exactly as it should; it assembles and sends off the URL, gets a response, uses VBA-JSON to parse it (code not shown, but as I understand it -- again, complete newbie here -- organizes everything into a collection), and then saves all the higher-level items to the test table.

There are really two problems with the csv data: first, we don't know ahead of time just how much there is. Here, for example, is part of a sample raw JSON:

{"timestamp":1502816153960,"tokensLeft":59,"refillIn":5065,"refillRate":1,"tokenFlowReduction":0.0,"products":[{"csv":[[3471336,-1],[3471336,3795],[3471336,3695],[3471336,30974,3471360,19836,3471488,22655 [ and so on, and so on, and so on]

All the top-level stuff only returns one value, so it's easy to assign it to a particular field in the Access table. Csv (4), which consists of a time stamp (csv (4)(1)) and a sales metric (csv(4)(2)), however, can have hundreds (time stamp at (4)(3), sales metric at (4)(4), time stamp at (4)(5), metric at (4)(6), etc., etc.), so it's completely impractical to get it to the table in the same way. Second, even if I were to create a new field for each possible number of resulting values, it would be organized in an unhelpful way; we need all the time stamps in a single field and all the sales data in another, i.e., to create a unique record every time it has a time stamp that isn't already in the table.

For the life of me, I can't see to get it to do that. My first thought was to do something like this:

Dim i As Integer
i = 1
For Each item In JSON("products")
rs.AddNew
rs!DateTime = item("csv")(4)(i)
rs!AZSalesRank = item("csv")(4)(i + i)
rs.Update
i = i + 2
Next

It got the values of csv(4)(1) and (4)(2) right and put them in the correct fields -- not a total failure! -- but only those two, and only one record. I'm sure I'm making some elementary mistake concerning loops or the syntax of collections, but then, I'm not even sure if this is the best way to go about this.

Any help you could offer would really be appreciated.

Thanks!

Sean (frustrated non-programmer)

 

[Cronk]

Disclaimer: I have not as yet had to work with JSON files, though I have done some XML node parsing using VBA.

Seems to me like your csv data is an array of arrays. So my first reaction is that you would need to have a loop embedded in an outer loop.

However, there is a current thread on "Using/parsing JSON with vba" which might be of use to you. There is a search facility near the top of the forum page.

 

[isladogs]

Hi

As a self proclaimed neophyte you've certainly chosen a difficult challenge to solve ....

The thread that Cronk mentioned is here:https://www.access-programmers.co.uk/forums/showthread.php?t=294906

A small group of us have been looking into ways of importing JSON files into Access. There are a lot of issues in doing so including:

1. There is no Access import wizard for JSON. The XML wizard won't do the job

2. JSON file structures vary enormously. A solution for one file won't work for a different JSON file from another source.

3. In many cases, the file needs to be parsed into several tables to ensure the result is properly normalised.
Your JSON file appears to be a very good example of this

The code you've picked up from the VBA-JSON website is a very good start.
If you really are new to VBA, you've done very well to get this far

As you say, solving the parsing of the arrays & sub-arrays looks horribly complicated.
I have just successfully parsed a series of JSON files with all postal addresses for selected postcodes.
It took me a while & the files were far simpler than yours.
For info, I will be uploading this to the Sample Databases area in a day or two.

However, I wouldn't know how to suggest you complete your complex parsing task in Access

I suggest you try a different approach - import first into Excel.
If you have Excel 2010/2013, you need to download an add-in called PowerQuery. Access 2016 has an updated version called Get & Transform built in.

These are both wizard based & allow you to experiment until you get it right
You might also find it useful to use an online JSON to CSV converter as this will help you visualise the data structure before starting to parse it.

I have been using the Excel based approach for a few weeks and it works well once you get used to it.
I have started preparing a series of You Tube videos explaining how to use Excel PowerQuery to parse JSON files.
They aren't yet finished but if you are interested, send me a private message & I'll provide links to draft versions of the first two videos

 

[jdraw]

Just to say that I agree with Ridders. His point #3 is a very critical part of the JSON to Access import/conversion process.
It is the different and varying structures that JSON allows that make any simplified import/conversion quite complex (to say the least).
The vba json parsers I was able to find seemed to require customization for each json file. There was not a general approach that could be used without knowing the json structure in advance. It seemed to me that a vba based json parser would be a great solution for processing a consistent json file format (such as a specific response from a specific webservice) --but most developers do not have a 1 supplier response format issue.

Colin(Ridders) found the Power Query addin for Excel and it seems a practical approach and is using a M$oft software. You still need to know something of the json file, but converting to tables (normalized structure) seems very promising. I'm looking forward to his videos and samples.

Good luck with your project. It may just be the one to confirm the usefulness and versatility of the Power Query intermediate step when processing json with Access.

 

[seanst8579]

Hi guys,

Thanks for the replies. VBA-JSON seems to be doing its job, at least in my case; if I run it with a watch on item("csv"), all the values are there.

Also, by adding this nested loop, as Cronk suggested:

        i = 1
        j = 1
        For Each subitem In item("csv")
            With rs
                .AddNew
                !DateTime = item("csv")(4)(i)
                !AzSalesRank = item("csv")(4)(i + j)
                .Update
                i = i + 2
            End With
        Next

where subitem was declared as a variant, I was able to get it to save more than just csv 4/1 and 4/2. Two puzzling hiccups, however: I've tested this with a bunch of different JSONs, and no matter how many items csv 4 actually has, it will only save 31 pairs.

The other thing is that it only seems to work with csv 4. If I keep everything else the same and change "4" to "3", I get a subscript out of range error.

Any clue as to what might be going on?