asked on

JSON import in Access 2016

I have a JSON string that I want to import into a new Access 2016 table.
Please give me an example of how to do this.

JSON:
{"username": "1465", "formal_picture": null, "show_almanac_date_of_birth": true, "payment_start_uri": "", "show_almanac_email": true, "secondary_last_name_main": "", "id": 207080, "custom_fields": null, "show_almanac": true, "first_name": "Pascal ", "show_almanac_phonenumbers": true, "date_of_birth": "1971-07-15", "given_name": "", "has_sdd_mandate": false, "email": "1234567@hotmail.com", "status": "Gast-rijder", "firstname": "Pascal ", "status_id": 1081, "middlename": "van der", "lastname": "mijnnaam", "payment_success_uri": "", "primary_last_name_prefix": "van der", "saldo": 0, "profile_picture": null, "phone_home": {"number_full_MSISDN": "", "number_full": ""}, "address": {"city": "mijnplaats ", "address": "mijnstraat", "zip": "1234AA", "country": "Netherlands"}, "bank_account": {"has_sdd_mandate": false, "iban": "NL11RAB1234567890", "bic": "RABONL2U"}, "secondary_last_name_prefix": "", "show_almanac_addresses": true, "gender": "m", "payment_required": false, "show_almanac_custom_fields": true, "phone_mobile": {"number_full_MSISDN": "31123456789", "number_full": "+31123456789"}, "primary_last_name_main": "mijnnaam", "initials": "P.T.F."}
json.txt

Scott McDaniel (EE MVE )

From which platform would you do the insert? Would you have this string present in an Access application, or in a web-based application.

If you're doing this entirely in Access, you could use the VBA Split function to get each of those Name:Value pairs and insert them.

If your doing this from a web application, then you'd have to tell use more about that web application.

The Split function would be something like this:

Dim arr() As STring
arr = Split(YourJsonString, ",")

Dim i as Integer
For i = 0 to UBound(arr)
Msgbox "First Value: " & Split(arr(i), ":")(0)
Msgbox "First Value: " & Split(arr(i), ":")(1)
Next i

To insert into an Access table, you could use an INSERT INTO statement ... but before we get into that, please let us know the environment you're using.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gustav Brock

Solution provided.