UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Https Response Into Combobox, Access 2010    
 
   
andywal6
post Aug 25 2017, 06:42 AM
Post#1



Posts: 110
Joined: 11-January 08



Hi All

Below is the Response from a GET call to an api like this: GET https://api.getAddress.io/find/nn13er?api-key={api-key}

Can anyone help me to populate a Combobox with this result and get rid of the latitude, longtitude and 'addresses' bits.
from there, on selection, I will insert the relevant columns into the Address fields on my Form.

I hope someone can help and my thanks for reading this strange request.

Andy

CODE
{
    "latitude": 52.24593734741211,
    "longitude": -0.891636312007904,
    "addresses":
    ["10 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "12 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "14 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "16 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "18 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "2 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "20 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "22 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "24 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "26 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "26a Watkin Terrace, , , , , Northampton, Northamptonshire",
    "26b Watkin Terrace, , , , , Northampton, Northamptonshire",
    "26c Watkin Terrace, , , , , Northampton, Northamptonshire",
    "26d Watkin Terrace, , , , , Northampton, Northamptonshire",
    "28 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "2a Watkin Terrace, , , , , Northampton, Northamptonshire",
    "30 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "32 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "36 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "38 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "4 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "40 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "40b Watkin Terrace, , , , , Northampton, Northamptonshire",
    "42 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "44 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "46 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "48 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "50 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "8 Watkin Terrace, , , , , Northampton, Northamptonshire",
    "Flat 1, 6 Watkin Terrace, , , , Northampton, Northamptonshire",
    "Flat 1, Watkin Court, Watkin Terrace, , , Northampton, Northamptonshire",
    "Flat 2, 6 Watkin Terrace, , , , Northampton, Northamptonshire",
    "Flat 2, Watkin Court, Watkin Terrace, , , Northampton, Northamptonshire",
    "Flat 3, 6 Watkin Terrace, , , , Northampton, Northamptonshire",
    "Flat 3, Watkin Court, Watkin Terrace, , , Northampton, Northamptonshire",
    "Flat 4, 6 Watkin Terrace, , , , Northampton, Northamptonshire",
    "Flat 4, Watkin Court, Watkin Terrace, , , Northampton, Northamptonshire",
    "Flat 5, Watkin Court, Watkin Terrace, , , Northampton, Northamptonshire",
    "Flat 6, Watkin Court, Watkin Terrace, , , Northampton, Northamptonshire",
    "Flat 7, Watkin Court, Watkin Terrace, , , Northampton, Northamptonshire",
    "Flat 8, Watkin Court, Watkin Terrace, , , Northampton, Northamptonshire",
    "Flat 9, Watkin Court, Watkin Terrace, , , Northampton, Northamptonshire"]
}
Go to the top of the page
 
jleach
post Aug 25 2017, 07:06 AM
Post#2


UtterAccess Editor
Posts: 9,814
Joined: 7-December 09
From: Staten Island, NY, USA


Sounds like you need a JSON parser: https://dymeng.com/parsing-json-with-VBA/

Here's a couple of text files with some sample JSON that the code in the blog post corresponds to (although you can use it for any valid JSON).

Also included is a demo which contains that code from a presentation I have on the topic earlier this year. Good luck!
Attached File(s)
Attached File  json_demo.zip ( 153.45K )Number of downloads: 14
 

--------------------
Go to the top of the page
 
andywal6
post Aug 25 2017, 10:45 AM
Post#3



Posts: 110
Joined: 11-January 08



Thank you for your suggestion Jack. I down loaded the zip and opened the modules and the json examples
but I could read that all day for a week and still not know how to put those values into a ComboBox on an Access Form.

Andy.

For those who know, it's easy. The rest of us are totally baffled. ^_^
Go to the top of the page
 
jleach
post Aug 25 2017, 10:58 AM
Post#4


UtterAccess Editor
Posts: 9,814
Joined: 7-December 09
From: Staten Island, NY, USA


Sorry, hadn't meant to imply that it'd be easy smile.gif

