Solved How to encode some field from Json string into the related tables in MS Access (1 Viewer)

[nector]

We will be getting data from JAR/WAR library on the same computers to be used to update our purchases module, now instead of capturing the received data manually I am thinking of trapping the data directly from Json into the related tables, that is tblpurchases and tblpurchasesdetails , this way we will surely avoid mistakes or human errors.

Now since at the moment the JAR/WAR library is not yet available, so while waiting, I just want to practice on the Json data in the notepad attached below and see if it can be moved into the mentioned related tables above that is tblpurchases and tblpurchasesdetails . I have provided a near complete VBA though not perfect but to give an idea of what is required:

Private Sub CmdJsondata_Click()
Dim db As dao.Database
Dim json As Object
Dim Details As Variant
Dim strDataAudit As String
Dim JsonAPI As String
Dim Z As Long
Dim rst As Recordset
Dim rs As Recordset

'Receipt Json from (JAR/WAR)Libray
'For demo purpose let us use the attached notepad to get the the data in those field into the related tables
'strDataAudit = Text.txt

  'Processing data from the string above
strDataAudit = "C:\Users\chris.hankwembo\Desktop\Testing\Json.txt"
Set db = CurrentDb
Set rst = db.OpenRecordset("tblpurchases", dbOpenDynaset) 'Im not sure here I'm just guesing
Set json = JsonConverter.ParseJson(strDataAudit)
     
    'Process data.even here I'm not 100% sure on the arranged field whether the data can be transfered
    
For Each Details In json
        rs.AddNew
        rs![resultDate] = Details("resultDate")
        rs![customerTpin] = Details("customerTpin")
        rs![customerBhfId] = Details("customerBhfId")
        rs![sarNo] = Details("sarNo")
        rs![ocrnDate] = Details("ocrnDate")
        rs![totItemCnt] = Details("totItemCnt")
        rs![totTaxblAmt] = Details("totTaxblAmt")
        rs![totTaxAmt] = Details("totTaxAmt")
        rs![totAmt] = Details("totAmt")
        rs![remark] = Details("remark")
        rs.Update
    Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set json = Nothing
    Set Details = Nothing
End Sub

{
  "resultCd": "000",
  "resultMsg": "It is succeeded",
  "resultDt": "20231120200723",
  "data": {
    "stockList": [
      {
        "custTpin": "9999911300",
        "custBhfId": "00",
        "sarNo": 6,
        "ocrnDt": "20231120",
        "totItemCnt": 1,
        "totTaxblAmt": 1800000,
        "totTaxAmt": 274576.27,
        "totAmt": 1800000,
        "remark": null,
        "itemList": [
          {
            "itemSeq": 1,
            "itemCd": "KR2BZX0000001",
            "itemClsCd": "1110162100",
            "itemNm": "Grocery_Item#1",
            "bcd": "8801234567051",
            "pkgUnitCd": "BZ",
            "pkg": 0,
            "qtyUnitCd": "CA",
            "qty": 450,
            "itemExprDt": null,
            "prc": 4000,
            "splyAmt": 1800000,
            "totDcAmt": 0,
            "taxblAmt": 1800000,
            "taxTyCd": "B",
            "taxAmt": 274576.27,
            "totAmt": 1800000
          }
        ]
      },
      {
        "custTpin": "9999911300",
        "custBhfId": "00",
        "sarNo": 59,
        "ocrnDt": "20231120",
        "totItemCnt": 1,
        "totTaxblAmt": 660000,
        "totTaxAmt": 100677.97,
        "totAmt": 660000,
        "remark": null,
        "itemList": [
          {
            "itemSeq": 1,
            "itemCd": "KR2AMXCRX0000001",
            "itemClsCd": "1110151600",
            "itemNm": "sample_nudle#1",
            "bcd": "8801234567001",
            "pkgUnitCd": "AM",
            "pkg": 0,
            "qtyUnitCd": "CR",
            "qty": 600,
            "itemExprDt": null,
            "prc": 1100,
            "splyAmt": 660000,
            "totDcAmt": 0,
            "taxblAmt": 660000,
            "taxTyCd": "B",
            "taxAmt": 100677.97,
            "totAmt": 660000
          }
        ]
      }
    ]
  }
}

 

