Page 1 of 1

help converting text to CSV

Posted: 26 Jun 2017, 05:01
by JackPJ
Hi All,

I'm having a problem formatting some text I get from a website.

I use a send request (not sure what the correct term is) to get data from

If I use the example below from their API docs, I get this wall of text. ... te=2013-01

I don't know where to even begin in trying to convert this to a CSV. Could someone point me in the right direction please?

Many thanks


Re: help converting text to CSV

Posted: 26 Jun 2017, 06:17
by BoBo
That outcome is pretty much in CSV-format. You've to get rid of unnecessary characters like the curly braces and set EOL-characters using StrReplace() OR RegExReplace()) and to decide how to split the sub content of "location".

{"category":"anti-social-behaviour","location_type":"Force","location":{"latitude":"52.630069","street":{"id":883344,"name":"On or near Mill Street"},"longitude":"-1.133045"},"context":"","outcome_status":null,"persistent_id":"","id":20603217,"location_subtype":"","month":"2013-01"}, ...

The output should look similar to this ...

"category":"anti-social-behaviour","location_type":"Force","latitude":"52.630069","streetid":883344,"streetname":"On or near Mill Street","longitude":"-1.133045","context":"","outcome_status":null,"persistent_id":"","id":20603217,"location_subtype":"","month":"2013-01"
FileRead, inVar,% "inputFile.txt"

outVar:= StrReplace(...
outVar:= RegExReplace(...
FileAppend,% outVar, % "outputFile.csv"
... and if it's really a JSON file you should forget all of this and follow jNizM's advise ... :)

Re: help converting text to CSV

Posted: 26 Jun 2017, 06:26
by jNizM
It looks more like a json file. So try JSON by coco

Re: help converting text to CSV

Posted: 26 Jun 2017, 07:17
by JackPJ
Thanks both, I'll have a play around with it with the info you've given me and fingers crossed I can get somewhere

In hindsight, had I have know what JSON is or if it would be relevant, the part on the API doc sayingThe API is implemented as a standard JSON web service would have probably been of use to you guys. My bad!

Re: help converting text to CSV  Topic is solved

Posted: 26 Jun 2017, 07:36
by jeeswg
I found it a bit fiddly to get going with that JSON library, and unpicking JSON itself, so here's an example, assuming that your text from above is put onto the clipboard before running it:

I think of JSON as taking Ini files to the next level, with all kinds of hierarchy possible.

The tricky thing is that whenever you see square brackets, you refer to those keys as 1, 2, 3 etc.

Btw in this example, there are certain fields that are regularly blank. (Which makes it harder to check that the code is actually working!)

One potential issue with the JSON library, is that it converts a JSON string to an object, but AHK objects do not preserve the original order of keys, for when you do a 'For loop', although that doesn't seem to pose a problem in this case. Anyway a really good job by Coco. (If I add keys: Q, W, E, to an object, and then do a For loop, it returns: E, Q, W.)

Code: Select all

q:: ;JSON example
oArray := JSON.Load(Clipboard) ;JSON string to AHK array
MsgBox, % oArray.Length()
Loop, % oArray.Length()
	vOutput := ""
	. oArray[A_Index].category "`r`n"
	. oArray[A_Index].location_type "`r`n"
	. oArray[A_Index].location.latitude "`r`n"
	. oArray[A_Index] "`r`n"
	. oArray[A_Index] "`r`n"
	. oArray[A_Index].location.longitude "`r`n"
	. oArray[A_Index].context "`r`n"
	. oArray[A_Index].outcome_status "`r`n"
	. oArray[A_Index].persistent_id "`r`n"
	. oArray[A_Index].id "`r`n"
	. oArray[A_Index].location_subtype "`r`n"
	. oArray[A_Index].month
	MsgBox, % vOutput
oArray := ""

;JSON 2.0 (and Jxon) - JSON lib for AutoHotkey - AutoHotkey Community
;GitHub - cocobelgica/AutoHotkey-JSON: JSON module for AutoHotkey
#Include, %A_ScriptDir%\Lib\JSON.ahk ;change or comment this out as appropriate

Re: help converting text to CSV

Posted: 26 Jun 2017, 07:47
by JackPJ
You guys are geniuses!
That example works perfect for me, I should be able to 'bodge it' to display as I need. Thanks all for your time, I'll mark this as solved unless I get stuck.

Looks like I need to spend some time on google learning JSON. It still looks Greek to me