What you have is a JSON response from the server (JavaScript Object Notation). It has two core constructs: {} and []. {} is an object, thus you have one object in your result. Within that object are properties (latitude, longitude, addresses).

[] indicates an array. The addresses property is an array of strings.

That's JSON. If you check the examples, you'll see a similar format: {} objects and [] arrays and property names and values (objects and arrays can be nested within each other).

Take the sample function that parses the JSON and run it against your JSON and you should get an output of all property values. (the JSON parser returns a VBA object which you can then "tap into" to get the values: the second part of the demo walks through that and outputs all of the property names and values). Then you need to "walk the tree" so to speak to determine how to access the strings you need (the contents of the addresses array) from the object that the parser returns.

Once you can do that, you take your strings and stuff them into a VBA array or one big delimited string. Then you can ditch all the JSON stuff and split the strings up and feed them to the combobox just as you would with any other type of strings (usually via value list, but there's numerous ways).

hth - I have a meeting in 2min else I'd dig in a bit further.

--------------------
Go to the top of the page
 
jleach
post Aug 26 2017, 05:33 AM
Post#5


UtterAccess Editor
Posts: 9,814
Joined: 7-December 09
From: Staten Island, NY, USA


I've modified the parser demo to work for your needs.

Open the attached file and look in the JsonParseDemo module and you'll see three functions.

RunThisFirst shows you how the parser can traverse the json document you supply it (this could be in the form of a string, it doesn't have to come from a file - whitespace is ignored with json).

RunThisSecond shows you how we can drill into just the values we need (the addresses)

And finally, the GetAddressesFromJSON function returns a delimited list of all address values from the json content, which you can then take and do your normal VBA string manipulation routes against to pretty it up for your combobox.

Attached File  example.zip ( 113.72K )Number of downloads: 14


Hope this helps (and hopefully it gives some insight as to how to work with JSON).

Cheers,

--------------------
Go to the top of the page
 
jleach
post Aug 27 2017, 08:37 AM
Post#6


UtterAccess Editor
Posts: 9,814
Joined: 7-December 09
From: Staten Island, NY, USA


Good points all around. For one-off processing it's likely to be much easier to use some sort of converter to get the JSON into a format more easily consumed by Access (VBA really isn't good for this). For a repeatable, automated process, using a separate utility may be more trouble than it's worth though.

Colin, just a quick note: I was reading through your thread at AWF and noticed that you mentioned Tim Hall's VBA-JSON library on github. This was the first place I had wound up when I started having to deal with this as well, but that implementation is unfortunately flawed and breaks on more complex JSON. I ran into this helping someone with a while back and went in search of the issue, which lead me into quite the rabbit hole. What I found was that a) the nature of the issue is not easy to fix, and b) the algorithm used by his code is used by a number of other similar implementations that can be found (apparently written long ago and re-used by many). As a result, most JSON parsers for VBA are similarly broken. The MS Script Engine solution I posted is the only one I've found (out of about 20 or so that I tested during this adventure) that correctly parses any valid JSON format. Unfortunately the interface for it isn't nearly so nice to use as Tim's, but it works.

Anyway, wanted to throw that out there - Tim has some really great stuff in his repo but that one in particular can fall over with valid data. One of these days I suppose I ought to work up a repro and file an issue for it.

Cheers,

--------------------
Go to the top of the page
 
theDBguy
post Aug 27 2017, 02:25 PM
Post#7


Access Wiki and Forums Moderator
Posts: 71,233
Joined: 19-June 07
From: SunnySandyEggo


Hi Jack,

Earlier when I was looking for a JSON parser, I saw this thread at StackOverflow. What caught my attention was this quoted part:

QUOTE
Note that the above approach makes the system vulnerable in some cases, since it allows the direct access to the drives (and other stuff) for the malicious JS code via ActiveX's. Let's suppose you are parsing web server response JSON, like JsonString = "{a:(function(){(new ActiveXObject('Scripting.FileSystemObject')).CreateTextFile('C:\\Test.txt')})()}". After evaluating it you'll find new created file C:\Test.txt. So JSON parsing with ScriptControl ActiveX is not a good idea.

It scared me then, so I avoided using ScriptControl and ended up using VBA-JSON because of it.

What is your opinion about the quoted message above?

Just curious...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
jleach
post Aug 27 2017, 05:18 PM
Post#8


UtterAccess Editor
Posts: 9,814
Joined: 7-December 09
From: Staten Island, NY, USA


Re: DbGuy, quoted part: yes, very relevant (and that is where the code from my demo came from). It uses eval(), which is not considered to be the safest by any means. With that said, most JavaScript engines will prevent that type of scripting (wouldn't want your browser to run some injected script like that), but I'm not sure what the exact case would be in this scenario. One would want to do their best to ensure the JSON is safe before running this with my demo above. Good catch.

Re: Colin, failing JSON: I'll try to dig it up tomorrow, I have it in my initial notes somewhere, but I think it might have been as simple as a few nested objects. There were two or three times I was able to make it fail (I tested pretty thoroughly prior to my presentation on it). I'll look it up.

--------------------
Go to the top of the page
 
jleach
post Aug 27 2017, 05:32 PM
Post#9


UtterAccess Editor
Posts: 9,814
Joined: 7-December 09
From: Staten Island, NY, USA


I found the file but can't post it due to the data. The structure is fairly simple though:

CODE
[
  {
    "customer": {
      "first_name": "blah",
      "last_name": "blah"
    }
  },
  {
    "customer": {
      "first_name": "blah",
      "last_name": "blah"
    }
  },
  {
    "customer": {
      "first_name": "blah",
      "last_name": "blah"
    }
  }
]


It's an odd though valid structure. I forget if there's a specific construct that caused the failure.

I just grabbed this off my network and don't have tools in front of me to test again, but thought I'd toss it out here anyway.

--------------------
Go to the top of the page
 
jleach
post Aug 28 2017, 09:36 AM
Post#10


UtterAccess Editor
Posts: 9,814
Joined: 7-December 09
From: Staten Island, NY, USA


>> would have expected <<

Yes, that certainly would have been a bit more practical. If there's one thing I've learned doing boatloads of web service work though is that we're completely at the mercy of whatever service we're working with.

I had even spent some time poring through the code to see if I could find to fix it, but I apparently didn't have the required ambition because I gave up on it.

I think it mostly boils down to a "human" implementation of a parser (versus a big-company team-based parsing engine with hundreds of man-hours put into making sure that every nuance is covered).

The point that theDbGuy brings up in terms of using eval() as a script engine requires is pretty considerable as well though. Still no 100% through and through, worry-free solution, it seems.

As much as I like working with JSON in frontend web development, as a data transfer object I've always preferred the parsability (and queryability) of XML (and that goes for any language I've work with, not just Access/VBA).

Cheers,

(I'll be curious to see any videos you come up with on the topic: we run into this a lot but there's little community support for doing this type of work and most people seem to be very much in the dark about how to even approach it, and I expect that the situation will only become more common as XML continues to take a back seat (or no seat at all))

--------------------
Go to the top of the page
 
andywal6
post Aug 31 2017, 09:45 AM
Post#11



Posts: 110
Joined: 11-January 08



Hi All

Sorry Jack for the silence, had to take the other half away for a few days.

I just wanted to say thank you Jack for your examples and demos.

Working with them I was able to do exactly what my client wanted and he is well pleased with the solution.

I also now have a better understanding of json structure and how to disassemble it.

Interesting reading from Ridders and DBGuy I had checked out many of the suggestions in their replies but was still connfused.

Ridders is correct about getaddress.io data.

It's good to see you all mulling over problems you may have dumped long ago.

I must have learnt something as when I looked at your Customer example, my first thought was that doesn't look like a good json structure.

You learn something new every day, so I'll continue studying. And with you guys out there one day I'll be able to repair a puncture on my bike. grin.gif

Again, Many thanks to Jack and all the guys who care enough to help us out.

Andy ^_^
Go to the top of the page
 
JonSmith
post Aug 31 2017, 11:35 AM
Post#12



Posts: 3,161
Joined: 19-October 10



Oooo.

I am really interested in this discussion guys.
I also recently searched for a JSON parser and came across the ones listed and found issues with them. One was problematic for me as it made nested dictionary objects and I found them inflexible.

I got fed up and wrote a converter from JSon to XML. Its a pain to do as in previous ones I saw basically went character by character. I tried to be abit more efficient and look for delimiters.
Took a while and I got it working and can now handle complex data in Json. I will try it with the 'problem Json' and see how it breaks mine and if I can fix it. You never know, mine might even work first time!!!
Go to the top of the page
 
jleach
post Sep 1 2017, 07:32 AM
Post#13


UtterAccess Editor
Posts: 9,814
Joined: 7-December 09
From: Staten Island, NY, USA


I've used this for testing: http://json.org/example.html

And a search around landed this, which might be fun to run through: https://adobe.github.io/Spry/samples/data_r...aSetSample.html

Some more searching and I found this, which struck me as a good sample as it uses character escaping for the quotes (which could be quite the bag for worms, JavaScript escaping is a little quirky):

CODE
{
  "kind": "youtube#searchListResponse",
  "etag": "\"m2yskBQFythfE4irbTIeOgYYfBU/PaiEDiVxOyCWelLPuuwa9LKz3Gk\"",
  "nextPageToken": "CAUQAA",
  "regionCode": "KE",
  "pageInfo": {
    "totalResults": 4249,
    "resultsPerPage": 5
  },
  "items": [
    {
      "kind": "youtube#searchResult",
      "etag": "\"m2yskBQFythfE4irbTIeOgYYfBU/QpOIr3QKlV5EUlzfFcVvDiJT0hw\"",
      "id": {
        "kind": "youtube#channel",
        "channelId": "UCJowOS1R0FnhipXVqEnYU1A"
      }
    },
    {
      "kind": "youtube#searchResult",
      "etag": "\"m2yskBQFythfE4irbTIeOgYYfBU/AWutzVOt_5p1iLVifyBdfoSTf9E\"",
      "id": {
        "kind": "youtube#video",
        "videoId": "Eqa2nAAhHN0"
      }
    },
    {
      "kind": "youtube#searchResult",
      "etag": "\"m2yskBQFythfE4irbTIeOgYYfBU/2dIR9BTfr7QphpBuY3hPU-h5u-4\"",
      "id": {
        "kind": "youtube#video",
        "videoId": "IirngItQuVs"
      }
    }
  ]
}


Here's one for transferring between common NoSQL dbs, nice to make sure a key with an underscore is valid (underscores have special meaning in the COM world, but this should be ok):

CODE
[{
  "_id": {
    "$oid": "5968dd23fc13ae04d9000001"
  },
  "product_name": "(I am a spammer)",
  "supplier": "Wisozk Inc",
  "quantity": 261,
  "unit_cost": "$10.47"
}, {
  "_id": {
    "$oid": "5968dd23fc13ae04d9000002"
  },
  "product_name": "Mountain Juniperus ashei",
  "supplier": "Keebler-Hilpert",
  "quantity": 292,
  "unit_cost": "$8.74"
}, {
  "_id": {
    "$oid": "5968dd23fc13ae04d9000003"
  },
  "product_name": "Dextromathorphan HBr",
  "supplier": "Schmitt-Weissnat",
  "quantity": 211,
  "unit_cost": "$20.53"
}]

--------------------
Go to the top of the page
 
jleach
post Sep 1 2017, 07:36 AM
Post#14


UtterAccess Editor
Posts: 9,814
Joined: 7-December 09
From: Staten Island, NY, USA


Andy, glad to help. I'm always happy to promote knowledge of the topic, as it becomes more and more prevalent (not just JSON, but web service work in general).

Jon - usually I avoid transformations if they're extra steps and not really required, but a good JSON to XML converter actually sounds good. I hate that there's no XPath equivalent for JSON. I imagine a home-built converter would be susceptible to many of the same issues as a home built parser, but still handy I bet.

Cheers,

--------------------
Go to the top of the page
 
jleach
post Sep 1 2017, 11:48 AM
Post#15


UtterAccess Editor
Posts: 9,814
Joined: 7-December 09
From: Staten Island, NY, USA


>> Does it contain (or do you have) code for saving the parsed data to normalised Access tables? <<

Nothing generic. I think this would have to be done on a case-by-case basis. For one, there's nothing to say that the JSON will be received in a format that can be normalized (at least not without some human intervention), and for two you would (almost) have to know the expected format so it could be parsed into an existing table accordingly.

An alternative may be to read through the JSON structure and create tables/fields/constraints on the fly to store it (which is where the generic part would come in), but given that a) you can't be sure the JSON structure "fits" a normalized table structure (think nested objects, or objects of the same type placed in different places throughout the JSON), and b) you couldn't hope to do much of anything useful with the resultant dynamically-created tables even if we did take the trouble to create them (if we don't know the structure upfront, we can't use them for a form/report source, etc), I wouldn't think it'd be worthwhile to attempt a generic/dynamic storage approach.

