I have done a bit of searching and am not getting anywhere and I do not know enough about JSON data to know if its a file format issue or something I am doing wrong.
I want to take the details of this JSON feed(this is a sample, the real data could be hundreds of iterations) and parse elements into an access table
[
{
"href": "https://bp105.betapark.co.uk/api/v1/orders/1",
"help": "https://bp105.betapark.co.uk/api/v1/help/orders",
"id": 1,
"order_number": "IN1001",
"channel": {
"id": 0,
"name": "",
"order_id": 0,
"order_number": ""
},
"invoice_number": "",
"order_date": "2017-09-28T14:20:55+01:00",
"invoice_date": "2017-09-28",
"due_date": "2017-09-28",
"delivery_date": "",
"despatch_date": "",
"status": {
"id": 1,
"name": "Pending"
},
"custom_status": {
"id": 0,
"name": ""
},
"paid": true,
"paid_on": "2017-09-28T14:20:55+01:00",
"placed_by": "admin",
"completed_on": "",
"completed_by": "",
"user_id": "0",
"email_address": "christopher.nash@xxxx.co.uk",
"allow_contact": true,
"user_group": {
"id": 0,
"name": ""
},
"account_id": null,
"billing_address": {
"title": "Mr",
"title_id": 1,
"first_name": "Chris",
"last_name": "Nash",
"company_name": "",
"address_line_1": "23 Road",
"address_line_2": "",
"town_city": "Bradford",
"county": "",
"postcode": "BD66 2QH",
"country": "United Kingdom",
"country_code": "GB",
"telephone_number": "01274 885521",
"vat_number": "",
"facebook_id": "",
"reference": ""
},
"delivery_address": {
"title": "Mr",
"title_id": 1,
"first_name": "Chris",
"last_name": "Nash",
"company_name": "",
"address_line_1": "23 Road",
"address_line_2": "",
"town_city": "Bradford",
"county": "",
"postcode": "BD66 2QH",
"country": "United Kingdom",
"country_code": "GB",
"telephone_number": "01274 885521",
"billing_match": true
},
"currency_code": "GBP",
"vat_rate": "20.00",
"vat_base": "inc",
"payment_method": {
"value": "card",
"description": "Credit / Debit Card",
"card_type": "",
"card_digits": false
},
"payment_checks": {
"failures": "0",
"avs_address": "",
"avs_postcode": "",
"cv2": "",
"3ds": ""
},
"payment_id": null,
"payment_total": "0.00",
"payment_type": "",
"payment_taken": "0.00",
"voucher_code": "",
"voucher_total": "0.00",
"points_used": "0",
"points_total": "0.00",
"products": [
{
"href": "https://bp105.betapark.co.uk/api/v1/orders/1/products/1",
"help": "https://bp105.betapark.co.uk/api/v1/help/orders/products",
"id": 1,
"sku": "TEST3",
"variant_id": "",
"product_sku": "TEST3",
"product_id": 46,
"bar_code": "",
"part_number": "",
"title": "Template Product Three",
"variant_title": "",
"category": {
"id": 8,
"title_path": "Skin Care|Watches",
"title": "Watches"
},
"brand": {
"id": 0,
"name": ""
},
"supplier": {
"id": 0,
"name": "",
"drop_shipper": false
},
"intangible": false,
"product_file": {
"value": "",
"url": "",
"downloaded": 0
},
"file_group": {
"id": 0,
"name": ""
},
"bundle": false,
"bundled": {
"parent_id": "0",
"parent_sku": ""
},
"gift_voucher": false,
"weight": "0.000",
"quantity": 1,
"quantity_picked": "0",
"quantity_shipped": "0",
"quantity_refunded": "0",
"warehouse_location": "",
"frequency": "",
"instalments": "0",
"vat_rate": "20.00",
"price_cost_exc_vat": "0.00",
"price_cost_inc_vat": "0.00",
"price_cost_vat": "0.00",
"price_full_exc_vat": "0.00",
"price_full_inc_vat": "0.00",
"price_full_vat": "0.00",
"price_paid_exc_vat": "0.00",
"price_paid_inc_vat": "0.00",
"price_paid_vat": "0.00",
"subtotal_exc_vat": "0.00",
"subtotal_inc_vat": "0.00",
"subtotal_vat": "0.00",
"addition_lines": []
}
],
"subtotal_exc_vat": "0.00",
"subtotal_inc_vat": "0.00",
"subtotal_vat": "0.00",
"discount_total_exc_vat": "0.00",
"discount_total_inc_vat": "0.00",
"coupon_code": "",
"coupon_total_exc_vat": "0.00",
"coupon_total_inc_vat": "0.00",
"weight_total": "0.000",
"shipping_method": {
"id": 1,
"name": "UK Standard",
"reference": "",
"tracking_url": ""
},
"shipping_vat_rate": "20.00",
"shipping_total_exc_vat": "0.00",
"shipping_total_inc_vat": "0.00",
"shipping_total_vat": "0.00",
"total_exc_vat": "0.00",
"total_inc_vat": "0.00",
"total_vat": "0.00",
"refunded_total": "0.00",
"points_earned": "0",
"ip_address": "88.211.116.162",
"device_type": "desktop",
"site_version": "desktop",
"affiliate": {
"id": 0,
"name": ""
},
"referring_domain": "",
"referring_search": "",
"customer_comments": "",
"admin_comments": "",
"admin_comments_public": false,
"modified": "2017-09-28T14:20:55+01:00"
},
{
"href": "https://bp105.betapark.co.uk/api/v1/orders/2",
"help": "https://bp105.betapark.co.uk/api/v1/help/orders",
"id": 2,
"order_number": "IN1002",
"channel": {
"id": 0,
"name": "",
"order_id": 0,
"order_number": ""
},
"invoice_number": "",
"order_date": "2017-09-28T14:58:43+01:00",
"invoice_date": "2017-09-28",
"due_date": "2017-09-28",
"delivery_date": "",
"despatch_date": "",
"status": {
"id": 1,
"name": "Pending"
},
"custom_status": {
"id": 0,
"name": ""
},
"paid": true,
"paid_on": "2017-09-28T14:58:43+01:00",
"placed_by": "admin",
"completed_on": "",
"completed_by": "",
"user_id": "0",
"email_address": "christopher.nash@xxxx.co.uk",
"allow_contact": true,
"user_group": {
"id": 0,
"name": ""
},
"account_id": null,
"billing_address": {
"title": "Mr",
"title_id": 1,
"first_name": "Christopher",
"last_name": "Nash",
"company_name": "",
"address_line_1": "xxxx",
"address_line_2": "",
"town_city": "Bradford",
"county": "",
"postcode": "BD66 2QH",
"country": "United Kingdom",
"country_code": "GB",
"telephone_number": "07522",
"vat_number": "",
"facebook_id": "",
"reference": ""
},
"delivery_address": {
"title": "Mr",
"title_id": 1,
"first_name": "Christopher",
"last_name": "Nash",
"company_name": "",
"address_line_1": "xxxx",
"address_line_2": "",
"town_city": "Bradford",
"county": "",
"postcode": "BD66 2QH",
"country": "United Kingdom",
"country_code": "GB",
"telephone_number": "07522",
"billing_match": true
},
"currency_code": "GBP",
"vat_rate": "20.00",
"vat_base": "inc",
"payment_method": {
"value": "card",
"description": "Credit / Debit Card",
"card_type": "",
"card_digits": false
},
"payment_checks": {
"failures": "0",
"avs_address": "",
"avs_postcode": "",
"cv2": "",
"3ds": ""
},
"payment_id": null,
"payment_total": "0.00",
"payment_type": "",
"payment_taken": "0.00",
"voucher_code": "",
"voucher_total": "0.00",
"points_used": "0",
"points_total": "0.00",
"products": [
{
"href": "https://bp105.betapark.co.uk/api/v1/orders/2/products/2",
"help": "https://bp105.betapark.co.uk/api/v1/help/orders/products",
"id": 2,
"sku": "TEST3",
"variant_id": "",
"product_sku": "TEST3",
"product_id": 46,
"bar_code": "",
"part_number": "",
"title": "Template Product Three",
"variant_title": "",
"category": {
"id": 8,
"title_path": "Skin Care|Watches",
"title": "Watches"
},
"brand": {
"id": 0,
"name": ""
},
"supplier": {
"id": 0,
"name": "",
"drop_shipper": false
},
"intangible": false,
"product_file": {
"value": "",
"url": "",
"downloaded": 0
},
"file_group": {
"id": 0,
"name": ""
},
"bundle": false,
"bundled": {
"parent_id": "0",
"parent_sku": ""
},
"gift_voucher": false,
"weight": "0.000",
"quantity": 1,
"quantity_picked": "0",
"quantity_shipped": "0",
"quantity_refunded": "0",
"warehouse_location": "",
"frequency": "",
"instalments": "0",
"vat_rate": "20.00",
"price_cost_exc_vat": "0.00",
"price_cost_inc_vat": "0.00",
"price_cost_vat": "0.00",
"price_full_exc_vat": "0.00",
"price_full_inc_vat": "0.00",
"price_full_vat": "0.00",
"price_paid_exc_vat": "0.00",
"price_paid_inc_vat": "0.00",
"price_paid_vat": "0.00",
"subtotal_exc_vat": "0.00",
"subtotal_inc_vat": "0.00",
"subtotal_vat": "0.00",
"addition_lines": []
}
],
"subtotal_exc_vat": "0.00",
"subtotal_inc_vat": "0.00",
"subtotal_vat": "0.00",
"discount_total_exc_vat": "0.00",
"discount_total_inc_vat": "0.00",
"coupon_code": "",
"coupon_total_exc_vat": "0.00",
"coupon_total_inc_vat": "0.00",
"weight_total": "0.000",
"shipping_method": {
"id": 1,
"name": "UK Standard",
"reference": "",
"tracking_url": ""
},
"shipping_vat_rate": "20.00",
"shipping_total_exc_vat": "0.00",
"shipping_total_inc_vat": "0.00",
"shipping_total_vat": "0.00",
"total_exc_vat": "0.00",
"total_inc_vat": "0.00",
"total_vat": "0.00",
"refunded_total": "0.00",
"points_earned": "0",
"ip_address": "88.211.116.162",
"device_type": "desktop",
"site_version": "desktop",
"affiliate": {
"id": 0,
"name": ""
},
"referring_domain": "",
"referring_search": "",
"customer_comments": "",
"admin_comments": "",
"admin_comments_public": false,
"modified": "2017-09-28T14:58:43+01:00"
}
]
I want to pull out multiple bits of info such ID, Billing Address Elements, Delivery Address Elements, the products info and a few other.
All the examples I find seem to require each order iteration to be labelled like this one Access VBA to parse json but I can not make it work.
Each of the returned orders and their information does not have a label that I can say for each ... in json do.....
Any help would be fantastic
Thanks in Advance - Chris
