Can't get the data in a customized format

[shahin]

My script can parse the data from a link containing json response. The problem I'm facing is that the data available there are comma delimited. For example, I wanted to collect them like:

col1   col2   col3   col4
A        B       C       D   ''Four different cells

The way I'm having (the data are there in comma delimited):

col1
A,B,C,D     ''In a single cell, as in A1

Link to the content: "http://stats.nba.com/stats/playerda...e=Playoffs&TeamID=0&VsConference=&VsDivision="

This is my script so far:

Sub FetchData()
    Const Url As String = "http://stats.nba.com/stats/playerdashptshots?DateFrom=&DateTo=&GameSegment=&LastNGames=6&LeagueID=00&Location=&Month=0&OpponentTeamID=0&Outcome=&PerMode=PerGame&Period=0&PlayerID=2544&Season=2017-18&SeasonSegment=&SeasonType=Playoffs&TeamID=0&VsConference=&VsDivision="
    Dim Http As New XMLHTTP60, SC As Object, elem As Object
    Dim results As Object, post As Object, R&

    Set SC = CreateObject("ScriptControl")
    SC.Language = "JScript"

    With Http
        .Open "GET", Url, False
        .send
        Set results = SC.Eval("(" + .responseText + ")")

        For Each post In CallByName(results, "resultSets", VbGet)
            If CallByName(post, "name", VbGet) = "ClosestDefender10ftPlusShooting" Then
                [A1] = CallByName(post, "headers", VbGet)
                For Each elem In CallByName(post, "rowSet", VbGet)
                    R = R + 1: Cells(R + 1, 1) = elem
                Next elem
            End If
        Next post
    End With
End Sub

How can I get the data (meant to spread across) in different columns instead of within a single cell?

 

[deannaed]

Hi

Do you only require that via VBA, or will a formula suffice?
If a formula will do for you - please have a look at the attached file.


All the best
Thanks

 

[shahin]

I've managed to make it work for the "headers" part. However, it is still beyond my thinking how can I go about "rowSet" portion.

If CallByName(post, "name", VbGet) = "ClosestDefender10ftPlusShooting" Then
    v = CallByName(post, "headers", VbGet)
    For R = 1 To UBound(Split(v, ","))
        Cells(1, R) = Split(v, ",")(R)
    Next R
End If

 

[shahin]

Thanks @deannaed, for your kind concern. The thing is I wish to do the whole thing using vba.

 

[YasserKhalil]

Hello my friend
Give this a try

Sub FetchData()
    Const url As String = "http://stats.nba.com/stats/playerdashptshots?DateFrom=&DateTo=&GameSegment=&LastNGames=6&LeagueID=00&Location=&Month=0&OpponentTeamID=0&Outcome=&PerMode=PerGame&Period=0&PlayerID=2544&Season=2017-18&SeasonSegment=&SeasonType=Playoffs&TeamID=0&VsConference=&VsDivision="

    Dim http        As New XMLHTTP60
    Dim sc          As Object
    Dim results    As Object
    Dim post        As Object
    Dim elem        As Object
    Dim v          As Variant
    Dim r          As Long
    Dim c          As Long

    Set sc = CreateObject("ScriptControl")
    sc.Language = "JScript"

    With http
        .Open "GET", url, False
        .send
        Set results = sc.Eval("(" + .responseText + ")")

        For Each post In CallByName(results, "resultSets", VbGet)
            If CallByName(post, "name", VbGet) = "ClosestDefender10ftPlusShooting" Then
                v = CallByName(post, "headers", VbGet)
                For c = 0 To UBound(Split(v, ","))
                    Cells(1, c + 1) = Split(v, ",")(c)
                Next c

                For Each elem In CallByName(post, "rowSet", VbGet)
                    r = r + 1
                    For c = 0 To UBound(Split(elem, ","))
                        Cells(r + 1, c + 1) = Split(elem, ",")(c)
                    Next c
                Next elem
            End If
        Next post
    End With
End Sub

 

[shahin]

Yes, it is. You made it perfectly @YasserKhalil. Thanks.

 

[shahin]

There is a slight anomaly in the output. Check out the csv which contains how the output should look like. Thanks.

 

[YasserKhalil]

Try this version

Sub FetchData()
    Const url As String = "http://stats.nba.com/stats/playerdashptshots?DateFrom=&DateTo=&GameSegment=&LastNGames=6&LeagueID=00&Location=&Month=0&OpponentTeamID=0&Outcome=&PerMode=PerGame&Period=0&PlayerID=2544&Season=2017-18&SeasonSegment=&SeasonType=Playoffs&TeamID=0&VsConference=&VsDivision="

    Dim http        As New XMLHTTP60
    Dim sc          As Object
    Dim results    As Object
    Dim post        As Object
    Dim elem        As Object
    Dim v          As Variant
    Dim r          As Long
    Dim c          As Long

    Set sc = CreateObject("ScriptControl")
    sc.Language = "JScript"

    With http
        .Open "GET", url, False
        .send
        Set results = sc.Eval("(" + .responseText + ")")

        For Each post In CallByName(results, "resultSets", VbGet)
            If CallByName(post, "name", VbGet) = "ClosestDefender10ftPlusShooting" Then
                v = CallByName(post, "headers", VbGet)
                For c = 0 To UBound(Split(v, ","))
                    Cells(1, c + 1) = Split(v, ",")(c)
                Next c

                For Each elem In CallByName(post, "rowSet", VbGet)
                    r = r + 1
                    For c = 0 To UBound(Split(elem, ","))
                        If c = 1 Then Cells(r + 1, c + 1) = Split(elem, ",")(c) & "," & Split(elem, ",")(c + 1): c = 3
                        If c >= 2 Then
                            Cells(r + 1, c) = Split(elem, ",")(c)
                        Else
                            Cells(r + 1, c + 1) = Split(elem, ",")(c)
                        End If
                    Next c
                Next elem
            End If
        Next post
    End With
End Sub

 

[shahin]

I knew you would come up with a refined one. Thanks for your fruitful effort. It's really cumbersome to work with a list.

 

[YasserKhalil]

You're welcome. Glad I can offer some help
Best Regards