seanst8579
New member
- Local time
- Today, 17:38
- Joined
- Aug 16, 2017
- Messages
- 2
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:
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:
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:
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)
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:
Code:
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 SubUntil 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:
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:
Code:
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
NextIt 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)
