将 JSON 提要自动解析为 MS Access
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30510570/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parsing JSON feed automatically into MS Access
提问by Martin O'Neill
My company has a vendor providing a JSON feed of data that I need to load into our MS Access database every two hours. I need to:
我的公司有一家供应商提供 JSON 数据源，我需要每两个小时将其加载到我们的 MS Access 数据库中。我需要：
- load the data from the feed,
- parse the JSON into a usable format for Access, and then
- insert it into the database.
- 从提要加载数据，
- 将 JSON 解析为 Access 可用的格式，然后
- 将其插入到数据库中。
I came across this questiondiscussing a similar issue, but there's no good description there as to how to implement this in MS Access. Any help gratefully appreciated!
我遇到了这个问题，讨论了一个类似的问题，但没有关于如何在 MS Access 中实现这一点的很好的描述。任何帮助，不胜感激！
回答by Parfait
Using the VBA JSONlibrary, you certainly can import JSON formatted files into MS Access. The idea is to consider JSON data as a collection of dictionaries and Visual Basic provides the collectionand dictionaryas data structures.
使用VBA JSON库，您当然可以将 JSON 格式的文件导入 MS Access。这个想法是将 JSON 数据视为字典的集合，而 Visual Basic 提供集合和字典作为数据结构。
Below are the steps:
以下是步骤：
- Build a table to match the structure of expected JSON data
- On the VBA IDE side of MS Access, import the JsonConverter.bas(from link above) into a new module
- Still in the IDE, under Tools / References, check off the VBA Reference: Microsoft Scripting Runtime
- Include the following code that reads the JSON text file, parses it as a collection of dictionaries (with keys and valeus), and appends values iteratively into Access table. Place code behind an Access form or module (example uses a one nested level JSON file)
- 构建一个表以匹配预期 JSON 数据的结构
- 在 MS Access 的 VBA IDE 端，将JsonConverter.bas（来自上面的链接）导入到一个新模块中
- 仍然在 IDE 中，在 Tools/References 下，勾选 VBA Reference: Microsoft Scripting Runtime
- 包含以下代码，该代码读取 JSON 文本文件，将其解析为字典集合（带有键和值），并将值迭代地附加到 Access 表中。将代码放在 Access 表单或模块后面（示例使用一个嵌套级别的 JSON 文件）
JSON
JSON
[
{
"col1": somenumber,
"col2": "somestring",
"col3": "somestring",
"col4": "somestring",
"col5": "somestring"
}
]
VBA Code
VBA 代码
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 "C:\Path\To\JsonFile.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 [col1] Long, [col2] Text(255), [col3] Text(255), " _
& "[col4] Text(255), [col5] Text(255); " _
& "INSERT INTO TableName (col1, col2, col3, col4, col5) " _
& "VALUES([col1], [col2], [col3], [col4], [col5]);"
Set qdef = db.CreateQueryDef("", strSQL)
qdef!col1 = element("col1")
qdef!col2 = element("col2")
qdef!col3 = element("col3")
qdef!col4 = element("col4")
qdef!col5 = element("col5")
qdef.Execute
Next element
Set element = Nothing
Set p = Nothing
End Function
回答by Heinz
Json file handling in MS Access is easy. Just rename the .json extension to .txt and use the textimport function with the delimiter set to (:) and the text delimiter to ("). One line of code... Happy coding!
MS Access 中的 Json 文件处理很容易。只需将 .json 扩展名重命名为 .txt 并使用分隔符设置为 (:) 并将文本分隔符设置为 (")的文本导入功能。一行代码......快乐编码！