[Uncle Gizmo]

I notice that you have not yet received a reply to your question. I am bumping it up the list so that it gets a second look...

 

[nector]

Thank so much for anticipated help

 

[Uncle Gizmo]

Thank so much for anticipated help

If you're still not getting any help, maybe rephrase the question. Possibly divided up into smaller questions which are more palatable to busy people trying to find the time to help out.

 

[nector]

Okay thank you so much for the advise

 

[nector]

Let us assume that the code below is coming from a web site , now I want to save it in an access table with the same columns or fields, how do I do it?

{
  "resultCd": "000",
  "resultMsg": "It is succeeded",
  "resultDt": "20231120200723",
  "data": {
    "stockList": [
      {
        "custTpin": "9999911300",
        "custBhfId": "00",
        "sarNo": 6,
        "ocrnDt": "20231120",
        "totItemCnt": 1,
        "totTaxblAmt": 1800000,
        "totTaxAmt": 274576.27,
        "totAmt": 1800000,
        "remark": null,
        "itemList": [
          {
            "itemSeq": 1,
            "itemCd": "KR2BZX0000001",
            "itemClsCd": "1110162100",
            "itemNm": "Grocery_Item#1",
            "bcd": "8801234567051",
            "pkgUnitCd": "BZ",
            "pkg": 0,
            "qtyUnitCd": "CA",
            "qty": 450,
            "itemExprDt": null,
            "prc": 4000,
            "splyAmt": 1800000,
            "totDcAmt": 0,
            "taxblAmt": 1800000,
            "taxTyCd": "B",
            "taxAmt": 274576.27,
            "totAmt": 1800000
          }
        ]
      },
      {
        "custTpin": "9999911300",
        "custBhfId": "00",
        "sarNo": 59,
        "ocrnDt": "20231120",
        "totItemCnt": 1,
        "totTaxblAmt": 660000,
        "totTaxAmt": 100677.97,
        "totAmt": 660000,
        "remark": null,
        "itemList": [
          {
            "itemSeq": 1,
            "itemCd": "KR2AMXCRX0000001",
            "itemClsCd": "1110151600",
            "itemNm": "sample_nudle#1",
            "bcd": "8801234567001",
            "pkgUnitCd": "AM",
            "pkg": 0,
            "qtyUnitCd": "CR",
            "qty": 600,
            "itemExprDt": null,
            "prc": 1100,
            "splyAmt": 660000,
            "totDcAmt": 0,
            "taxblAmt": 660000,
            "taxTyCd": "B",
            "taxAmt": 100677.97,
            "totAmt": 660000
          }
        ]
      }
    ]
  }
}

 

[Edgar_]

Use the VBA JSON library from github, I think I have seen you use it in the past, but here it is:
https://github.com/VBA-tools/VBA-JSON

Assuming the JSON data is stored in a string variable, you could use:
parsedJson = JsonConverter.ParseJson(MyJson)
And that would create a dictionary object. You can traverse dictionaries using loops.

 

[gemma-the-husky]

You can't automatically store it though, although maybe you can with some of @isladogs stuff.

I think what you want to do is manually analyse and understand the contents, and then use a JSON parser to extract the data you want, and save it with appropriate code.

I presume it's no good just, say, pulling out all the itemCd records without understanding how they form part of the entire JSON file.
The itemCd tag may even appear in different places in each record, and the ones you want might just be the ones that are in the itemList array. But even so the itemCd is probably no use without the other elements that also appear in the itemList rows.

 

[isladogs]

We will be getting data from JAR/WAR library on the same computers to be used to update our purchases module, now instead of capturing the received data manually I am thinking of trapping the data directly from Json into the related tables, that is tblpurchases and tblpurchasesdetails , this way we will surely avoid mistakes or human errors.

