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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Https Response Into Combobox, Access 2010    
 
   
jleach
post Sep 1 2017, 11:48 AM
Post#21


UtterAccess Editor
Posts: 9,745
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#22


UtterAccess Editor
Posts: 9,745
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
 
ridders
post Sep 2 2017, 07:07 PM
Post#23



Posts: 170
Joined: 15-January 17
From: Somerset UK


Hi Jack

Sorry about the delay in replying.

Yes I'm only too aware that the final step of importing into Access tables has to be done on a 'case by case basis'.
My approach is to save the JSON data to a file & read that into Access (or Excel) so I can visualise it & create a suitable table structure for the import.
Where appropriate I split into several tables for normalisation purposes

QUOTE
Typically, I'll see people open a recordset and parse the values directly into the recordset.

That is indeed the approach I use though I understand your reasons for not doing so

QUOTE
1) holding a recordset open during a parsing operations is needlessly tying up locks on the table - good point though in many cases the update can be done at 'quiet' times
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 - I believe I'm doing that with my approach
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 - as I've saved the data to a local JSON text file, that's not an issue
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)

- similar answer as above

As you say the above issues apply to any data import - csv / xml etc

Anyway, I've now successfully imported about 20 JSON files from different sources & with varying structures. Some more easily than others.
For example, I've imported the JSON address data from GetAddress as mentioned by the OP:
CODE
{"latitude":51.4773818,"longitude":-2.5963032,"addresses":["66 Egerton Road,,,,,Bristol,Avon","68 Egerton Road,,,,,Bristol,Avon","70 Egerton Road,,,,,Bristol,Avon","72 Egerton Road,,,,,Bristol,Avon","74 Egerton Road,,,,,Bristol,Avon","76 Egerton Road,,,,,Bristol,Avon","78 Egerton Road,,,,,Bristol,Avon","80 Egerton Road,,,,,Bristol,Avon","82 Egerton Road,,,,,Bristol,Avon","84 Egerton Road,,,,,Bristol,Avon","86 Egerton Road,,,,,Bristol,Avon","88 Egerton Road,,,,,Bristol,Avon","90 Egerton Road,,,,,Bristol,Avon","92 Egerton Road,,,,,Bristol,Avon","94 Egerton Road,,,,,Bristol,Avon","96 Egerton Road,,,,,Bristol,Avon","98 Egerton Road,,,,,Bristol,Avon","100 Egerton Road,,,,,Bristol,Avon","102 Egerton Road,,,,,Bristol,Avon","104 Egerton Road,,,,,Bristol,Avon","106 Egerton Road,,,,,Bristol,Avon","108 Egerton Road,,,,,Bristol,Avon","110 Egerton Road,,,,,Bristol,Avon","112 Egerton Road,,,,,Bristol,Avon","114 Egerton Road,,,,,Bristol,Avon","116-118 Egerton Road,,,,,Bristol,Avon","120 Egerton Road,,,,,Bristol,Avon","122 Egerton Road,,,,,Bristol,Avon","St. Bonaventures Catholic Primary School,Egerton Road,,,,Bristol,Avon","The Presbytery,Egerton Road,,,,Bristol,Avon"]}


This really is a very bad way of displaying data unless all you want is an address line / block
This is a much better alternative for the same addresses from another site Ideal-Postcodes.
Its got a lot more info but crucially, a far better structure

