Handling large JSON web response in VBA

Question

I need to create a program that accesses a REST API and imports the JSON response into Excel. I am using XMLHTTP WebRequest to get the response into an string and then use the VBA-JSON Library to parse it.

Sample code:

Dim Url As String
Dim Response As String
Dim HttpReq, ResponseObj As Object
Url = "https://jsonplaceholder.typicode.com/posts"

'Download JSON response and Parse it into an object
Set HttpReq = CreateObject("WinHttp.WinHttprequest.5.1")
HttpReq.SetTimeouts -1, -1, -1, -1
HttpReq.Open "GET", Url, False
HttpReq.Send
Response = HttpReq.ResponseText        'Line with Out of Memory Error
ResponseObj = ParseJson(Response)

'This is followed by further code that places the response rows into Excel Cells

The input is well structured array, similar to the URL used here and the current code works well for majority of requests. However there are some requests where the response is approximately 40 MB where I get an "Out of Memory" error on retrieving the response (Line with reference to HttpReq.ResponseText). I have tried restarting the machine, closing other programs, Excel Safe Mode, etc but the error persists.

The question I have is, is there any way that I can download, parse and import data into Excel in a more memory efficient manner so that I do not run into the memory error above. For reference, I need to use Excel 2010 32 bit as that's the only approved and installed version at my workplace.

If you are in Excel 2013 or later, you might be able to load and parse the JSON file in Power Query, and then load it to a worksheet in the form of a table. Maybe `ParseJson()` performs string concatenation (just guessing, could be wrong) -- which VBA is typically inefficient at doing. — chillin, Feb 17 '18 at 23:15
Could you please share the URL for such large response, for testing and reproducing the error (check [MCVE](https://stackoverflow.com/help/mcve)). Take a look at [this](https://stackoverflow.com/a/34365852/2165759) and [this](https://stackoverflow.com/a/48763323/2165759). — omegastripes, Feb 18 '18 at 00:25
@chillin Do you have any examples of doing this with powerquery please? Many thanks — QHarr, Feb 18 '18 at 09:45
I'm on mobile, so I can't write out the full M code, but I think OP would either need to: (1) save the API's responses to disk, then use `File.Contents` and feed its output to `JSON.Document` function (2) or use `Web.Contents` to request the data (ensuring that the call is authenticated correctly and bears credentials) and then pass its output to `JSON.Document`. The output of `JSON.Document` should consist of records/list, which can then be expanded via the GUI or M code. https://msdn.microsoft.com/en-us/library/mt260861.aspx — chillin, Feb 18 '18 at 11:45
Saving the responses to disk as .json files would allow OP to keep static copies of the responses, meaning refreshing the query doesn't necessarily mean re-requesting the data -- which makes it less likely to run into API quota limits, at the cost of having to store the responses locally. — chillin, Feb 18 '18 at 11:58
@QHarr, you might want to follow this [link](https://www.youtube.com/watch?v=Son4TJQckxE). You can grab any json file the way you want from a webpage using `Power Query`. — SIM, Feb 18 '18 at 14:05
Unfortunately, I have to use Excel 2010 32 Bit as that is the approved and installed version available at my workplace. — RIBH, Feb 18 '18 at 14:07
@RIBH, No problem at all. Here is the [link](https://www.youtube.com/watch?v=gwW2CDdvUUs) to the installation guide. If you have excel 2010 along with 32 bit, then you can have it. — SIM, Feb 18 '18 at 16:14
Thanks SIM. This was super helpful. Tested manually and this worked on a sample in Excel 2010. Going to automate this in VBA. If you could post the PowerQuery solution as an answer, I'll mark it accordingly. — RIBH, Feb 18 '18 at 17:02

score 1 · Answer 1 · answered Feb 17 '18 at 23:44

1

Try the below approach to get the Id,Title and body from that webpage.

Sub Get_data()
    Dim HTTP As New XMLHTTP60, res As Variant
    Dim r As Long, v As Long

    With HTTP
        .Open "GET", "https://jsonplaceholder.typicode.com/posts", False
        .setRequestHeader "User-Agent", "Mozilla/5.0"
        .send
        res = Split(.responseText, "id"":")
    End With

    r = UBound(res)

    For v = 1 To r
        Cells(v, 1) = Split(res(v), ",")(0)
        Cells(v, 2) = Split(Split(Split(res(v), "title"":")(1), """")(1), """,")(0)
        Cells(v, 3) = Split(Split(Split(res(v), "body"":")(1), """")(1), """,")(0)
    Next v
End Sub

Reference to add to the library:

Microsoft XML, V6.0

answered Feb 17 '18 at 23:44

SIM

21,997
5
37
109

I think OP is attempting to parse JSON response, not HTML. Maybe I am wrong. – chillin Feb 18 '18 at 06:48
Oh I see!! What does json response contain, Any data? If his ultimate goal is to reach the data, What the above solution is doing then? – SIM Feb 18 '18 at 06:53
My understanding is your solution expects the response to be HTML and contain ID, Title, Body, but JSON is syntactically different and consists of key-value pairs -- which wouldn't contain IDs, Title, Body. – chillin Feb 18 '18 at 07:11
Yeah, I know about the structural pattern of json. The thing is there is no built-in library in vba to scrape json data. As `VBA-tools/VBA-JSON` is an add-in, so I offered an alternative approach without using any third party tool. That's it. – SIM Feb 18 '18 at 07:20
Sorry, I hadn't checked the contents of `"https://jsonplaceholder.typicode.com/posts"` until now. Your answer makes sense, apologies. – chillin Feb 18 '18 at 08:11
@SIM : The error is in retrieving the content before parsing it. Is there a way to process the JSON data as it arrives instead of waiting for all of it to be loaded into a string. May have to do event based asynchronous processing and split on ID: as you suggest. – RIBH Feb 18 '18 at 13:47

Handling large JSON web response in VBA

1 Answers1