Solved import json

[isladogs]

I used a few of my favorite online tools to check if it's valid and none said there was a problem. It's valid JSON, at least the json in #12.

I checked the myjson2 file from post #10 and the sample json with 200 records. Both were zipped in post #5 of this parallel thread by the same OP

import xml

is there any way to automate importing XML for beginner user I want to use the VBA Code Because I want to hide the ribbon External data > import and link > XML

www.access-programmers.co.uk

 

[zezo2021]

And here's the result of that. I may have changed the method to read the file, merely out of being my method. You can keep using yours. Just remember to handle the BOM.

Option Compare Database
Option Explicit

Private Sub Command0_Click()
    ImportJSON
End Sub

Sub ImportJSON()
    Dim FilePath As String
    Dim RawContent As String
    Dim FinalContent As String
    Dim FileNumber As Long
    Dim JsonStart As Long
 
    FilePath = CurrentProject.Path & "\myjson3.json"
    FileNumber = FreeFile
    Open FilePath For Input As FileNumber

    ' read all
    RawContent = Input$(LOF(1), FileNumber)
    Close FileNumber
 
    ' find the first curly bracket
    JsonStart = InStr(1, RawContent, "{")

    ' the real start is at the curly bracket
    FinalContent = Mid(RawContent, JsonStart)
 
    Dim json As Object
    Dim arr As Variant
    Dim obj As Variant
    Set json = JsonConverter.ParseJson(FinalContent)
 
    ' import
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Table2", dbOpenTable)
 
    For Each arr In json
        For Each obj In json(arr)
            rs.AddNew
            rs![Serial Number] = obj("Serial Number")
            rs![Company Name] = obj("Company Name")
            rs![Employee Markme] = obj("Employee Markme")
            rs![Description] = obj("Description")
            rs![Leave] = obj("Leave")
            rs.Update
        Next obj
    Next arr
 
End Sub

For the other examples, a similar approach can be followed.

Done

Tested with another file and work correctly