populateJSON acting very slowly...

31 views
Skip to first unread message

stu.mc...@gmail.com

unread,
Mar 19, 2014, 9:22:11 AM3/19/14
to excel-r...@googlegroups.com
Hello,
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!!!
Cheers
stu

Bruce Mcpherson

unread,
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()
     .tearDown()
  end with
  .tearDown()
end with

bruce




--
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.

stu.mc...@gmail.com

unread,
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
    Sheets("import").Select
    
    '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
            .tearDown
        End With
        .tearDown
    End With
extractJson_clean:
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    Exit Sub
extractJson_err:
    MsgBox "Error: " & vbCrLf & vbCrLf & Err.description
    Err.clear
    Resume extractJson_clean
End Sub
Reply all
Reply to author
Forward
0 new messages