Hey guys,
I have a module that queries an API, uses VBA-JSON to parse the response, and then saves it to a table. The problem I'm having is that I'm not sure how to get it to the table in exactly the format I need. Here's an example JSON:
and here is the code as originally formulated:{"timestamp":1502816153960,"tokensLeft":59,"refill In":5065,"refillRate":1,"tokenFlowReduction":0.0," products":[{"csv":[[3471336,-1],[3471336,3795],[3471336,3695],[3471336,30974,3471360,19836,3471488,22655,3471594, 22861,3471696,26073,3471796,15827,3471896,18927,34 71996,17856,3472096,18901,3472196,17880,3472296,20 382,3472496,22045,3472644,23059,3472746,23868,3472 838,21469,3472946,19273,3473046,20197,3473146,2263 4,3473246,27909,3473346,24571,3473446,22214,347354 6,26630,3473646,28768,3473746,31896,3473846,32530, 3473946,33268,3474046,33230,3474124,33739,3474196, 34410,3474296,38680,3474398,41748,3474496,36000,34 74596,35161,3474796,44106],null,null,null,null,null,null,null,[3471336,1],[3471336,1],null,null,null,null,null,null,null,null,null,null ,null,null,null,null,null,null,null,null],"categories":[1000],"imagesCSV":"61XCk-U59EL.jpg","manufacturer":"America's Test Kitchen","title":"The Complete Mediterranean Cookbook: 500 Vibrant, Kitchen-Tested Recipes for Living and Eating Well Every Day","lastUpdate":3482884,"lastPriceChange":347133 6,"rootCategory":283155,"productType":0,"parentAsi n":null,"variationCSV":null,"asin":"1974801691","d omainId":1,"type":"BOOKS_1973_AND_LATER","hasRevie ws":true,"ean":9781974801695,"upc":0,"mpn":null,"t rackingSince":3471336,"brand":null,"label":"Americ a's Test Kitchen","department":null,"publisher":"America's Test Kitchen","productGroup":"Book","partNumber":null," studio":"America's Test Kitchen","genre":null,"model":null,"color":null,"s ize":null,"edition":"rebound version /","platform":null,"format":null,"packageHeight":25 ,"packageLength":251,"packageWidth":216,"packageWe ight":1270,"packageQuantity":-1,"isAdultProduct":false,"isEligibleForTradeIn":fa lse,"isEligibleForSuperSaverShipping":false,"offer s":null,"buyBoxSellerIdHistory":null,"isRedirectAS IN":false,"isSNS":false,"author":"The Editors at America's Test Kitchen","binding":"Spiral-bound","numberOfItems":-1,"numberOfPages":440,"publicationDate":20161207," releaseDate":-1,"languages":[["English","Unknown"]],"lastRatingUpdate":3480824,"eanList":["9781974801695"],"upcList":null,"liveOffersOrder":[0,1],"stats":null,"offersSuccessful":false,"g":126}]}
Everything in the main and product arrays does exactly what it should, but not the csv stuff. As you can see above, I had originally set it up in exactly the same way as the top-level data but quickly discovered that just won't do; first, because for every ASIN/ISBN the API will return an indeterminate number of csv items (depending on how long they've been keeping data) I can't possibly specify unique fields for and, second, because that would be a bad way to organize the data anyway.Code:Public Sub getJSON2() 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", "https://api.keepa.com/product?key=" & APIKey & "&domain=" & Region & ASINorISBN & ASIN & Update & LiveData & Exclude, False http.send Set db = CurrentDb Set rs = db.OpenRecordset("Keepa", 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 Prodcut-csv Array. rs!AZPriceNew = item("csv")(1)(2) rs!MPPriceNew = item("csv")(2)(2) rs!MPPriceUsed = item("csv")(3)(2) rs!MPNewOffers = item("csv")(12)(2) rs![AZSalesRank] = item("csv")(4)(2) rs![DateTime] = item("csv")(2)(1) rs.Update Next End Sub
Take csv 4 in the JSON above (data begins with 3471336,30974,3471360,19836). It's made up of sets of two components, a time stamp (csv 4/1) and an Amazon sales rank (csv 4/2), stamp (csv 4/3) and rank (4/4), stamp (4/5) and rank (4/6), and so on. What I'd like to do is have a single date and time field ("DateTime" above), a single sales rank field ("AZSalesRank" above) and create new records for each unique stamp/rank pair. So rather than this, which is how it would end up working as is (values in brackets = corresponding part of JSON):
ID ASIN DateTime [csv 4/1] AZSalesRank [csv 4/2] DateTime2 [csv 4/3] AZSalesRank2 [csv 4/4] DateTime3 [csv 4/5] AZSalesRank3 [csv 4/6] 1 1974801691 3471336 30974 3471360 19836 3471488 22655
I'd like it to look something like:
ID ASIN DateTime AZSalesRank 1 1974801691 3471336 [csv 4/1] 30974 [csv 4/2] 2 1974801691 3471360 [csv 4/3] 19836 [csv 4/4] 3 1974801691 3471488 [csv 4/5] 22655 [csv 4/6]
Any help/pointers to resources would be greatly appreciated. For the record, I'm very, very new to VBA; save for some very simple functionality in forms, this is the first time I've really ever used it, so please be gentle!
Thanks,
Sean