Link to home
Start Free TrialLog in
Avatar of Svgmassive
Svgmassive

asked on

sparse download file in vba

I would like to extract the location,city,country etc.from the attach file in vba.
text.txt
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

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/
extract the location,city,country etc

Does "etc" mean you want all possible data?
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

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

Open in new window

Avatar of Svgmassive
Svgmassive

ASKER

alimark having problems setting up the pattern with the quotation marks
1. Select the pattern and replace single quote characters with two quote characters.
2. Put the resulting string inside a pair of quote characters.
aikimark got an example of the entire function?
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
aikimark Awesome!!