我正在尝试将 JSON 文件导入到 MS Access 表中。我在网上查了一下,发现这个堆栈溢出链接说明了这一点。自动将 JSON feed 解析到 MS Access 中我已经复制并粘贴了该字符串中的代码,并对其进行了修改以提取我的 JSON 文件,并且该代码似乎确实解析了该文件。但是,我在将解析文件的所有元素放入 Access 表时遇到问题。它似乎只引入不属于对象或数组的元素。换句话说,NPI 元素没有包含在方括号或大括号中,因此导入成功。请参阅下面的代码和 JSON 数据结构。
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
Set db = CurrentDb
' READ FROM EXTERNAL FILE
FileNum = FreeFile()
'Open "P:\PROF REIMB\PROF REIMB\HIX\CY 2021 Analysis\Centene\JSON\provider_facility - jun 52020.json"
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)
' ITERATE THROUGH DATA ROWS, APPENDING TO TABLE
For Each element In P
strSQL = "PARAMETERS (first), [middle] Text(255), [last] Text(255), [suffix] Text(255), [npi]
Text(255), [type] Text(255), [addresses] Text(255), [addresses_2] Text(255), [city] Text(255),
[state] Text(255), [zip] Text(255), [phone] Text(255), [specialty] Text(255), [accepting]
Text(255), [plans] Text(255), [plan_id_type] Text(255), [plan_id] Text(255), [network_tier]
Text(255), [years] Text(255); " _
& "INSERT INTO FrmJSONFile (first, middle, last, suffix, npi, type, addresses,
addresses_2, city, state, zip, phone, specialty, accepting, plans, plan_id_type,
plan_id, network_tier, years) " _
& "VALUES([first], [middle], [last], [suffix], [npi], [type], [addresses], [addresses_2], [city],
[state], [zip], [phone], [specialty], [accepting], [plans], [plan_id_type], [plan_id],
[network_tier], [years]);"
Set qdef = db.CreateQueryDef("", strSQL)
qdef!first = element("first")
qdef!middle = element("middle")
qdef!last = element("last")
qdef!suffix = element("suffix")
qdef!npi = element("npi")
qdef!Type = element("type")
qdef!addresses = element("addresses")
qdef!addresses_2 = element("addresses_2")
qdef!city = element("city")
qdef!State = element("state")
qdef!Zip = element("zip")
qdef!phone = element("phone")
qdef!specialty = element("specialty")
qdef!accepting = element("accepting")
qdef!plans = element("plans")
qdef!plan_id_type = element("plan_id_type")
qdef!plan_id = element("plan_id")
qdef!network_tier = element("network_tier")
qdef!years = element("years")
qdef.Execute
Next element
Set element = Nothing
Set P = Nothing
结束功能
JSON 文件:
[{
"name":{
"first":"John","middle":"G","last":"Doe","suffix":"MD"
},
"npi":"1234567891",
"type":"INDIVIDUAL",
"addresses":[
{"address":"123 Main St",
"address_2":"",
"city":"CHARLESTON",
"state":"SC",
"zip":"29406",
"phone":"8037779311"}
],
"specialty":["ANESTHESIOLOGY"],
"accepting":"not accepting",
"plans":[
{"plan_id_type":"HIOS-PLAN-ID","plan_id":"12345678912",
"network_tier":"PREFERRED","years":[2020]},
{"plan_id_type":"HIOS-PLAN-ID","plan_id":"12345678913",
"network_tier":"PREFERRED","years":[2020]},
{"plan_id_type":"HIOS-PLAN-ID","plan_id":"12345678914",
"network_tier":"PREFERRED","years":[2020]},
{"plan_id_type":"HIOS-PLAN-ID","plan_id":"12345678915",
"network_tier":"PREFERRED","years":[2020]},
{"plan_id_type":"HIOS-PLAN-ID","plan_id":"12345678916",
"network_tier":"PREFERRED","years":[2020]},
{"plan_id_type":"HIOS-PLAN-ID","plan_id":"12345678917",
"network_tier":"PREFERRED","years":[2020]},
{"plan_id_type":"HIOS-PLAN-ID","plan_id":"12345678918",
"network_tier":"PREFERRED","years":[2020]},
{"plan_id_type":"HIOS-PLAN-ID","plan_id":"12345678919",
"network_tier":"PREFERRED","years":[2020]}
],
"languages":["ENGLISH"],
"gender":"Male",
"last_updated_on":"2020-05-26"
}]