Results 1 to 5 of 5
  1. #1
    Meeks is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2021
    Posts
    2

    using JSONconverter.bas to import JSON file objects to Access table

    Does anyone have experience using a tool called JsonConverter.bas to bring json files into MS Access? It is available at VBA-tools/VBA-JSON: JSON conversion and parsing for VBA (github.com)
    I learned of JsonConverter.bas and was able to replicate a couple of examples given on Stack Overflow where people were asking for help importing JSON files to MS Access (https://stackoverflow.com/questions/...s-access-table is the best example), but I am not able to get the code to work with my own JSON file.
    I am including a screenshot of the file structure of the JSON file that I am working with, There are 68 high level nested objects in the file and I am trying to import the 10,943 objects nested within object Gross Charges into a table.
    The maximum key:value pairs with the 10,943 objects that falls under object Gross Charges is 9. I expanded an object with 8 key:value pairs in the screen shot so you can see the table, and some objects have as few as 4 key/value pairs. The table I built in Access to hold the imported data has all 9 fields.
    The code I wrote (that guides the JSON file import using the JSONParse functions in the JSONconverter.bas) to try and pull my elements errors out and crashes my Access database. I get a runtime error 19 Out of String Space. I know I am not controlling for the nested elements correctly. Given the object "Gross Charges" is an element itself, I thought the "sub-el" was the way to go but it isn't.
    I tried using the solver's method which involves bring my JSON data in via an append query, as once I get it to work I will bring in some of the other higher nested objects. I did try it also using the code in this example, which imported data directly to a table (no append query), but that didn't work either (Parsing JSON feed automatically into MS Access - Stack Overflow)

    Thank you for any help, it is appreciated more than you know. I have been spinning on this hoping little tweaks would work but they fail miserably.
    My append qry (app_grosschg) SQL, that appends to table NL_Gross_Chg:
    PARAMETERS [prm_ItemCd] Text ( 255 ), [prm_ItemDesc] Text ( 255 ), [prm_CDMRevCd] Text ( 255 ), [prm_CDMHCPCS] Text ( 255 ), [prm_AvgLoadPrice1] Long, [prm_StandPriceOpt1] Long, [prm_AvgLoadPrice2] Long, [prm_StandPriceOpt2] Long;
    INSERT INTO NL_Gross_Chg ( ItemCd, ItemDesc, CDMRevCd, CDMHCPCS, AvgLoadPrice1, StandPriceOpt1, AvgLoadPrice2, StandPriceOpt2 )


    VALUES (prm_ItemCd, prm_ItemDesc, prm_CDMRevCd, prm_CDMHCPCS, prm_AvgLoadPrice1, prm_StandPriceOpt1, prm_AvgLoadPrice2, prm_StandPriceOpt2);
    My code based on the solver's code on Stack Overflow:
    Private Function JSONImport()
    Dim db As Database, qdef As QueryDef
    Dim FileNum As Integer
    Dim DataLine As String, jsonStr As String, strSQL As String
    Dim P As Object, element As Variant, sub_el As Variant
    Set db = CurrentDb
    ' READ FROM EXTERNAL FILE
    FileNum = FreeFile()
    Open "C:\Users\bethl\Desktop\DeleteEventually\NL.js on" For Input As #FileNum
    ' PARSE FILE STRING
    jsonStr = ""
    While Not EOF(FileNum)
    Line Input #FileNum, DataLine
    jsonStr = jsonStr & DataLine & vbNewLine
    Wend
    Close #FileNum
    Set P = ParseJson(jsonStr)
    qdef.Execute
    Set qdef = Nothing
    ' GrossChg QUERY
    Set qdef = db.QueryDefs("app_grosschg")
    ' NESTED ITERATION THROUGH EACH PLANS ITEMS
    For Each sub_el In element("gross charges")
    qdef!prm_ItemCd = sub_el("Itemcode")
    qdef!prm_ItemDesc = sub_el("Description")
    qdef!prm_CDMRevCd = sub_el("CDM Revenue Code")
    qdef!prm_CDMHCPCS = sub_el("CDM HCPCS")
    qdef!prm_AvgLoadPrice1 = sub_el("Average Load Price Across All Fee Schedules*")
    qdef!prm_StandPriceOpt1 = sub_el("Standard Price Option 1")
    qdef!prm_AvgLoadPrice2 = sub_el("Average Load Price Across All Fee Schedules* (2)")
    qdef!prm_StandPriceOpt2 = sub_el("Standard Price Option 2")
    qdef!prm_Note = sub_el("Note")
    qdef.Execute
    Next sub_el
    Next element
    Set element = Nothing: Set P = Nothing
    Set qdef = Nothing: Set db = Nothing
    End Function
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    meeks,
    Your post has been approved so now is showing.

    I suggest you review this thread. Also note the link within the thread to JATFA.

  3. #3
    Meeks is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2021
    Posts
    2
    I will, thank you so much! I only glanced so far, but I am loving this comment "Even the simplest JSON structures can sometimes defeat Tim Hall's ParseJson code!" I appreciate the help more than you know, I have spent a lot of time trying to make my code work and now am kind of obsessed. So thank you again!!!!!!

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Yes, but in a thin column of text?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi
    The JATFA app on my website JSON Analyse & Transform - Mendip Data Systems will, later this week, be moved to a new website due to issues with the old web host
    If that link fails, try this one JSON Analyse & Transform - Isladogs on Access.

    That file structure isn't particularly complex compared to many JSON files I have worked with

    If you have any questions about the app or issues with downloads, please feel free to contact me by email or PM
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Exporting Query As JSON File
    By breakingme10 in forum Code Repository
    Replies: 1
    Last Post: 02-19-2020, 10:55 AM
  2. Replies: 11
    Last Post: 04-27-2017, 05:16 PM
  3. Replies: 3
    Last Post: 10-08-2014, 11:05 AM
  4. Import txt file into Access Table
    By redbull in forum Import/Export Data
    Replies: 3
    Last Post: 05-02-2012, 09:32 AM
  5. Replies: 0
    Last Post: 12-08-2011, 09:12 AM

Tags for this Thread

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