Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46

    Importing/Reading JSON to Populate Fields

    Using Access 2016 (64 and 32)

    Recently had the need to read a JSON file to populate a table

    Seems straight forward but keep hitting walls.

    I came across this example but can't even get the code to run.

    Yes I know it using the XMLHTTP but that is only for the GET, however, it fails with "Compile Error: Can't Find Project or Library"

    Don't know if this will even work, but I thought I would try and dissect it to see.

    Anyone else using JSON? Is there a better way, pretty much a noob when it comes to this. However the JSON I am getting is very complex and has several nested objects.

    I must be missing something.

    Public Sub ReadXML()
    Dim reader As New XMLHTTP60
    Dim doc As DOMDocument60

    reader.Open "GET", "http://localhost:55834/api/contacts", False
    reader.setRequestHeader "Accept", "application/xml"
    reader.send

    Do Until reader.ReadyState = 4
    DoEvents
    Loop

    If reader.Status = 200 Then
    Set doc = reader.responseXML
    doc.Save "c:\Northwind\Contacts.xml"



    Application.ImportXML "c:\Northwind\Contacts.xml", acStructureAndData

    Else
    MsgBox "Unable to import data."
    End If


    End Sub

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Some api's have an XML or JSON option.
    I have also seen references to a JSONConverter for vba --but have not seen a working example.
    It looks like you are expecting xml - you might heck with the website from which you are getting data and see if JSON or XML is available as response format.

    It might be better for readers if you could post some of the data you are receiving.

  3. #3
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46
    Yeah, same here on the converters, working on another one that just fails in the middle of the script, Maybe the person posting the example finally got it to work for them, but the code posted doesn't .

    No, not expecting XML and can't get XML only JSON, the first Post is an example from ?Steve Bishop?, has online vids, saw him create it and it worked. He was Just using the XMLHTTP60 to read the JSON, there is another module that does the converting. I can't get past the first step which is the example code I posted.

    The JSON Data is in PP below.

    Attached the JSON file: example.txt
    Last edited by wrayman; 10-28-2016 at 10:05 AM. Reason: too many attachements

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Yes I am familiar with Steve Bishop's stuff. He references a JSON converter by someone, but I think it is no longer available.
    Steve's material is very well done. often recommend his programming videos to posters.

  5. #5
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46
    Yes very well done, but I can't get past this error to even try the rest of the script.

    You would think with JSON being so prevalent that ACCESS would have some built-in function to handle it. Microsoft is married to XML and I guess they aren't going to acknowledge another format. Excel does have the JSON built in however, it can't handle complex JSON nesting like the JSON I am working with. Surely there is a solution to this problem.

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

  7. #7
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46
    Here are the two database examples containing the code

    36 is for the XML
    37 is for the JSON

    Neither example will run for me in Access 2016 (32 or 64 bit) I do know that he was using 2013.

    Maybe someone could give it a whirl and tell me how I goofed up again.

    Corresponding Videos:
    36. (Advanced Programming In Access 2013) Importing XML To A Table From A RESTful Web API: https://youtu.be/k8TXif1j82Q
    37. (Advanced Programming In Access 2013) Importing JSON To A Table From A RESTful Web API https://youtu.be/5qpUFV5Gtb0

    bishopexample.zip

  8. #8
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46
    The Library should be in the examples

  9. #9
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I found the source previously. I'm trying to get the JSONParser towork with your file.
    In his case he had a contact with a few fields. Your file seems quite complex. I'm trying to figure out what I can print/save from the parsing to show that I'm getting something.
    Don't know enough detail of what I'm trying to output.
    Open to ideas.

    I do have your file loaded and I can assign it to a string, and can try to parse that string (which simulates the JSON response text).

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

    I have got the jsonparser to take a simple json file and produce some output.

    Here is the file and the output (very simple stuff so far).

    Simple JSON file
    Code:
    [
        {
            color: "red",
            value: "#f00"
        },
        {
            color: "green",
            value: "#0f0"
        },
        {
            color: "blue",
            value: "#00f"
        },
        {
            color: "cyan",
            value: "#0ff"
        },
        {
            color: "magenta",
            value: "#f0f"
        },
        {
            color: "yellow",
            value: "#ff0"
        },
        {
            color: "black",
            value: "#000"
        }
    ]
    Output
    Code:
    red #f00
    green #0f0
    blue #00f
    cyan #0ff
    magenta #f0f
    yellow #ff0
    black #000

  12. #12
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46
    great, are you just using the JSON (Lib's)?

    If you can post the db with code, see if it runs here.

    Thanks

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm using the clsJSONParser that Steve used.
    I don't know what exactly I should/could be outputting from your file.
    I found a simple json file and used the JsonParser on it.
    I used his READJSON subroutine and commented out much of the code since I was not sending nor reading from a URL. I was not using the Contact table that he was working with.

    Here is my vba
    Code:
    Option Compare Database
    Option Explicit
    
    'Attempting to use info from Steve Bishop clsJsonParser
    ' with sample file from  https://www.accessforums.net/showthread.php?t=62671
    
    'The key here is I have a file called ExampleJSON.txt which is a valid json response
    'in my Documents folder
    'I will see if I can start with the file and use the clsJSONParser to parse that file.
    
    Public Sub ReadJSON()
          'Dim reader As New XMLHTTP60
              Dim coll As Collection
              Dim json As New clsJSONParser
              Dim db As DAO.Database
              Dim rs As DAO.Recordset
              Dim contact As Variant
              Dim MyJSONFile As String
              ' MyJSONFile = "C:\users\mellon\documents\exampleJSON.txt"
    10        MyJSONFile = "C:\users\mellon\documents\colorJSON.txt"
              Dim fso As FileSystemObject, JSONIn As TextStream
              '====
              Const ForReading = 1, ForWriting = 2, ForAppending = 3
    20        Set fso = CreateObject("Scripting.FileSystemObject")
    30        Set JSONIn = fso.OpenTextFile(MyJSONFile, ForReading)
              Dim x As String
    
    40        x = JSONIn.ReadAll
              '=====
    50        Debug.Print x
              'I'm not reading or sending to a URL so these are not needed
              'reader.Open "GET", "http://localhost:55834/api/contacts", False
              'reader.setRequestHeader "Accept", "application/json"
              'reader.send
    
              'Do Until reader.ReadyState = 4
              '    DoEvents
              'Loop
    
              ' If reader.Status = 200 Then
    
    
    60        Set db = CurrentDb
              '   Set rs = db.OpenRecordset("Contact", dbOpenDynaset, dbSeeChanges)
              '   Set coll = json.parse(reader.responseText)
    70        Set coll = json.parse(x)
    80        For Each contact In coll
    
    90            Debug.Print contact.item("color") & " " & contact.item("value") 'print items in coll
                  '            rs.AddNew
                  '            rs!id = contact.item("Id")
                  '            rs!FirstName = contact.item("FirstName")
                  '            rs!MiddleName = contact.item("MiddleName")
                  '            rs!LastName = contact.item("LastName")
                  '            rs!Email = contact.item("Email")
                  '            rs.Update
    100       Next
    
              '   Else
              '       MsgBox "Unable to import data."
              '   End If
    End Sub
    Here is the json file I used and the source code for the clsJSONParser from Steve.
    Attached Files Attached Files

  14. #14
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46
    Off topic but I am trying to enter this string in script of course I get errors I have tried just about everything ,editor doesn't like double double " any quick suggestion?

    MyRequest.Open "GET", "https:/api/v3/parts/match?apikey=EXAMPLE_KEY&queries=[{"mpn":"SN74S74N"}]"

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You could try (guessing)

    MyRequest.Open "GET", 'https:/api/v3/parts/match?apikey=EXAMPLE_KEY&queries=[{"mpn":"SN74S74N"}]'

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

Similar Threads

  1. System DSN (MS Access) to REST API / JSON
    By Hitesh in forum Queries
    Replies: 1
    Last Post: 03-15-2016, 11:37 AM
  2. Reading fields in querydef - how to find criteria
    By aytee111 in forum Programming
    Replies: 4
    Last Post: 05-08-2014, 03:44 PM
  3. Replies: 3
    Last Post: 05-03-2014, 11:00 AM
  4. Replies: 8
    Last Post: 08-02-2012, 10:50 AM
  5. Replies: 1
    Last Post: 06-04-2012, 12:43 AM

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