Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62

    Export to Nested JSON

    Hi All,

    Thank you in advance for any assistance you can provide on this.

    Below is a json format that I need to create from the attached table.



    Is it possible to do this in access?

    Json File: test_json.txt

    Please see the attached table: test.zip

    Thanks again for the help.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Access does not have native support for JSON.
    You have several choices
    1. Create your own code to manage the import. This is likely to be time consuming and may be complicated '
    2. Use Excel 'Get & Transform' feature (A2013/2016/365) or 'Power Query' (A2010) to transform into a format you can import into Access
    Neither is available in Excel 2007
    3. Instead import as XML if that's available which Access can handle
    4. Use an commercial app to manage the JSON import. For example see my JSON Analyse and Transform for Access (JATFA) app available from http://www.mendipdatasystems.co.uk/j...orm/4594138310

    Hope that helps
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sorry. My answer above referred to importing JSON files into Access.
    I don't have a specific answer for you regarding exporting data to a JSON file.
    You may be able to use Excel Get and Transform if you have access to Office 365.
    Alternatively, a commercial app like AccessToFile may do the job (but I've not tried it)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Quote Originally Posted by isladogs View Post
    Sorry. My answer above referred to importing JSON files into Access.
    I don't have a specific answer for you regarding exporting data to a JSON file.
    You may be able to use Excel Get and Transform if you have access to Office 365.
    Alternatively, a commercial app like AccessToFile may do the job (but I've not tried it)
    isladogs,

    Thank you for the feedback -- I am still struggling with a solution for this. I do not believe AccessToFile will summarize the data like I am trying to accomplish, but it seems to create the general framework for the json file. Unfortunately, I need the entire structure. I could be wrong and maybe it does both -- if someone has worked with AccessToFile, please let me know.

    Or maybe someone has an alternative solution.

    Thanks!

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Not sure what you mean by the entire structure compared to the general framework.
    Another possibility is to export the data as CSV file then use one of the free online CSV to JSON converters

    Once you've done this, how will be JSON file created be handled/used?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Quote Originally Posted by isladogs View Post
    Not sure what you mean by the entire structure compared to the general framework.
    Another possibility is to export the data as CSV file then use one of the free online CSV to JSON converters

    Once you've done this, how will be JSON file created be handled/used?

    Sorry, that was confusing... the general json format that I am running across on the formatting tools will add the header and sub categories, but I do not see any that will summarize the data, which I was referring to as the entire structure (of the json I posted).

    I was hoping to find a vba solution so that I can call it in MS Access (where the data is stored) and it will create the json needed. I am also looking for a solution in Python that will read in a csv and convert to the right format, but the to_json library is also not designed to do what I am attempting.

    Ultimately the file will be ingested into another system, that requires the json format that I attached in my original post.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As I said in post #1 and you probably already know, Access does not support JSON natively.
    I think your best bet is to convert to CSV then use an online converter or use Excel to do the conversion.

    If you ever need to do this in reverse (import JSON into Access), then I can help you.

    Good luck and if you find a solution, do let us know.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I was watching some videos by Steve Bishop that referenced JSON.... don't remember if there was a mention of exporting, but you might look into it.
    The video: https://www.youtube.com/watch?v=5qpU...Pr5FZ&index=38

    There is also a link to Google Docs for VBA - JSON class code -> https://code.google.com/archive/p/vba-json/source


    I searched stackoverflow using https://stackoverflow.com/search?pag...%20using%20VBA


    Maybe this post on stackoverflow would be of help.... https://stackoverflow.com/questions/...to-nested-json
    It references Excel, but maybe you could modify it to work with Access.....

  9. #9
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Quote Originally Posted by isladogs View Post
    As I said in post #1 and you probably already know, Access does not support JSON natively.
    I think your best bet is to convert to CSV then use an online converter or use Excel to do the conversion.

    If you ever need to do this in reverse (import JSON into Access), then I can help you.

    Good luck and if you find a solution, do let us know.
    Hi isladogs,

    I'm still seeking answers on this problem and I was wondering if you could assist in exporting the data from MS Access in an XML fomat that was similar to the JSON format I have posted.

    Thanks!

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi
    Sorry but I've never needed to do an export to XML. However it should be relatively straightforward as that functionality is built in to Access
    You should find it easier to get assistance for working with XML as more Access users are likely to have done this.

    Good luck
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Thanks for the response isladogs

  12. #12
    PBCN is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    24
    Im working on a similar situation but posting in Json, below is the code I'm using.

    Function apitestpost()
    Dim httpReq As New XMLHTTP60
    Dim sendtext As String
    Dim msti As String
    Dim tpid As String
    Dim insdate As String
    Dim reid As String
    Dim insby As String
    Dim acc As String
    Dim abn As String
    Dim emsub As String
    Dim empost As String
    Dim emname As String
    Dim cted As String
    Dim inspId As String

    inspId = Me.ID
    acc = Me.InspectorAccredtiationNo
    insby = Me.Inspector
    reid = Me.ResultId
    insdate = Me.DateOfInspection
    tpid = Me.TypeId
    msti = Me.MilestoneTypeId
    abn = Me.EmployerAbn
    emsub = Me.EmployerSuburb
    empost = Me.EmployerPostcode
    emname = Me.EmployerName
    cted = Now
    sendtext = "{""ID"":" & inspId & ",""milestonetypeid"":" & msti & ",""created"":""" & cted & """,""typeid"":" & tpid & ",""inspectiondate"":""" & insdate & """,""resultid"":" & reid & ",""inspectedby"":""" & insby & """,""accreditationnumber"":""" & acc & """,""employername"":""" & emname & """,""employerabn"":""" & abn & """,""employersuburb"":""" & emsub & """,""employerpostcode"":""" & empost & """}"

    httpReq.Open "POST", "http://localhost:55505/api/inspections", False
    httpReq.SetRequestHeader "Content-Type", "application/json"
    httpReq.SetRequestHeader "Authorization", "Token type Token"
    httpReq.send sendtext




    End Function

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Are you just sending one record as your JSON file or do you have code to convert an entire table as JSON?
    Or better still to create a 'nested' JSON file as the OP wants?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    PBCN is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    24
    What I posted is for one record at a time, its intended for real time reporting.

  15. #15
    PBCN is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    24
    what Im struggling with at the moment is the GET request to get the token, where and how to word the grant type, username and password. either getting a bad header request or unsupported grant type

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. Replies: 15
    Last Post: 04-26-2018, 01:54 PM
  3. Dealing with json
    By control1 in forum Access
    Replies: 24
    Last Post: 06-07-2017, 02:08 AM
  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: 4
    Last Post: 07-31-2015, 12: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