Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    seanst8579 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    14

    Help saving parsed JSON with indeterminate amount of data

    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:

    {"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}]}
    and here is the code as originally formulated:

    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
    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.

    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

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I was hoping somebody would respond, as I don't have much experience with JSON. That said, if you're already parsing that out okay, what does the csv field contain? If it's that comma delimited list, you can use the Split() function to create an array of that data, then within a loop of that array, add a record to a related table using the key from the main loop.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    seanst8579 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    14
    Hi Paul,

    Thanks for the reply. Before I got around to checking this thread I was attempting the following solution:

    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
    Next
    It saves 4/1 and 4/2 but not the others, so I assume I've messed up the loop. Any suggestions there?

    If this doesn't work out I'll see if I can pull off your suggestion.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Disclaimer: I have 0 experience with JSON... just what I have read.


    From Post #1,
    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
    This is not a normalized structure - there are repeating fields.


    This is (mostly) normalized:
    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]


    Since I don't know your table structures, I threw together some tables and modified the posted code.
    I don't have the API and don't know the parameters for the HTTP call, so I couldn't test the code.


    Look at the relationship window to see the tables/relationships. Most of the fields in the tables are text because I didn't know the field data types...
    Attached Files Attached Files

  5. #5
    seanst8579 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    14
    Quote Originally Posted by ssanfu View Post
    Disclaimer: I have 0 experience with JSON... just what I have read.


    From Post #1,
    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
    This is not a normalized structure - there are repeating fields.


    This is (mostly) normalized:
    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]


    Since I don't know your table structures, I threw together some tables and modified the posted code.
    I don't have the API and don't know the parameters for the HTTP call, so I couldn't test the code.


    Look at the relationship window to see the tables/relationships. Most of the fields in the tables are text because I didn't know the field data types...
    Thanks a ton for this! Ran it once and it returned an error because the inner csv loop was using the same control variable as the main one, but I dimmed a new variant for the array, ran it again and it spit out all the data.

    Really, really appreciate it.

    Sean

  6. #6
    seanst8579 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    14
    Might have actually spoken too soon. There's two bits of weirdness I hadn't noticed in my joy with the progress:

    1) I've now tested it with a bunch of different JSON data, and whichever I run through, it will only save 31 pairs of csv values. But there are actually more there; I can see them if I run it with a watch on item("csv").

    2) It only seems to work on csv 4. If I keep everything else the same and change item("csv")(4)(i) to (3)(i), it returns a subscript out of range error.

    Any suggestions?

    Again, thanks for the help!

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Thanks a ton for this! Ran it once and it returned an error because the inner csv loop was using the same control variable as the main one, but I dimmed a new variant for the array, ran it again and it spit out all the data.
    Sorry about that.... I had no way to test the code....


    OK, remembering my disclaimer (Disclaimer: I have 0 experience with JSON!!), this is what I think is happening.

    I used your example JSON in Post #1. I found a free JSON validator and a free JSON Formatter. I was finally able to get the JSON validated and formatted. See the attached text file.........

    The key "Products" is an array and within "Products" is the key "CSV" array. For ease of viewing, I deleted the data for the "CSV" array, to show the 28 items in JSON("Products"):
    Code:
    {
       "timestamp": 1502816153960,
       "tokensLeft": 59,
       "refillIn": 5065,
       "refillRate": 1,
       "tokenFlowReduction": 0,
       "products": [
          {
             "csv": [items removed],      << this is the 1st item in Products
                null,              << this is the 2nd item in Products
                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               << this is the 29th item in Products
             ],
             "categories": [1000],
             "imagesCSV": "61XCk-U59EL.jpg",
             <snip>
    So this code is looping through "Products"
    Code:
            With rsCSV
                For Each Pitem In JSON("products")
                    .AddNew
                    !NodeID_FK = AN
                    !DateTime = item("csv")(4)(i)
                    !StampRank_DT = "(4) (" & i & ")"
                    !AZSalesRank = item("csv")(4)(i + i)
                    !StampRank_Rank = "(4) (" & (i + 1) & ")"
                    .Update
                    i = i + 2
                Next
            End With
    BUT the code needs to loop through item("CSV")... so the looping code for "CSV" needs to be changed.
    Somehow need to read nested arrays....( the examples I have foound have been in something like C or C++.

    I don't know (& haven't found) the syntax to access "CSV" items. I would think it would be something like
    Code:
    For Each Pitem In JSON("Products.CSV")
    but that is a total guess......


    It only seems to work on csv 4. If I keep everything else the same and change item("csv")(4)(i) to (3)(i), it returns a subscript out of range error.
    Now looking at the array "CSV", you will see
    Code:
       "products": [
          {
             "csv": [                                                                            
                [3471336,-1],                  << this is item("CSV") (1) with item("CSV") (1)(1) = 3471336 and  item("CSV") (1)(2) = -1
                [3471336,3795],               << this is item("CSV") (2) with item("CSV") (2)(1) = 3471336 and  item("CSV") (2)(2) = 3795
                [3471336,3695],               << this is item("CSV") (3) with item("CSV") (3)(1) = 3471336 and  item("CSV") (3)(2) = 3695
                [  3471336,               30974,  << this is item("CSV") (4) with item("CSV") (4)(1) = 3471336 and  item("CSV") (4)(2) = 30974
                   3471360,               19836,  << this is item("CSV") (4) with item("CSV") (4)(3) = 3471360 and  item("CSV") (4)(4) = 19836
                   3471488,               22655,  << this is item("CSV") (4) with item("CSV") (4)(5) = 3471488 and  item("CSV") (4)(6) = 22655
                   3471594,               22861,
                   3471696,               26073,
                   3471796,               15827,
                   3471896,               18927,
                   3471996,               17856,
                   3472096,               18901,
                   3472196,               17880,
                   3472296,               20382,
                   3472496,               22045,
                   3472644,               23059,
                   3472746,               23868,
                   3472838,               21469,
                   3472946,               19273,
                   3473046,               20197,
                   3473146,               22634,
                   3473246,               27909,
                   3473346,               24571,
                   3473446,               22214,
                   3473546,               26630,
                   3473646,               28768,
                   3473746,               31896,
                   3473846,               32530,
                   3473946,               33268,
                   3474046,               33230,
                   3474124,               33739,
                   3474196,               34410,
                   3474296,               38680,
                   3474398,               41748,
                   3474496,               36000,
                   3474596,               35161,
                   3474796,               44106 ],  << this is item("CSV") (4) with item("CSV") (4)(67) = 3474796 and  item("CSV") (4)(68) = 44106
                null,                                                                                   
                null,

    OK, it is late and it is time for .... , ..then .....
    Attached Files Attached Files

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  9. #9
    seanst8579 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    14
    Quote Originally Posted by orange View Post
    Sean,
    This is cross posted https://access-programmers.co.uk/for...d.php?t=295297

    When you cross post, identify the other (post(s)/link(s)) to users.

    Here is why.
    Apologies orange!

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe try this??? (this is a WAG!)
    replace the loop in the code with
    Code:
            i = 1
            'Elements Under Prodcut-csv Array.
            With rsCSV
                '   For Each Pitem In JSON("products")
                For Each Pitem In item("csv")
                    .AddNew
                    !NodeID_FK = AN
                    !DateTime = item("csv")(4)(i)
                    !StampRank_DT = "(4) (" & i & ")"
                    !AZSalesRank = item("csv")(4)(i + i)
                    !StampRank_Rank = "(4) (" & (i + 1) & ")"
                    .Update
                    i = i + 2
                Next
            End With

  11. #11
    seanst8579 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    14
    So after some trial and error I was able to get it working. First I added

    Code:
    Dim subitem As Variant
    Dim subsubitem4 As Variant
    Dim j As Integer
    and put this inside the main loop

    Code:
    For Each subitem In item("csv")
                    If Not IsNull(item("csv")(4)) Then
                    i = 1
                    j = 1
                        For Each subsubitem4 In item("csv")(4)
                            If i + j <= item("csv")(4).Count Then
                                With rsCSV
                                    If IsNull(DLookup("CSV!DateTime", "CSV", item("csv")(4)(i))) Then
                                    .AddNew
                                    !ProductID_PK = AP
                                    !DateTime = item("csv")(4)(i)
                                    !ItemRank = item("csv")(4)(i + j)
                                    .Update
                                    End If
                                i = i + 2
                                End With
                            End If
                        Next
                    End If
                 Next
    To get all the data -- from csv 1 through 31 -- I have to repeat everything beginning with the first If. It strikes me a pretty inelegant, and I can't help but think there is a better way of doing it, but at least it works.

  12. #12
    seanst8579 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    14
    Hi Steve,

    That was my first guess too. When I tried it, I noticed that even though it was now saving multiple records, it was only ever saving 31 of them, no matter how much data was actually in csv (4). Then I realized why; it was iterating over csv (1) through (31), not through the values in csv (4). If you do another loop inside that one, it works!

    ETA: A question about normalization best practices. All the different csv's record different kinds of data. Csv 4 is a time stamp and sales rank, csv 1 is a time stamp and price, etc. I should be creating a different table for all of them, yeah?

    Also, as you can see from the code above, I added an If statement to prevent it from duplicating data. I did the same thing for all the stuff in ProductNode. I then tied all the csv records not to the autonumber in NodeFields, but to the autonumber in ProductNode (since the data are unique to a particular product). The problem is that, when working with a JSON whose product is already in the database but which has csv data that isn't, it isn't making a new autonumber to pass to the csv table and the new record there; instead I need to get the existing ProductNodeID_PY value for the product and put that in the new csv record.

    I've never been good with DLookup syntax. Something like: DLookup("ProductNode!ProductID_PK", "ProductNode", "ProductNode!asin =" item("asin")), i.e., the criteria should be that the value of "asin" in the JSON matches the value of in the asin field in ProductNode. But that syntax is wrong, and so is every other i've tried.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Code:
    DLookup("ProductNode!ProductID_PK", "ProductNode", "ProductNode!asin =" item("asin"))
    Problems:
    1) Don't use the table name in the expression clause or the criteria
    expr is the field data you want to return (ProductID_PK)
    Domain is the table to look in (ProductNode)
    Criteria is the field to match'limit the records (asin)

    2) You need to concatenate the value of item("asin") (using the "&" characters)
    3) If "asin" is a text type field, you need to use delimiters

    So try:
    Code:
    DLookup("ProductID_PK", "ProductNode", "asin = '" & item("asin") & "'")

  14. #14
    ksharpes is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    1
    Did you manage to get this sorted in the end?

    I need something very similar and you could potentially be a life saver!


    Quote Originally Posted by seanst8579 View Post
    Hi Steve,

    That was my first guess too. When I tried it, I noticed that even though it was now saving multiple records, it was only ever saving 31 of them, no matter how much data was actually in csv (4). Then I realized why; it was iterating over csv (1) through (31), not through the values in csv (4). If you do another loop inside that one, it works!

    ETA: A question about normalization best practices. All the different csv's record different kinds of data. Csv 4 is a time stamp and sales rank, csv 1 is a time stamp and price, etc. I should be creating a different table for all of them, yeah?

    Also, as you can see from the code above, I added an If statement to prevent it from duplicating data. I did the same thing for all the stuff in ProductNode. I then tied all the csv records not to the autonumber in NodeFields, but to the autonumber in ProductNode (since the data are unique to a particular product). The problem is that, when working with a JSON whose product is already in the database but which has csv data that isn't, it isn't making a new autonumber to pass to the csv table and the new record there; instead I need to get the existing ProductNodeID_PY value for the product and put that in the new csv record.

    I've never been good with DLookup syntax. Something like: DLookup("ProductNode!ProductID_PK", "ProductNode", "ProductNode!asin =" item("asin")), i.e., the criteria should be that the value of "asin" in the JSON matches the value of in the asin field in ProductNode. But that syntax is wrong, and so is every other i've tried.

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ksharpes,

    I suggest you make a new thread for your specific problem.
    You can reference(add a link to this thread) and indicate the similarity

    I also suggest you tell us the specifics of your issue with an overview description of what you are dealing with in plain English. Then more detail about what you want to achieve and how you intend to proceed. The more clarity in your request, the more focused will be responses/advice.

    Good luck.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Importing/Reading JSON to Populate Fields
    By wrayman in forum Import/Export Data
    Replies: 22
    Last Post: 09-17-2018, 10:28 AM
  2. Dealing with json
    By control1 in forum Access
    Replies: 24
    Last Post: 06-07-2017, 02:08 AM
  3. Replies: 1
    Last Post: 06-24-2016, 02:34 PM
  4. System DSN (MS Access) to REST API / JSON
    By Hitesh in forum Queries
    Replies: 1
    Last Post: 03-15-2016, 11:37 AM
  5. Replies: 12
    Last Post: 07-18-2014, 01:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums