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