Foursquare data into Excel

Question

Im attempting to get the results of a foursquare API venue search into excel as a simple table. Im using this request here and saving the results into a file with a .json ending. Ive then tried using several online converters to change it into a CSV/XML to be read by Excel. However, I have only managed to get one website to actually produce the results, and unfortunately they charge. Is there a way to get the results of this query into an Excel table more easily?

What does the result look like? How complex is the json response? Maybe try Codo's answer here: http://stackoverflow.com/questions/5773683/excel-vba-parsed-json-object-loop — Tim Williams, Oct 07 '13 at 16:33
If I paste the json result from above into [this](http://www.utilities-online.info/xmltojson/#.UlPFRVBJPV4) website, and save the resulting XML, then load it into Chrome I get the following error _error on line 5 at column 1: Extra content at the end of the document_ — dojogeorge, Oct 08 '13 at 08:46
Your API link points to a login page, so there's nothing to see there. So far your question is too vague to offer any suggestions. You should include the json result into your question (or if it's too large then a representative part of it) — Tim Williams, Oct 08 '13 at 16:01
[Here](http://pastie.org/8395261) is the example json response. Im trying to get it imported into Excel, but using an XML converter and importing gives me the above error in Chrome when opening the converted XML. — dojogeorge, Oct 11 '13 at 15:31

score 0 · Answer 1 · answered Oct 11 '13 at 18:55

Here's one approach you could take to extract individual elements from the response. It does mean you need to create "accessor" functions in the script control for each property though.

Sub TestIt()

    Dim json As String, x As Long, num As Long

    json = CreateObject("scripting.filesystemobject"). _
           opentextfile("C:\_Stuff\test\json.txt").readall()

    'Debug.Print json

    Dim s As Object, jsObj As Object
    Set s = CreateObject("ScriptControl")
    s.Language = "JScript"

    s.ExecuteStatement "var obj = null;" & vbCrLf & _
         "function JSON(s){obj=eval('('+s+')');}" & vbCrLf & _
         "function numVenues(){return obj.response.venues.length;}" & vbCrLf & _
         "function venueId(i){return obj.response.venues[i].id;}"

    s.Run "JSON", json

    num = s.Run("numVenues") 'how many venues
    Debug.Print num
    For x = 0 To num - 1
        Debug.Print s.Run("venueId", x)
    Next x


End Sub

A more OO approach . Just go array.item(0) see this link (http://stackoverflow.com/questions/5773683/excel-vba-parsed-json-object-loop/19359035#19359035) — ozmike, Oct 14 '13 at 12:49

Foursquare data into Excel

1 Answers1