Now since at the moment the JAR/WAR library is not yet available, so while waiting, I just want to practice on the Json data in the notepad attached below and see if it can be moved into the mentioned related tables above that is tblpurchases and tblpurchasesdetails . I have provided a near complete VBA though not perfect but to give an idea of what is required:

You can't automatically store it though, although maybe you can with some of @isladogs stuff.

I've no idea what the JAR/WAR library is (or will be) but as mentioned in the last post by @gemma-the-husky I have an app designed to do precisely what you want to achieve i.e. import JSON data, process it and save it in suitable tables.

JSON Analyse & Transform

JSON Analyse & Transform for Access makes importing and handling JSON files a straightforward and largely automated task.

www.isladogs.co.uk

For many fairly simple JSON files (which yours appears to be), the whole process is completely automated.
However, you can modify the process where necessary to suit your own purposes.

This is a commercial app but there is a free cut-down version available to download and try out.
Obviously it does not have all the features of the commercial version

 

[cheekybuddha]

@isladogs, is your app still closed source?

 

[Edgar_]

You can't automatically store it though

You totally can store it automatically. The moment the JSON string is stored as a dictionary you can process the data in any way you want.

I presume it's no good just, say, pulling out all the itemCd records without understanding how they form part of the entire JSON file.
The itemCd tag may even appear in different places in each record, and the ones you want might just be the ones that are in the itemList array. But even so the itemCd is probably no use without the other elements that also appear in the itemList rows.

You're probably thinking in terms of string manipulation, that approach would give you such problems, the mentioned dictionary would not.

Thus, no need to pay for additional apps, but that's the choice of each person.

 

[isladogs]

@isladogs, is your app still closed source?

My app is commercial but has always been supplied as an ACCDB file (as is the free cut-down version) so all code is freely available to both view & edit. It also generates transform functions for use in the developer's own applications

The purpose of this app is to make the process as straightforward as possible.
It currently makes use of one section of Tim Hall's VBA JSON library for a small part of the process but almost all of the code is my own.

 

[cheekybuddha]

but has always been supplied as an ACCDB file

 

[GPGeorge]

My app is commercial but has always been supplied as an ACCDB file (as is the free cut-down version) so all code is freely available to both view & edit. It also generates transform functions for use in the developer's own applications

The purpose of this app is to make the process as straightforward as possible.
It currently makes use of one section of Tim Hall's VBA JSON library for a small part of the process but almost all of the code is my own.

I can vouch for it. Great tool.

 

[gemma-the-husky]

You totally can store it automatically. The moment the JSON string is stored as a dictionary you can process the data in any way you want.


You're probably thinking in terms of string manipulation, that approach would give you such problems, the mentioned dictionary would not.

Thus, no need to pay for additional apps, but that's the choice of each person.

What I mean is it's not trivial to extract data from an JSON file, any more than it us with an XML file. You have to understand the file structure, and the data elements that comprise the information you need. In my opinion.

Edit.
You might not want to capture all the data. You might just want to update certain fields in your database to values taken from the JSON file.

 

[Edgar_]

OP, let's break this down. I deleted all the code and will start from scratch.

We need to import the example text, here's one way to do it

Dim strDataAudit As String
strDataAudit = CreateObject("Scripting.FileSystemObject").OpenTextFile(CurrentProject.Path & "\Formatted Json Response.txt", ForReading).ReadAll

Let's convert that into a dictionary using the library

Dim json As Object
Set json = JsonConverter.ParseJson(strDataAudit)

It seems that you want to get resultDt from the root of the JSON to fill tblPurchases.resultDate. Here's what I'm seeing using a popular online tool for viewing JSON strings:

Let's see if this works first:
Debug.Print json("resultDt")

It works, the output was 20231120200723, we can now proceed. According to your code, you now want to fill tblPurchases.customerTpin. That can be found in another place of the JSON file:

