Solved import json

zezo2021 · Sep 23, 2023

Why this code not work

I do all the steps
Add reference and module

attache all code and reference

the db with code
json file

theDBguy · Sep 23, 2023

You have these lines:

Code:

            .AddNew
            !fruit = item("fruit")
            !Size = item("size")
            !Color = item("color")
            .Update

Try changing them into these:

Code:

            .AddNew
            !fruit = json("fruit")
            !Size = json("size")
            !Color = json("color")
            .Update

zezo2021 · Sep 23, 2023

theDBguy said:

You have these lines:

Code:

            .AddNew
            !fruit = item("fruit")
            !Size = item("size")
            !Color = item("color")
            .Update

Try changing them into these:

Code:

            .AddNew
            !fruit = json("fruit")
            !Size = json("size")
            !Color = json("color")
            .Update

magic

zezo2021 · Sep 23, 2023

another question:
Why was the row repeated 3 times

zezo2021 · Sep 23, 2023

Code:

{
    "firstName": "Rack",
    "lastName": "Jackon",
    "gender": "man",
    "age": 24,
    "address": {
        "streetAddress": "126",
        "city": "San Jone",
        "state": "CA",
        "postalCode": "394221"
    },
    "phoneNumbers": [
        { "type": "home", "number": "7383627627" }
    ]
}

for address and phoneNumbers

What should I do to convert to an access table?

firstName,lastname, age is very easy same design

Edgar_ · Sep 23, 2023

zezo2021 said:
for address and phoneNumbers

What should I do to convert to an access table?

firstName,lastname, age is very easy same design

This is how you would go though them:

Code:

Private Sub xxx()
    Dim str As String
    str = _
    "{" & _
    "'firstName': 'Rack'," & _
    "'lastName': 'Jackon'," & _
    "'gender': 'man'," & _
    "'age': 24," & _
    "'address': {" & _
    "    'streetAddress': '126'," & _
    "    'city': 'San Jone'," & _
    "    'state': 'CA'," & _
    "    'postalCode': '394221'" & _
    "}," & _
    "'phoneNumbers': [" & _
    "    { 'type': 'home', 'number': '7383627627' }" & _
    "]" & _
    "}"
   
    Dim json As Object
    Set json = JsonConverter.ParseJson(str)
   
    Dim item As Variant
    Dim subItem As Variant
   
    ' address is a key with an embedded object
    For Each item In json("address")
        Debug.Print item, json("address")(item)
    Next item
   
    ' phoneNumbers is a key with an embedded array of objects
    For Each item In json("phoneNumbers")
        For Each subItem In item
            Debug.Print subItem, item(subItem)
        Next subItem
    Next item
   
End Sub

I don't know your table structure.

zezo2021 · Sep 23, 2023

Edgar_ said:

This is how you would go though them:

Code:

Private Sub xxx()
    Dim str As String
    str = _
    "{" & _
    "'firstName': 'Rack'," & _
    "'lastName': 'Jackon'," & _
    "'gender': 'man'," & _
    "'age': 24," & _
    "'address': {" & _
    "    'streetAddress': '126'," & _
    "    'city': 'San Jone'," & _
    "    'state': 'CA'," & _
    "    'postalCode': '394221'" & _
    "}," & _
    "'phoneNumbers': [" & _
    "    { 'type': 'home', 'number': '7383627627' }" & _
    "]" & _
    "}"
  
    Dim json As Object
    Set json = JsonConverter.ParseJson(str)
  
    Dim item As Variant
    Dim subItem As Variant
  
    ' address is a key with an embedded object
    For Each item In json("address")
        Debug.Print item, json("address")(item)
    Next item
  
    ' phoneNumbers is a key with an embedded array of objects
    For Each item In json("phoneNumbers")
        For Each subItem In item
            Debug.Print subItem, item(subItem)
        Next subItem
    Next item
  
End Sub

I don't know your table structure.

Hello

thank you for your efforts

I don't need code

I need the structure of the table

I want to map this code to access table

for example
first name data type text 255

the problem is with address and phone number I don't know how to convert them to fields

because they are complex fields in JSON

Edgar_ · Sep 24, 2023

I see what you mean. I think you have 2 options:

1. Schema with Separate "Persons," "Addresses," and "PhoneNumbers" Tables:

Code:

CREATE TABLE Persons (
    person_id AUTOINCREMENT PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    gender TEXT,
    age INT
);

CREATE TABLE Addresses (
    address_id AUTOINCREMENT PRIMARY KEY,
    person_id INT,
    street_address TEXT,
    city TEXT,
    state TEXT,
    postal_code TEXT,
    FOREIGN KEY (person_id) REFERENCES Persons(person_id)
);

CREATE TABLE PhoneNumbers (
    phone_number_id AUTOINCREMENT PRIMARY KEY,
    person_id INT,
    type TEXT,
    number TEXT,
    FOREIGN KEY (person_id) REFERENCES Persons(person_id)
);

2. Schema with Address Fields Included in "Persons" Table:

Code:

CREATE TABLE Persons (
    person_id AUTOINCREMENT PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    gender TEXT,
    age INT,
    street_address TEXT,
    city TEXT,
    state TEXT,
    postal_code TEXT
);