This is not an issue exclusive to Access/VBA (or even JSON: XML requires the same considerations). Most of my work is in .NET and even there I have to have a "gatekeeper" of sorts that's responsible for transforming incoming JSON (or, more accurately, any external data) into a "known" format that the rest of the system can rely on. While I can easily parse any given JSON string into a dynamic object in .NET, there's no guarantee that such an object would be in a state that's useful to the rest of the project, and as such any incoming data must be "scrubbed and typed" at it's entry point (this is also a good opportunity to make use of "interfacing practices" (.NET or VBA or whatever) so that if the structure changes, the transformer can be swapped out with no ill-effect on the parts of the system that ultimately consume it).

So, no.

With that said, what use case would you have for wanting this? Naturally once the required data structure is known, there's a table (or set of tables) that it usually winds up in, but the creation of those as well as the parsing details to get the VBA-JSON-result are case-by-case (which is why I didn't bother to put it in a demo of some sort).

Cheers,

--------------------
Go to the top of the page
 
jleach
post Sep 1 2017, 11:57 AM
Post#16


UtterAccess Editor
Posts: 9,814
Joined: 7-December 09
From: Staten Island, NY, USA


To further this "gatekeeper" concept, my usual implementation is to come up with a set of User Defined Types to hold all of the data I'm parsing.

