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

问题描述

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

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. 从提要中加载数据，
2. 将JSON解析为可用于Access的格式，然后
3. 将其插入数据库.

我遇到了这个问题，讨论了类似的问题，但是关于如何在MS Access中实现这一点，这里没有很好的描述.任何帮助表示感谢！

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!

推荐答案

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

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.

以下是步骤:

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

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

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

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