0

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?

Gerlof Leuhof
  • 107
  • 1
  • 7
  • 1
    You could convert the text to a csv with a series of search and replaces (or a regular expression) then open it in Excel – cybernetic.nomad Sep 12 '18 at 21:35
  • Yeah, but how should I do that? Which tool / script / program? – Gerlof Leuhof Sep 12 '18 at 21:41
  • 1
    you may use the [Get & Transform](https://support.office.com/en-us/article/get-transform-in-excel-881c63c6-37c5-4ca2-b616-59e18d75b4de) function of Excel 2013+ in order to convert a JSON file in an Excel table. See also [this answer](https://superuser.com/questions/977249/loading-a-json-file-into-power-query) on superuser – visu-l Sep 14 '18 at 12:31

3 Answers3

3

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.

Nicholas Humphrey
  • 143
  • 8
1

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.

JohnnyVegas
  • 3,470
  • 1
  • 14
  • 18
  • 1
    I don't think it's a GPS data file though. It's extracted from a bigger HTML file. Also, I want non-location columns like 'Date' and 'Title' – Gerlof Leuhof Sep 12 '18 at 21:40
  • with some regex expressions in sublime text you could convert to a CSV in no time - lookup regex in sublime help – JohnnyVegas Sep 19 '18 at 20:32
1

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

cybernetic.nomad
  • 5,415
  • 12
  • 25
  • regex101.com is there to *test* your regex, not process an entire dataset. Many text editors support regex. Look for `regular expression` or `regex` in the find/replace dialog box – cybernetic.nomad Sep 13 '18 at 18:18
  • Thanks. Got it to work with the tools http://www.unit-conversion.info/texttools/add-line-breaks/ and https://www.regextester.com/97259 – Gerlof Leuhof Sep 13 '18 at 18:45