Typically, I'll see people open a recordset and parse the values directly into the recordset. I've opted not to do this for a few reasons:

1) holding a recordset open during a parsing operations is needlessly tying up locks on the table
2) by stuffing the values into a set of UDTs, you have the full benefit of being able to fully validate everything before you move them further into the system
3) often times getting a complete set of data requires multiple calls to the external service (for pagination/throttling reasons), so I definitely don't want to be holding a recordset open while I wait for another call to come back from over the wire
4) if something goes wrong halfway through, I don't have to worry about cleaning up records that I may have inserted already while having not completed the operation (e.g., transaction management)


So, once I'm aware of the data structure I need to extract from the (external source), I create UDTs as applicable to store the information. Then I parse the data to the UDTs, either validating as I go, or after I'm done. Once I have a solid, satisfactory set of UDTs, I can write them into tables in one quick shot.

The only possible drawback to this is if the full collection of data in an array of UDTs would have such a memory footprint as to cause problems, in which case I'd probably end up giving it a "special consideration" approach (likely writing each web service call's results into a temp table), but I've never come close to this before.


(One of my more recent XML projects I inherited would import bulk data from an online shopping cart: the data had to be fetched in 5 or 6 calls to the service due to volume, and the prior developer had the table/recordset open the whole time while they parsed the XML, from the initial call to completion. The table was *the* core table in the application, and each time this was run (luckily only weekly), the process locked up the tail pages of the table for a good ten seconds or so. Refactoring this to use the UDT-then-table approach took the lock impact down to about a quarter of second!)

--------------------
Go to the top of the page
 
theDBguy
post Sep 12 2017, 04:47 PM
Post#17


Access Wiki and Forums Moderator
Posts: 71,233
Joined: 19-June 07
From: SunnySandyEggo


Hi Colin,

Thanks for sharing! thumbup.gif

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 11:27 PM