help converting text to CSV Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
JackPJ
Posts: 18
Joined: 19 Oct 2016, 05:52

help converting text to CSV

26 Jun 2017, 05:01

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 https://data.police.uk/

If I use the example below from their API docs, I get this wall of text.
https://data.police.uk/api/crimes-stree ... te=2013-01
Spoiler

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

Jack
BoBo
Posts: 2519
Joined: 13 May 2014, 17:15

Re: help converting text to CSV

26 Jun 2017, 06:17

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"
F1::
FileRead, inVar,% "inputFile.txt"
parseStat(inVar)
SoundBeep
Return

parseStat(inVar){
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 ... :)
Last edited by BoBo on 26 Jun 2017, 06:38, edited 3 times in total.
User avatar
jNizM
Posts: 2435
Joined: 30 Sep 2013, 01:33
GitHub: jNizM
Contact:

Re: help converting text to CSV

26 Jun 2017, 06:26

It looks more like a json file. So try JSON by coco
[AHK] 1.1.30.03 x64 Unicode | [WIN] 10 Pro (Version 1903) x64 | [GitHub] Profile
Donations are appreciated if I could help you
JackPJ
Posts: 18
Joined: 19 Oct 2016, 05:52

Re: help converting text to CSV

26 Jun 2017, 07:17

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!
User avatar
jeeswg
Posts: 6541
Joined: 19 Dec 2016, 01:58
Location: UK

Re: help converting text to CSV  Topic is solved

26 Jun 2017, 07:36

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].location.street.id "`r`n"
	. oArray[A_Index].location.street.name "`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 := ""
return

;JSON 2.0 (and Jxon) - JSON lib for AutoHotkey - AutoHotkey Community
;https://autohotkey.com/boards/viewtopic.php?f=6&t=627
;GitHub - cocobelgica/AutoHotkey-JSON: JSON module for AutoHotkey
;https://github.com/cocobelgica/AutoHotkey-JSON
#Include, %A_ScriptDir%\Lib\JSON.ahk ;change or comment this out as appropriate
Last edited by jeeswg on 05 Jul 2017, 11:47, edited 3 times in total.
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
JackPJ
Posts: 18
Joined: 19 Oct 2016, 05:52

Re: help converting text to CSV

26 Jun 2017, 07:47

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

Return to “Ask For Help”

Who is online

Users browsing this forum: vsub and 170 guests