populateJSON acting very slowly...

Skip to first unread message


Mar 19, 2014, 9:22:11 AM3/19/14
to excel-r...@googlegroups.com
I am using the following function from your cDataSet workbook example, with a few changed params.
dSet.populateJSON jo.deSerialize(cb.httpGET(url)).child("entry"), Range("import!$a$1")

Do you know why it might be taking so long? It takes 4-5 minutes to parse 2000 rows x 10 columns into Excel. The JSON from my URL is one massive dump in a single line. It starts populating rows immediately which I can visibly see (changing Application.ScreenUpdating doesnt change anything) but takes a long time to complete. Is this normal/expected?

Otherwise this is a brilliant function - perfect for what I need so Thanks!!!

Bruce Mcpherson

Mar 19, 2014, 10:25:56 AM3/19/14
to excel-r...@googlegroups.com
. did you turn calculate to manual from automatic ? 
. also to encourage the garbage collector between runs for a big dataset like this, you probably need to execute tearDowns() (http://ramblings.mcpher.com/Home/excelquirks/snippets/scope/garbage) , otherwise the memory wont be released till you exit excel

....something like (im just typing this into mail, so there may be some syntax problems..)

with cb
  with JSONParse (.httpGET(url) )
     dSet.populateJSON (.child("entry"), Range("import!$a$1")).teardown()
  end with
end with


You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblin...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


Mar 19, 2014, 12:19:34 PM3/19/14
to excel-r...@googlegroups.com
Perfect, Thanks Bruce.
Here is my whole solution in case others want to use it:

Public Sub extractJson()
    Dim dSet As New cDataSet, cb As New cBrowser, jo As New cJobject, url As String
    On Error GoTo extractJson_err
    'get the url from a named cell range, switch to import sheets
    url = Range("lenniesJsonUrl").Value2
    'Application.ScreenUpdating = False
    Application.Calculation = xlManual
    'fetch the Json from the url, parse it from the object name (entry) and populate the specified range
    With cb
        With JSONParse(.httpGET(url))
            dSet.populateJSON(.child("entry"), Range("import!$a$1")).tearDown
        End With
    End With
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    Exit Sub
    MsgBox "Error: " & vbCrLf & vbCrLf & Err.description
    Resume extractJson_clean
End Sub
Reply all
Reply to author
0 new messages