CODE
{"result":[{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"St. Bonaventures Catholic Primary School","udprn":2766905,"umprn":"","postcode_type":"S","su_organisation_indicator":"Y","delivery_point_suffix":"2P","line_1":"St. Bonaventures Catholic Primary School","line_2":"Egerton Road","line_3":"","premise":"","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"","building_name":"The Presbytery","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766904,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"2N","line_1":"The Presbytery","line_2":"Egerton Road","line_3":"","premise":"The Presbytery","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"66","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766918,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1R","line_1":"66 Egerton Road","line_2":"","line_3":"","premise":"66","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"68","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766919,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1S","line_1":"68 Egerton Road","line_2":"","line_3":"","premise":"68","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"70","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766920,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1T","line_1":"70 Egerton Road","line_2":"","line_3":"","premise":"70","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"72","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766921,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1U","line_1":"72 Egerton Road","line_2":"","line_3":"","premise":"72","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"74","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766922,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1W","line_1":"74 Egerton Road","line_2":"","line_3":"","premise":"74","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"76","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766923,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1X","line_1":"76 Egerton Road","line_2":"","line_3":"","premise":"76","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"78","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766924,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1Y","line_1":"78 Egerton Road","line_2":"","line_3":"","premise":"78","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"80","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766925,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1Z","line_1":"80 Egerton Road","line_2":"","line_3":"","premise":"80","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"82","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766926,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"2A","line_1":"82 Egerton Road","line_2":"","line_3":"","premise":"82","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"84","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766927,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"2B","line_1":"84 Egerton Road","line_2":"","line_3":"","premise":"84","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"86","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766928,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"2D","line_1":"86 Egerton Road","line_2":"","line_3":"","premise":"86","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"88","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766929,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"2E","line_1":"88 Egerton Road","line_2":"","line_3":"","premise":"88","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"90","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766930,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"2F","line_1":"90 Egerton Road","line_2":"","line_3":"","premise":"90","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"92","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766931,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"2G","line_1":"92 Egerton Road","line_2":"","line_3":"","premise":"92","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"94","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766932,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"2H","line_1":"94 Egerton Road","line_2":"","line_3":"","premise":"94","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"96","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766933,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"2J","line_1":"96 Egerton Road","line_2":"","line_3":"","premise":"96","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"98","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766934,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"2L","line_1":"98 Egerton Road","line_2":"","line_3":"","premise":"98","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"100","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766906,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1A","line_1":"100 Egerton Road","line_2":"","line_3":"","premise":"100","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"102","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766907,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1B","line_1":"102 Egerton Road","line_2":"","line_3":"","premise":"102","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"104","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766908,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1D","line_1":"104 Egerton Road","line_2":"","line_3":"","premise":"104","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"106","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766909,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1E","line_1":"106 Egerton Road","line_2":"","line_3":"","premise":"106","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"108","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766910,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1F","line_1":"108 Egerton Road","line_2":"","line_3":"","premise":"108","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"110","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766911,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1G","line_1":"110 Egerton Road","line_2":"","line_3":"","premise":"110","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"112","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766912,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1H","line_1":"112 Egerton Road","line_2":"","line_3":"","premise":"112","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"114","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766913,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1J","line_1":"114 Egerton Road","line_2":"","line_3":"","premise":"114","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"","building_name":"116-118","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766914,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1L","line_1":"116-118 Egerton Road","line_2":"","line_3":"","premise":"116-118","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"120","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766916,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1P","line_1":"120 Egerton Road","line_2":"","line_3":"","premise":"120","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"},{"postcode":"BS7 8HP","postcode_inward":"8HP","postcode_outward":"BS7","post_town":"BRISTOL","dependant_locality":"","double_dependant_locality":"","thoroughfare":"Egerton Road","dependant_thoroughfare":"","building_number":"122","building_name":"","sub_building_name":"","po_box":"","department_name":"","organisation_name":"","udprn":2766917,"umprn":"","postcode_type":"S","su_organisation_indicator":"","delivery_point_suffix":"1Q","line_1":"122 Egerton Road","line_2":"","line_3":"","premise":"122","longitude":-2.59630428070796,"latitude":51.4773857770899,"eastings":358684,"northings":175549,"country":"England","traditional_county":"Gloucestershire","administrative_county":"","postal_county":"Avon","county":"Avon","district":"Bristol,City of","ward":"Redland"}],"code":2000,"message":"Success"}



Anyway, the reason I was asking about procedures for importing the parsed data into Access tables was that I was having difficulty with a couple of JSON files.
Both of these looked easy but weren't (for me anyway)

1. Currency Exchange:(now done)

CODE
{"base":"GBP","date":"2017-09-01","rates":{"AUD":1.6314,"BGN":2.1241,"BRL":4.0644,"CAD":1.6106,"CHF":1.2426,"CNY":8.4914,"CZK":28.321,"DKK":8.078,"HKD":10.13,"HRK":8.0603,"HUF":331.35,"IDR":17241.0,"ILS":4.6266,"INR":82.898,"JPY":142.59,"KRW":1450.3,"MXN":23.091,"MYR":5.5285,"NOK":10.052,"NZD":1.807,"PHP":66.142,"PLN":4.6056,"RON":4.9919,"RUB":74.746,"SEK":10.294,"SGD":1.7536,"THB":42.942,"TRY":4.4508,"USD":1.2946,"ZAR":16.745,"EUR":1.0861}}



