Extract data from a very messy text file into Excel

Question

I have a very messy .txt file with a lot raw data in it. It's one big long line, constantly repeating location data:

\"Locations\":[{\"lat\":\"37.77160263061523\",\"lng\":\"-120.85061645507812\",\"city\":\"Oakdale\",\"poi\":\"\",\"pindata_id\":\"194395\"}]},{\"User\":{\"id\":\"<censored>\",\"username\":\"<censored>\"},\"Pindata\":{\"id\":\"194739\",\"date\":\"2018-05-18\",\"country\":\"US\",\"title\":\"Let's go!\"} ... and then it repeats this a lot of times

Now what I want is a script or tool that extracts all that data like this into Excel columns and cells. So from the above line(s), to make it like this:

Latitude            Longitude             City      Date         Title
37.77160263061523   -120.85061645507812   Oakdale   2018-05-18   Let's go!
etc.
etc.

How would I do something like this?

Answer 1

This is JSON format and you should be able to use a JSON library to extract the data. I'm using this one for my work:

https://github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas

Note that in this library, JSON arrays (content between [ and ]) are parsed as VBA Collection, while JSON Objects (between { and }) are parsed as VBA Variant.

Answer 2

A simple free tool is available to convert GPS data files to excel - GPSBabel.

https://www.gpsbabel.org/download.html

It's a free project so donate some funds to this very useful tool.

Answer 3

Open the text file in an editor with regex capability (Emeditor, BBedit, etc...) then do the following regex search:

.+lat\\":\\"(.+)\\",\\"lng\\":\\"(.+)\\",\\"city\\":\\"(.+)\\",\\"poi.+date\\":\\"(.+)\\",\\"country.+title\\":\\"(.+)\\"\}

And replace with:

$1\t$2\t$3\t$4\t$5

This will give you a tab delimited file which you can then open in Excel (tab delimited since comma delimited may run into problems if the title contains commas.

Test: https://regex101.com/r/waUoNh/2