Скачать или смотреть AL: Parsing Data using JSON and VBA in Access, Neil Sargent and Leo (the DBguy)

Excellent presentation by Neil Sarget and Leo (the DBguy) about parsing data from the web in JSON format using VBA in Access. Hosted by Maria Barnes.

Background Last month's presentation
Using Dictionaries and Collections to work with JSON in Access, Maria Barnes, Neil Sargent, Leo theDBguy (1:03:47)
   • AL:Using Dictionaries and Collections to w...  

Download
2 PDFs + ACCDB in ZIP
https://accessusergroups.org/lunch/wp...

0:32 Welcome from Maria
0:49 last month's presentation video link above
1:29 Neil Sargent Difficulties with Tim Hall's tool
1:49 reference video: AL: Working with the Microsoft Graph API from VBA with Maria Barnes (1:09:49)
   • AL: Working with the Microsoft Graph API f...  
2:32 Get method to get data from URL
downloaded typically using JSON, sometimes XML
3:09 last month, looked at what JSON is
in VBA, handle it as a set of dictionary objects
VBA collections to represent arrays
3:45 this month, how we took the VBA tools and what to tweak to make it work
4:42 Tweaks to Tim Hall’s VBA-JSON tool that Neil made
5:07 WebClient: Default to connect with TLS1.2
6:10 WebRequest: Support for logging of http request
7:13 WebResponse: Support for logging of http response
7:42 ConvertToJson(): option NoEscapeSolidus to EscapeSolidus
8:33 ConvertToJson(): New optional parameter to expand JSON strings
9:41 ParseJson() + ConvertToJson(): Support for null
11:01 URLEncode(): Support for multibyte Unicode characters
12:05 Tweaks
12:34 Problems with JSON and Tim Hall’s VBA-JSON tool
13:44 Support for multibyte Unicode characters
form in Access with Text, URL Encode, URL Decode
14:09 VBA-JSON library from Tim Hall
https://github.com/VBA-tools/VBA-JSON/
14:22 VBA-Web library does more, super-set, has tools to request and post
https://github.com/VBA-tools/VBA-Web
15:14 back to Access form
(WebHelpers module in Neil's download - translate into web-friendly encoded data)
for instance, space to %20
16:34 Decode works the other way
16:59 control characters that web wants to use like . , ? %
space is %20, comma is %2C
17:48 unicode character comes back as ?
VBE environment doesn't support Unicode, just ANSI
18:03 failed to encode Greek Sigma properly
18:37 textboxes support Unicode
18:49 %3F not right
18:54 characters should be encoded but aren't
20:01 VBA UrlEncode function
instructions in comments
22:41 read 1 character at a time
23:02 AscW instead of Asc
26:48 WideCharToMultiByte
25:35 How to convert VBA/VB6 Unicode strings to UTF-8
https://www.di-mgt.com.au/howto-conve...
25:54 StrConv problems
26:58 modUTF, Utf8BytesFromString
28:52 video: Windows API in VBA - Strings (Part 2) - Unicode vs. ANSI
Philipp Stiefel, codekabinett.com.
   • Windows API in VBA - Strings (Part 2) - Un...  
29:35 summary convert string to byte array
30:41 textboxes show Unicode properly encoded and decoded
32:00 smiley face is 3 bytes encoded
32:18 vba, Utf8BytesToString
33:27 form examples
33:59 vba, multi byte string back, tack it on, job done.
34:07 Q: Adrian
36:02 Default support for TLS1.2
Modify WebClient.PrepareHttpRequest() to use TLS1.2 by default
https://support.microsoft.com/en-gb/t...
36:30 vba, modify WebClient module
37:14 Support for Null
38:32 Resources
38:53 Maria, thank you, take it away Leo
39:00 Leo thank Neil for fascinating deep dive into Tim Hall's library
39:10 *Alternatives & Comparisons*, Parsing JSON data using VBA
39:42 Available Options
40:07 JSON Converter
Also known as VBA-JSON
GitHub
https://github.com/VBA-tools/VBA-JSON
41:39 Characteristics
Widely used and more popular
Failed to parse some valid JSON input
Initially had problems with speed
Requires a reference to Microsoft Scripting Runtime
42:30 JSON Parser
More powerful
Uses the built-in Microsoft JavaScript Engine
Dymeng (Jack Leach)
https://dymeng.com/parsing-json-with-...
UA thread more information
https://www.utteraccess.com/topics/20...
44:06 Characteristics
Cumbersome to work with
Parses all valid JSON
Vulnerable to some security threats
Uses late-binding for Microsoft Script Control
45:05 JSON Interpreter
Object-based (using Class Modules)
GitHub
https://github.com/erikvona/JSONInter...
Usage
Dim jsi As New JSONInterpreter
jsi.JSON = strJSON
45:44 Characteristics
Object-oriented approach
Extremely fast
Parses all JSON
Uses late-binding for Microsoft Scripting Runtime
favorite is Exists
48:30 Syntax Comparisons
51:38 (Non-Scientific) Speed Comparisons
53:44 Maria, March cancelled
54:19 Q: Neil: Does this do JSON encoding as well as decoding?
58:00 Colin, JSON values either strings or numbers?
58:18 Neil, not correct
null , in lower case without quotes around it, supported by JSON
(covered in next video for Access Lunchtime)

Next:

JSON After Party video
2-way exchange, Get AND Post

NOTE: March meeting cancelled, April in air,
stay tuned!