2. Colour codes: (still to be solved)
CODE
{"aliceblue":"#f0f8ff","antiquewhite":"#faebd7","aqua":"#00ffff","aquamarine":"#7fffd4","azure":"#f0ffff","beige":"#f5f5dc","bisque":"#ffe4c4","black":"#000000","blanchedalmond":"#ffebcd","blue":"#0000ff","blueviolet":"#8a2be2","brown":"#a52a2a","burlywood":"#deb887","cadetblue":"#5f9ea0","chartreuse":"#7fff00","chocolate":"#d2691e","coral":"#ff7f50","cornflowerblue":"#6495ed","cornsilk":"#fff8dc","crimson":"#dc143c","cyan":"#00ffff","darkblue":"#00008b","darkcyan":"#008b8b","darkgoldenrod":"#b8860b","darkgray":"#a9a9a9","darkgreen":"#006400","darkgrey":"#a9a9a9","darkkhaki":"#bdb76b","darkmagenta":"#8b008b","darkolivegreen":"#556b2f","darkorange":"#ff8c00","darkorchid":"#9932cc","darkred":"#8b0000","darksalmon":"#e9967a","darkseagreen":"#8fbc8f","darkslateblue":"#483d8b","darkslategray":"#2f4f4f","darkslategrey":"#2f4f4f","darkturquoise":"#00ced1","darkviolet":"#9400d3","deeppink":"#ff1493","deepskyblue":"#00bfff","dimgray":"#696969","dimgrey":"#696969","dodgerblue":"#1e90ff","firebrick":"#b22222","floralwhite":"#fffaf0","forestgreen":"#228b22","fuchsia":"#ff00ff","gainsboro":"#dcdcdc","ghostwhite":"#f8f8ff","gold":"#ffd700","goldenrod":"#daa520","gray":"#808080","green":"#008000","greenyellow":"#adff2f","grey":"#808080","honeydew":"#f0fff0","hotpink":"#ff69b4","indianred":"#cd5c5c","indigo":"#4b0082","ivory":"#fffff0","khaki":"#f0e68c","lavender":"#e6e6fa","lavenderblush":"#fff0f5","lawngreen":"#7cfc00","lemonchiffon":"#fffacd","lightblue":"#add8e6","lightcoral":"#f08080","lightcyan":"#e0ffff","lightgoldenrodyellow":"#fafad2","lightgray":"#d3d3d3","lightgreen":"#90ee90","lightgrey":"#d3d3d3","lightpink":"#ffb6c1","lightsalmon":"#ffa07a","lightseagreen":"#20b2aa","lightskyblue":"#87cefa","lightslategray":"#778899","lightslategrey":"#778899","lightsteelblue":"#b0c4de","lightyellow":"#ffffe0","lime":"#00ff00","limegreen":"#32cd32","linen":"#faf0e6","magenta":"#ff00ff","maroon":"#800000","mediumaquamarine":"#66cdaa","mediumblue":"#0000cd","mediumorchid":"#ba55d3","mediumpurple":"#9370db","mediumseagreen":"#3cb371","mediumslateblue":"#7b68ee","mediumspringgreen":"#00fa9a","mediumturquoise":"#48d1cc","mediumvioletred":"#c71585","midnightblue":"#191970","mintcream":"#f5fffa","mistyrose":"#ffe4e1","moccasin":"#ffe4b5","navajowhite":"#ffdead","navy":"#000080","oldlace":"#fdf5e6","olive":"#808000","olivedrab":"#6b8e23","orange":"#ffa500","orangered":"#ff4500","orchid":"#da70d6","palegoldenrod":"#eee8aa","palegreen":"#98fb98","paleturquoise":"#afeeee","palevioletred":"#db7093","papayawhip":"#ffefd5","peachpuff":"#ffdab9","peru":"#cd853f","pink":"#ffc0cb","plum":"#dda0dd","powderblue":"#b0e0e6","purple":"#800080","rebeccapurple":"#663399","red":"#ff0000","rosybrown":"#bc8f8f","royalblue":"#4169e1","saddlebrown":"#8b4513","salmon":"#fa8072","sandybrown":"#f4a460","seagreen":"#2e8b57","seashell":"#fff5ee","sienna":"#a0522d","silver":"#c0c0c0","skyblue":"#87ceeb","slateblue":"#6a5acd","slategray":"#708090","slategrey":"#708090","snow":"#fffafa","springgreen":"#00ff7f","steelblue":"#4682b4","tan":"#d2b48c","teal":"#008080","thistle":"#d8bfd8","tomato":"#ff6347","turquoise":"#40e0d0","violet":"#ee82ee","wheat":"#f5deb3","white":"#ffffff","whitesmoke":"#f5f5f5","yellow":"#ffff00","yellowgreen":"#9acd32"}


The parsing code works e.g.

Attached File  Capture2.PNG ( 5.7K )Number of downloads: 0



but for some reason, only the CSS value is imported ....

Attached File  Capture.PNG ( 13.15K )Number of downloads: 0


Its bound to be something simple but its late here & I'm currently bereft of ideas
Will look at it & other examples tomorrow

--------------------
Colin

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10, Website, email
Go to the top of the page
 
ridders
post Sep 12 2017, 02:27 PM
Post#24



Posts: 170
Joined: 15-January 17
From: Somerset UK


For info, I have just uploaded a JSON parser database to Access World Forums sample databases area:
https://www.access-programmers.co.UK/forums...ad.php?t=295789

It includes over 20 JSON files as exemplars that I have successfully parsed using the database and another 10 for others who are interested to try out

You can use online data (using HTTP GET) or JSON files previously saved to your computer

Here is a screenshot:

Attached File  MainForm1.PNG ( 77.36K )Number of downloads: 3




--------------------
Colin

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10, Website, email
Go to the top of the page
 
theDBguy
post Sep 12 2017, 04:47 PM
Post#25


Access Wiki and Forums Moderator
Posts: 70,671
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
 
ridders
post Sep 12 2017, 05:45 PM
Post#26



Posts: 170
Joined: 15-January 17
From: Somerset UK


You're welcome
Hopefully it will be useful to others

--------------------
Colin

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10, Website, email
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    24th September 2017 - 09:09 AM