将 JSON 提要自动解析为 MS Access [英] Parsing JSON feed automatically into MS Access

问题描述

我公司有一家供应商提供 JSON 数据源，我需要每两个小时将其加载到我们的 MS Access 数据库中.我需要:

1. 从提要加载数据，
2. 将 JSON 解析为 Access 可用的格式，然后
3. 将其插入到数据库中.

我遇到了这个问题类似的问题，但没有很好的描述如何在 MS Access 中实现这一点.非常感谢任何帮助！

解决方案

使用 VBA JSON 库，您当然可以将 JSON 格式的文件导入 MS Access.这个想法是将 JSON 数据视为字典的集合，Visual Basic 提供了 收藏 和字典 作为数据结构.

以下是步骤:

1. 构建一个表以匹配预期 JSON 数据的结构
2. 在 MS Access 的 VBA IDE 端，将 JsonConverter.bas(来自上面的链接)导入一个新模块
3. 仍在 IDE 中，在工具/参考下，勾选 VBA 参考:Microsoft Scripting Runtime
4. 包含以下代码，用于读取 JSON 文本文件，将其解析为字典集合(带有键和值)，并将值迭代地附加到 Access 表中.将代码放在 Access 表单或模块后面(示例使用一个嵌套级别的 JSON 文件)

JSON

<预><代码>[{col1":某个数字，"col2": "somestring","col3": "somestring","col4": "somestring",col5":一些字符串"}]

VBA 代码

私有函数 JSONImport()Dim db As Database, qdef As Querydef将 FileNum 变暗为整数Dim DataLine As String, jsonStr As String, strSQL As StringDim p As Object，元素作为变体设置 db = CurrentDb' 从外部文件中读取FileNum = FreeFile()打开C:PathToJsonFile.json"作为输入#FileNum' 解析文件串jsonStr = ""虽然不是 EOF(FileNum)行输入 #FileNum, DataLinejsonStr = jsonStr &数据线新行温德关闭#FileNum设置 p = ParseJson(jsonStr)' 遍历数据行，附加到表对于每个元素在 pstrSQL = "PARAMETERS [col1] Long, [col2] Text(255), [col3] Text(255), " _&[col4] 文本(255)，[col5] 文本(255)；_&插入表名(col1，col2，col3，col4，col5)"_&值([col1]，[col2]，[col3]，[col4]，[col5])；"设置 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.执行下一个元素设置元素 = 无设置 p = 无结束函数

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:

1. load the data from the feed,
2. parse the JSON into a usable format for Access, and then
3. insert it into the database.

I came across this question discussing a similar issue, but there's no good description there as to how to implement this in MS Access. Any help gratefully appreciated!

解决方案

Using the VBA JSON library, 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 collection and dictionary as data structures.

Below are the steps:

1. Build a table to match the structure of expected JSON data
2. On the VBA IDE side of MS Access, import the JsonConverter.bas (from link above) into a new module
3. Still in the IDE, under Tools / References, check off the VBA Reference: Microsoft Scripting Runtime
4. 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

[
  {
    "col1": somenumber,
    "col2": "somestring",
    "col3": "somestring",
    "col4": "somestring",
    "col5": "somestring"
  }
]

VBA Code

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:PathToJsonFile.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

这篇关于将 JSON 提要自动解析为 MS Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！