正在分析MS Access中的CSV(JSON)数据字段

Question

我有一个CSV数据文件，我将其加载到MS Access中。这样做没问题。我已经全部装好了。

我的问题是，文件中的一条记录包含一个字段，该字段包含CSV数据(JSON数据(，即行项目发票数据(ProductData(，它可能最多包含25个行项目。

我试图做的是将该字段解析为不同表中的多个记录，所有记录都与"500"之后的数字相关，即事务编号。

有人对如何在Microsoft Access中做到这一点有什么想法吗？

示例数据：

500,3,30600052763,30128903358,"{""type"": ""reportingData"", ""merStoreId"": ""8135001"", ""productData"": [{""productCode"": ""001"", ""totalAmount"": ""20.00"", ""quantity"": ""16.000"", ""unitPrice"": ""1.25"", ""tax1Amount"": ""6.22""}, {""productCode"": ""102"", ""totalAmount"": ""14.00"", ""quantity"": ""1.000"", ""unitPrice"": ""14.00""}, {""productCode"": ""963"", ""totalAmount"": ""1.00"", ""quantity"": ""1.000"", ""unitPrice"": ""1.00"", ""tax1Amount"": ""0.00""}]}"

我已经附上了一个样本，我希望根据上面的交易编号3来查看表格：

Tbl500                                      
Transaction Number  Second Field    Third Field Type            MerStoreID  ProductCode TotalAmount Quantity    UnitPrice   Tax1Amount  Tax2Amount
3                   30600052763     0128903358  reportingData   8135001     001         20.00       16.000      1.25        6.22    
3                   30600052763     30128903358 reportingData   8135001     102         14.00       1.000       14.00       
3                   30600052763     30128903358 reportingData   8135001     963         1.00        1.000       1.00        0.00    

Answer 1

下面是一些代码，它获取文本文件，导入数据并对其进行整理，然后将其拆分为可变长度的数组。然后，它循环这个数组，并将数据添加到表中，使用"productCode"的存在来检测它是否是新记录：

Sub sGetJSONData()
On Error GoTo E_Handle
Dim db As DAO.Database
Dim rsData As DAO.Recordset
Dim strFile As String
Dim intFile As Integer
Dim strInput As String
Dim astrData() As String
Dim intLoop1 As Integer
Set db = DBEngine(0)(0)
Set rsData = db.OpenRecordset("SELECT * FROM tblJSON WHERE 1=2;")
strFile = "C:testjson.txt"
intFile = FreeFile
Open strFile For Input As intFile
Do
Erase astrData
Line Input #intFile, strInput
strInput = Replace(strInput, Chr(34) & Chr(34), "")
strInput = Replace(strInput, "productData: [{", "")
strInput = Replace(strInput, "{productCode", "productCode")
strInput = Left(strInput, Len(strInput) - 4)
strInput = Replace(strInput, "}", "")
strInput = Replace(strInput, ", ", ",")
astrData = Split(strInput, ",")
With rsData
.AddNew
!TransactionNumber = astrData(1)
!SecondField = astrData(2)
!ThirdField = astrData(3)
!Type = Mid(astrData(4), 9)
!ProductCode = Mid(astrData(6), 13)
!MerStoreID = Mid(astrData(5), 13)
For intLoop1 = 7 To UBound(astrData)
If Left(astrData(intLoop1), 11) = "totalAmount" Then !TotalAmount = Mid(astrData(intLoop1), 14)
If Left(astrData(intLoop1), 8) = "quantity" Then !Quantity = Mid(astrData(intLoop1), 11)
If Left(astrData(intLoop1), 9) = "unitPrice" Then !UnitPrice = Mid(astrData(intLoop1), 12)
If Left(astrData(intLoop1), 10) = "tax1Amount" Then !Tax1Amount = Mid(astrData(intLoop1), 13)
If Left(astrData(intLoop1), 10) = "tax2Amount" Then !Tax2Amount = Mid(astrData(intLoop1), 13)
If Left(astrData(intLoop1), 11) = "productCode" Then
.Update
.AddNew
!TransactionNumber = astrData(1)
!SecondField = astrData(2)
!ThirdField = astrData(3)
!Type = Mid(astrData(4), 9)
!ProductCode = Mid(astrData(intLoop1), 13)
!MerStoreID = Mid(astrData(5), 13)
End If
Next intLoop1
.Update
End With
Loop Until EOF(intFile)
sExit:
On Error Resume Next
rsData.Close
Set rsData = Nothing
Reset
Exit Sub
E_Handle:
MsgBox Err.Description & vbCrLf & vbCrLf & "sGetJSONData", vbOKOnly + vbCritical, "Error: " & Err.Number
Resume sExit
End Sub

问候，