I am trying to clean up a large .txt dataset in Excel. I think that a macro or another application may be able to clean up the data they way I want but I have limited programming skills. I need to create fields from different sections in the .txt file so I can then geocode the points. I am conducting a crime analysis for my local police department and up until now I have been using wildcards and the Find & Replace tool to do this, but I have nearly 2 years worth of police calls. Fields needed:"Address", "City", "State", "Type", "Location", "Date", "Day of Week", "Time Received", "Time Dispatched", "Time Arrived", "Time Cleared". I put up the data from January 2012 on Github in repo called SLO-Crime under my name, chadbunn. I will be using CartoDB to geocode the data and then QGIS to perform the analysis. I will probably make some maps in TileMill as well if I have enough time to. Any input would be appreciated because it is getting tedious! Thanks!
Text sample below showing a single incident (plain text file, each section composed of a dispatch header summary followed by type / address / officer and other information):
=============================================================================== 120101001 01/01/12 Received:00:05 Dispatched:00:06 Arrived:00:12 Cleared:00:53 =============================================================================== Type: MENTAL SUBJ Location:LZ1 As Observed: Mental Health Addr: 1257 RICH, San Luis Obispo, CA Clearance Code:Report Filed in Responsible Officer: Hyman, J Units: 4235 ,4241 Des: incid#=120101001 AP/KAVANAGH,KOLLEEN 060455 166 PC clr:RTF oc:MENH call=1l