Help with JSON parse (1 Viewer)

ironfelix717

Registered User.
Local time
Today, 12:50
Joined
Sep 20, 2019
Messages
188
Applauding computer science --- we've actually counterproductively made what is supposed to be a universal data structure into a PITA to use.

I am trying to use the bloated JSON parser to grab a value from a JSON string.

Frankly, I don't understand dictionaries that well. And now were gonna nest them - sweet.

Here is the JSON (unformatted crap):

Code:
{"request_info":{"success":true},"account_info":{"api_key":"NoneUrBiz","name":"Johnson","email":"fruit@gmail.com","plan":"Starter","credits_used":3,"credits_remaining":9997,"credits_reset_at":"2022-02-20T22:19:27.000Z","credits_limit":10000,"zipcodes_used":0,"zipcodes_limit":1,"zipcodes_available":1,"account_balance_usd":0,"account_balance_message":"Your Account Balance is used to pay any Overage charges (to avoid multiple small transactions being made to your payment card). You can top up your Account Balance on the Account page of the Dashboard. Your Account Balance is not used for your monthly Plan payment.","destinations_used":0,"destinations_limit":50,"destinations_available":50,"overage_allowed":true,"overage_enabled":false,"overage_limit":20000,"overage_used":0,"collections_used":0,"collections_limit":10000,"collections_available":10000,"usage_history":[{"month":"January","year":2022,"month_number":1,"is_current_month":true,"credits_total_for_month":3,"credits_total_per
_day":{"1":0,"2":0,"3":0,"4":0,"5":0,"6":0,"7":0,"8":0,"9":0,"10":0,"11":0,"12":0,"13":0,"14":0,"15":0,"16":0,"17":0,"18":0,"19":0,"20":3,"21":0,"22":0,"23":0,"24":0,"25":0,"26":0,"27":0,"28":0,"29":0,"30":0,"31":0}},{"month":"December","year":2021,"month_number":12,"is_current_month":false,"credits_total_for_month":0,"credits_total_per_day":{"1":0,"2":0,"3":0,"4":0,"5":0,"6":0,"7":0,"8":0,"9":0,"10":0,"11":0,"12":0,"13":0,"14":0,"15":0,"16":0,"17":0,"18":0,"19":0,"20":0,"21":0,"22":0,"23":0,"24":0,"25":0,"26":0,"27":0,"28":0,"29":0,"30":0,"31":0}},{"month":"November","year":2021,"month_number":11,"is_current_month":false,"credits_total_for_month":0,"credits_total_per_day":{"1":0,"2":0,"3":0,"4":0,"5":0,"6":0,"7":0,"8":0,"9":0,"10":0,"11":0,"12":0,"13":0,"14":0,"15":0,"16":0,"17":0,"18":0,"19":0,"20":0,"21":0,"22":0,"23":0,"24":0,"25":0,"26":0,"27":0,"28":0,"29":0,"30":0}},{"month":"October","year":2021,"month_number":10,"is_current_month":false,"credits_total_for_month":0,"credits_total_per_day":{"1":0,"2":
0,"3":0,"4":0,"5":0,"6":0,"7":0,"8":0,"9":0,"10":0,"11":0,"12":0,"13":0,"14":0,"15":0,"16":0,"17":0,"18":0,"19":0,"20":0,"21":0,"22":0,"23":0,"24":0,"25":0,"26":0,"27":0,"28":0,"29":0,"30":0,"31":0}}],"status":[{"component":"Search Requests","description":"Status of CnodeAPIDataSystems 'type=search' requests","group":"Parsing","status":"operational"},{"component":"Product Requests","description":"Status of CnodeAPIDataSystems 'type=product' requests","group":"Parsing","status":"operational"},{"component":"Offers Requests","description":"Status of CnodeAPIDataSystems 'type=offers' requests","group":"Parsing","status":"operational"},{"component":"Stock Estimation Requests","description":"Status of CnodeAPIDataSystems 'type=stock_estimation' requests","group":"Parsing","status":"operational"},{"component":"Reviews Requests","description":"Status of CnodeAPIDataSystems 'type=reviews' requests","group":"Parsing","status":"operational"},{"component":"Bestmanagers Requests","description":"Status of CnodeAPIDataSystems 'type=bestmanagers' requests","group":"Parsing","sta
tus":"operational"},{"component":"Questions Requests","description":"Status of PULLTERM 'type=questions' requests","group":"Parsing","status":"operational"},{"component":"Category Requests","description":"Status of CnodeAPIDataSystems 'type=category' requests","group":"Parsing","status":"operational"},{"component":"Also Bought Requests","description":"Status of CnodeAPIDataSystems 'type=also_bought' requests","group":"Parsing","status":"operational"},{"component":"manager Profile Requests","description":"Status of CnodeAPIDataSystems 'type=manager_profile' requests","group":"Parsing","status":"operational"},{"component":"Deals Requests","description":"Status of CnodeAPIDataSystems 'type=deals' requests","group":"Parsing","status":"operational"},{"component":"Autocomplete Requests","description":"Status of CnodeAPIDataSystems 'type=autocomplete' requests","group":"Parsing","status":"operational"},{"component":"Author Page Requests","description":"Status of CnodeAPIDataSystems 'type=author_page' requests","group":"Parsing","status":"operational"}]}}


And here is my code using the JSON parser: (Tim Halls Code: https://github.com/VBA-tools/VBA-JSON)


Code:
Dim ret As Dictionary
Dim val As Variant

Set ret = ParseJson(resp)

For Each val In ret("account_info")
    Debug.Print val
Next


This gives...
Code:
api_key
name
email
plan
credits_used
credits_remaining
credits_reset_at
credits_limit
zipcodes_used
zipcodes_limit
zipcodes_available
account_balance_usd
account_balance_message
destinations_used
destinations_limit
destinations_available
overage_allowed
overage_enabled
overage_limit
overage_used
collections_used
collections_limit
collections_available
usage_history
status


I need to be able to grab the values of these properties! How do I do this.



Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:50
Joined
Oct 29, 2018
Messages
19,135
Hi. Not in front of a computer now, but have you tried?

Debug.Print val(0)

If that doesn't work, you could try using a different variable name, since Val() is a built in VBA function.

Hope that helps...
 

ironfelix717

Registered User.
Local time
Today, 12:50
Joined
Sep 20, 2019
Messages
188
Hi. Not in front of a computer now, but have you tried?

Debug.Print val(0)

If that doesn't work, you could try using a different variable name, since Val() is a built in VBA function.

Hope that helps...

@theDBguy

Yep tried that... As for 'Val' i've been using it for a long time and never had an issue. Unsure if 'val' is an actual keyword, if it is, i still probably won't change ;)

Maybe its the JSON parser. Have no idea how i'm supposed to grab a property's value.
Hey but the entire module of code should be able to parse out a few simple lines of JSON right? .... L O L
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:50
Joined
May 7, 2009
Messages
16,779
i tested it but the parser says it "has no row"?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:50
Joined
May 7, 2009
Messages
16,779
create a New Text Document.txt to your Desktop and paste your json string and close.
and run StoretHTTPToTextFile() (in ModJson).
you will see the Output in Result sheet.
 

Attachments

  • Json-Data-to-Excel.zip
    45.3 KB · Views: 214

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:50
Joined
Jan 20, 2009
Messages
12,537
Yet another of hundreds of reasons developers would be advised to progress into SQL Server. It has native JSON capabilities.
 

isladogs

CID VIP
Local time
Today, 17:50
Joined
Jan 14, 2017
Messages
16,377
Tim Hall's code is a good starting point for handling JSON files but it can be defeated by even simple files that are badly structured.
I have my own app for JSON: https://www.isladogs.co.uk/json-analyse-transform/

I read in your file with no issues. According to this, it apparently has 42 fields and one subarray.

1642755821475.png


See PDF report attached

EDIT:
Your file includes 30 fields with numbers as field names - 2 through to 31.
That indicates a poorly structured source file and the number fields will cause issues for Access
 

Attachments

  • rptJSONFileStructure.pdf
    183.3 KB · Views: 232
Last edited:

ironfelix717

Registered User.
Local time
Today, 12:50
Joined
Sep 20, 2019
Messages
188
@isladogs i saw your app and didn't download it. I need functions not apps. However, I will analyze it and see if I can use any of the code.

As for Tim Halls code... That many lines and there's no check on the structure of the file? Nice.

As for the JSON file, I have no control over its format. Have to work with what I'm given.

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:50
Joined
Oct 29, 2018
Messages
19,135
As for Tim Halls code... That many lines and there's no check on the structure of the file? Nice.
I use it fine. Try changing your code from this:
Code:
    Debug.Print val
to this:
Code:
    Debug.Print ret("account_info")(val)
Hope that helps...
 

isladogs

CID VIP
Local time
Today, 17:50
Joined
Jan 14, 2017
Messages
16,377
Two other methods of handling JSON files are to use Get & Transform AKA PowerQuery in Excel or Powershell.
No code needed but there is a steep learning curve in each case.
 

ironfelix717

Registered User.
Local time
Today, 12:50
Joined
Sep 20, 2019
Messages
188
OK still having troubles with this. For the sake of everyones sanity - I can grab the value I need from this particular string no problem. But the project I just took on (unprofessional) requires a lot of JSON interaction and I need to be able to easily access/store data with some level of reliability - with (hopefully) not having to learn the entire JSON spec. I just need to grab some data, but do it often.

@isladogs I haven't got a chance to review your file yet.

@arnelgp Your parser at least runs on my file. And its pretty simple and understandable. Worst case scenario I can build around it to suit my needs to do 'rough queries' on the JSON. However your parser did not parse correctly in my case. Its almost as if it stopped a "level" short in the file.

Here is an example of output on the file in question:

Code:
js.LoadTheJSONString (resp)  'resp = string var of HTTPGET call to my API

If js.err <> 1 Then
    'fail
    MsgBox "Failed"
Else
    For i = 1 To js.NumElements
        Debug.Print js.Key(i) & "==" & js.Value(i)
    Next i
End If


OUTPUT:
>account_info>usage_history>month==January  'valid
>account_info>usage_history>year==2022  'valid
>account_info>usage_history>month_number==1  'valid
>account_info>usage_history>is_current_month==true  'valid
>account_info>usage_history>credits_total_for_month==4  'incorrect
>account_info>usage_history>credits_total_per_day>1==0   'incorrect...
>account_info>usage_history>credits_total_per_day>2==0
>account_info>usage_history>credits_total_per_day>3==0
>account_info>usage_history>credits_total_per_day>4==0
>account_info>usage_history>credits_total_per_day>5==0
>account_info>usage_history>credits_total_per_day>6==0
>account_info>usage_history>credits_total_per_day>7==0


@arnelgp Any ideas?

Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:50
Joined
May 7, 2009
Messages
16,779
on the Excel sheet that i posted:

Screenshot 2022-01-22 125827.png
 

ironfelix717

Registered User.
Local time
Today, 12:50
Joined
Sep 20, 2019
Messages
188
Update:

The API i am interfacing with has a CSV data return option. So, i'm no longer even attempting to waste time with JSON.

Your parser does work pretty decent though.

Thanks for the help - closing thread.
 

Users who are viewing this thread

Top Bottom