We help IT Professionals succeed at work.

sparse download file in vba

I would like to extract the location,city,country etc.from the attach file in vba.
text.txt
Comment
Watch Question

Your file is in JSON format which is described e.g. here: https://en.wikipedia.org/wiki/JSON

You may read it in VBA using e.g. JSON Converter: https://github.com/VBA-tools/VBA-JSON
Some code samples are also here: https://dymeng.com/parsing-json-with-vba/
Top Expert 2014

Commented:
extract the location,city,country etc

Does "etc" mean you want all possible data?
Top Expert 2014

Commented:
You can use the vbscript.regexp object to do your parsing.

The regex pattern you would need for this sample is:
"([^"]*)":"([^"]*)"|"([^"]*)":\d+|"([^"]*)":\[([^\]]*)\]

Open in new window

Top Expert 2014

Commented:
Correction on the regex pattern.  I missed a capture group.  Should be:
"([^"]*)":"([^"]*)"|"([^"]*)":(\d+)|"([^"]*)":\[([^\]]*)\]

Open in new window

Author

Commented:
alimark having problems setting up the pattern with the quotation marks
Top Expert 2014

Commented:
1. Select the pattern and replace single quote characters with two quote characters.
2. Put the resulting string inside a pair of quote characters.

Author

Commented:
aikimark got an example of the entire function?
Top Expert 2014
Commented:
Sub Q_29169268()
    Dim oRE As Object
    Dim oMatches As Object
    Dim oM As Object
    Dim lngSM As Long
    Dim strJSON As String
    Dim vType As Variant
    vType = Array("str", "num", "list")
    
    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = """([^""]*)"":""([^""]*)""|""([^""]*)"":(\d+)|""([^""]*)"":\[([^\]]*)\]"
    
    strJSON = "{""request"":{""type"":""City"",""query"":""New York, United States of America"",""language"":""en"",""unit"":""m""},""location"":{""name"":""New York"",""country"":""United States of America"",""region"":""New Zealand"",""lat"":""90.498"",""lon"":""-104.489"",""timezone_id"":""America\/New_York"",""localtime"":""2020-01-11 12:21"",""localtime_epoch"":1578759660,""utc_offset"":""-5.0""},""current"":{""observation_time"":""09:21 PM"",""temperature"":20,""weather_code"":122,""weather_icons"":[""https:\/\/assets.weatherstack.com\/images\/wsymbols01_png_64\/wsymbol_0004_black_low_cloud.png""],""weather_descriptions"":[""Overcast""],""wind_speed"":9,""wind_degree"":230,""wind_dir"":""SW"",""pressure"":1019,""precip"":0,""humidity"":56,""cloudcover"":100,""feelslike"":20,""uv_index"":4,""visibility"":16,""is_day"":""yes""}}"
    
    If oRE.test(strJSON) Then
        Set oMatches = oRE.Execute(strJSON)
        For Each oM In oMatches
            For lngSM = 0 To oM.submatches.Count - 1 Step 2
                If Len(oM.submatches(lngSM)) <> 0 Then
                    Debug.Print vType(lngSM / 2), oM.submatches(lngSM), oM.submatches(lngSM + 1)
                    Exit For
                End If
            Next
        Next
    End If
End Sub

Open in new window

Author

Commented:
aikimark Awesome!!