CREATE TABLE PhoneNumbers (
    phone_number_id AUTOINCREMENT PRIMARY KEY,
    person_id INT,
    type TEXT,
    number TEXT,
    FOREIGN KEY (person_id) REFERENCES Persons(person_id)
);

As you can see, TEXT should be used to store those fields.

zezo2021 · Sep 24, 2023

Edgar_ said:

I see what you mean. I think you have 2 options:

1. Schema with Separate "Persons," "Addresses," and "PhoneNumbers" Tables:

Code:

CREATE TABLE Persons (
    person_id AUTOINCREMENT PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    gender TEXT,
    age INT
);

CREATE TABLE Addresses (
    address_id AUTOINCREMENT PRIMARY KEY,
    person_id INT,
    street_address TEXT,
    city TEXT,
    state TEXT,
    postal_code TEXT,
    FOREIGN KEY (person_id) REFERENCES Persons(person_id)
);

CREATE TABLE PhoneNumbers (
    phone_number_id AUTOINCREMENT PRIMARY KEY,
    person_id INT,
    type TEXT,
    number TEXT,
    FOREIGN KEY (person_id) REFERENCES Persons(person_id)
);

2. Schema with Address Fields Included in "Persons" Table:

Code:

CREATE TABLE Persons (
    person_id AUTOINCREMENT PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    gender TEXT,
    age INT,
    street_address TEXT,
    city TEXT,
    state TEXT,
    postal_code TEXT
);

CREATE TABLE PhoneNumbers (
    phone_number_id AUTOINCREMENT PRIMARY KEY,
    person_id INT,
    type TEXT,
    number TEXT,
    FOREIGN KEY (person_id) REFERENCES Persons(person_id)
);

As you can see, TEXT should be used to store those fields.

thanks

zezo2021 · Sep 24, 2023

this file has not been imported
please use Form 2

I don't want to duplicate the same row

thank you in advance

theDBguy · Sep 24, 2023

I think your table schema is wrong. That structure could be gained through the result of a crosstab query. I would modify your table structure to simply have ID, Product, Customer, QTR, and Amount.

zezo2021 · Sep 24, 2023

Can you check attached db and very simple JSON file
here is a very simple file but an error appears

theDBguy · Sep 24, 2023

zezo2021 said:
Can you check attached db and very simple JSON file
here is a very simple file but an error appears

If you're getting this error,

then try using the attached updated json file instead.

However, your code still needs to be modified, because you will eventually get this error next.

zezo2021 · Sep 24, 2023

theDBguy said:
If you're getting this error,
View attachment 110009
then try using the attached updated json file instead.

However, your code still needs to be modified, because you will eventually get this error next.
View attachment 110011

Thanks for your efforts

I don't know how to solve type mismatch

I think converting JSON is not easy

theDBguy · Sep 24, 2023

zezo2021 said:
Thanks for your efforts

I don't know how to solve type mismatch

I think converting JSON is not easy

I used to use that JSON parser too but changed to a different one, since I had issues with it earlier as well.

Where are you getting your JSON files? They don't seem to be well-formed.

zezo2021 · Sep 24, 2023

theDBguy said:
I used to use that JSON parser too but changed to a different one, since I had issues with it earlier as well.

Where are you getting your JSON files? They don't seem to be well-formed.

I'm training on importing files to access DB
Learning
with a basic JSON file

form this site

Coding Tutorials and Tools for Developers, Get Skilled Developers,IT Solutions & Services

AppsLoveWorld Technologies provides software development and offer Free Resources for the developer like Programming Tutorial and Technology Reviews.

www.appsloveworld.com

isladogs · Sep 24, 2023

zezo2021 said:
I think converting JSON is not easy

Correct. That was why I created my own JSON app a few years ago:

JSON Analyse & Transform

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

www.isladogs.co.uk

I did look at both your files but gave up on the first as it was badly formed.
The data would also be denormalised if imported into Access.
I didn't attempt to analyse your second file but at a quick glance it looked more complex.

Edgar_ · Sep 24, 2023

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.

Edgar_ · Sep 24, 2023

Using a different function, I was able to find the problem for the wrong import:

Code:

Sub readmyfile()
    Dim FilePath As String
    Dim RawContent As String
    Dim FileNumber As Integer
    
    FilePath = CurrentProject.Path & "\myjson3.json"

    ' get available number of file
    FileNumber = FreeFile
    Open FilePath For Input As FileNumber

    ' read 100 first chars
    RawContent = Input$(100, FileNumber)
    Close FileNumber

    Debug.Print RawContent
End Sub

JSON:

ï»¿{
"Sheet1": [
    {
        "Serial Number": "9788189999599",
        "Company Name": "TALES OF SHIVA",
        "Emplo

The BOM is not letting you continue the parsing. You have a lot of options.
1. Handle the BOM
2. Change the code that exports this json to an encoding that does not include that
3. Open file, copy paste

Edgar_ · Sep 24, 2023

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.

Code:

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.

Solved import json

Member

Attachments

I’m here to help

Member

Member

Member

Active member

Member

Active member

Member

Member

Attachments

I’m here to help

Member

Attachments

I’m here to help

Attachments

Member

I’m here to help

Member

MVP / VIP

Active member

Active member

Active member

Attachments

Similar threads

Users who are viewing this thread