According to your example and your code, you want to get custTpin, which is located in data.stockList. The "data" key is an object, so it expects only one. However, the square brackets in stockList indicate that this key expects an array of objects. Let's handle that with a For Each loop.

Dim stock As Variant
For Each stock In json("data")("stockList")
    Debug.Print stock("custTpin")
Next stock

It works as expected. Although, from the example and the output, I can see that custTpin returns the same number, must be the same customer. All good. Let's continue. You now want tblPurchases.customerBhfId and all the other keys from that same object, like sarNo, ocrnDt, etc. No idea what they mean in reality, but the data is in the JSON, so I can extract it the same way I did previously.

I notice that in your code you want to extract the data from the JSON string using the names defined in your table, that is wrong. When you want to extract data from the JSON string, you have to use the key names that the JSON uses. Look:

For example, resultDate is the name assigned to a column in your tblPurchases table, but the JSON file uses resultDt as the key name. I hope you get the idea: resultDate vs resultDt

These are the debug.print lines that help me see my data is being extracted properly from the JSON.

Dim stock As Variant
For Each stock In json("data")("stockList")
    Debug.Print "------------------"
    Debug.Print stock("custTpin")
    Debug.Print stock("custBhfId")
    Debug.Print stock("sarNo")
    Debug.Print stock("ocrnDt")
    Debug.Print stock("totItemCnt")
    Debug.Print stock("totTaxblAmt")
    Debug.Print stock("totTaxAmt")
    Debug.Print stock("totAmt")
    Debug.Print stock("remark")
    Debug.Print "------------------"
Next stock

Great, we can now proceed to populate tblPurchases using a recordset and these references. Things to remember:
1. you assigned your table column names that may be different from the JSON key names.
2. you must keep type consistency. You have string dates in the JSON, so you have to convert them.
3. you have to add a record for each "stock" in tblPurchases. The problem is that resultDate is shared for both, how do we assign this date to both records if the value won't be in the loop? Simple, we get it before the loop, then we reference it in the loop.

Dim db As DAO.Database
Set db = CurrentDb

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("tblpurchases")

Dim resDt As String
resDt = json("resultDt")

Dim stock As Variant
For Each stock In json("data")("stockList")
    rs.AddNew
    rs![resultDate] = DateSerial(Left(resDt, 4), Mid(resDt, 5, 2), Mid(resDt, 7, 2)) + _
                  TimeSerial(Mid(resDt, 9, 2), Mid(resDt, 11, 2), Mid(resDt, 13, 2))
    rs![customerTpin] = stock("custTpin")
    rs![customerBhfId] = stock("custBhfId")
    rs![sarNo] = stock("sarNo")
    rs![ocrnDate] = DateSerial(Left(stock("ocrnDt"), 4), Mid(stock("ocrnDt"), 5, 2), Mid(stock("ocrnDt"), 7, 2))
    rs![totItemCnt] = stock("totItemCnt")
    rs![totTaxblAmt] = stock("totTaxblAmt")
    rs![totTaxAmt] = stock("totTaxAmt")
    rs![totAmt] = stock("totAmt")
    rs![remark] = stock("remark")
    rs.Update
Next stock

See the attached database to see this in action. I'll get back at it to make it add to the other table soon, or you can take the lead. I hope this has been of help so far. Things to consider in the next post:
1. We need to set the recordset to see changes
2. We need to set the bookmark to the last modified record and get our Id from there
3. We have to insert into the child table the Id we got using another recordset
4. The JSON structure shows that the key ItemList in each stock expects an array of objects, so that's another For Each Loop we must perform before going into the next stock (embedded recordset)

 

[arnelgp]

here is a complete solution.
only you need reference to Microsoft Office XX.X Object library and Microsoft Excel XX.X Object Library.
this is using Excel worksheet, to break your json into rows of data.
then i manipulate it to save to your 2 tables.

open Form1.

 

[nector]

Thank you so much arnelgp & edgar I'm with your solutions .

With regards

Chris