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
  • 1
    You should probably edit your question to give some specific examples of what you are trying to do, what you have tried and why it is not working. People are also more likely to respond if you upvote and mark 'correct' good answers to your previous questions!
    – Simbamangu
    Nov 13 '13 at 19:20
  • I am trying to separate specific pieces of data into the corresponding fields in Excel. I want to populate my fields with the info from the .txt file. I have brought the .txt data file into Excel and have been using Find&Replace to pull out the data I need one date at a time. I need a faster way to do this. I would upvote the answer to my previous question but I don't have a big enough rep yet.
    – cbunn
    Nov 13 '13 at 19:30
  • You can certainly accept the answer given to your previous question - just tick the check mark; at least, do so if the answer worked for you (and edit your question or comment on the answer if not).
    – Simbamangu
    Nov 14 '13 at 18:12
  • Whoops! I must have missed that. I did it now, thanks! Now time to work with these answers.
    – cbunn
    Nov 14 '13 at 23:00

One of the great things about structured text like this (generally fixed width data) is that it is pretty easy to parse out using a programming language. I used almost the same approach that @congrene used, but I wrote it with Python, which is widely used in the GIS community.

You'll note that in many cases the City and State are not fully populated. Therefore I (and @congrene) hard-coded the City and State attributes. If they were fully populated, you could extract the city with record["city"] = parseaddress[1] and state with record["state"] = parseaddress[2].

For explanatory purposes of how the code works, I created a Python dictionary (record) and then added new key:value pairs to hold each individual attribute. I then added the whole "record" as a Python List item, which could be used to loop through all records when it came time to write to CSV. That made it easier to build out the CSV by just calling 1) the next record and 2) the correct key:value for the record when writing the output to the CSV file.

import os, datetime, csv

infile = r'd:\path_to_your_data\January_Data.txt'
outfile = r'd:\path_to_your_data\January_Data.csv'

#Create an list aggregator to hold all csv values
csv_data = []

with open(infile) as f:
    for line in f.readlines():  ##read through each line in the file

        #Look for keywords in a line and extract the data that follows it
        if "Received:" in line:
            record = {}  ##create a dictionary to hold incident-level data
            record["incident"] = line[0:9].strip()
            record["date"] = line[10:18].strip()
            record["dayofweek"] = datetime.datetime.strptime(record["date"],'%m/%d/%y').strftime('%A')
            record["received"] = line[28:33].strip()
            record["dispatched"] = line[45:50].strip()
            record["arrived"] = line[59:64].strip()
            record["cleared"] = line[73:80].strip()

        elif "Type:" in line:
            record["type"] = line[6:20].strip()
            record["location"] = line[71:80].strip()

        elif "Addr:" in line:
            parseaddress = line[6:49].strip().split(",")
            record["addr"] = parseaddress[0]
            record["city"] = "San Luis Obispo"
            record["state"] = "CA"
            record["clearcode"] = line[64:80].strip()

        elif "Responsible Officer:" in line:
            record["officer"] = line[21:80].strip()

        elif "Units:" in line:
            record["units"] = line[6:80].strip()

        elif "Des:" in line:
            ##This line appears to terminate an incident record

if os.path.exists(outfile):  ##check to see if the output exists already

#Write the data to a CSV file
with open (outfile, 'wb') as csvfile:
    csvwriter = csv.writer(csvfile, delimiter=',', quotechar='"')

    #create the header row
    csvwriter.writerow(["Incident","Address", "City", "State", "Type", "Location", "Date", "Day of Week", "Time Received", "Time Dispatched", "Time Arrived", "Time Cleared"])

    #Add individual records to the CSV
    for record in csv_data:
        csvwriter.writerow([ record["incident"], record["addr"], record["city"], record["state"], record["type"], record["location"], record["date"], record["dayofweek"], record["received"], record["dispatched"], record["arrived"], record["cleared"] ])

print "Data converted to :" + str(outfile)
  • Got this error when trying to run the script. I am running Python 3.3.3 in the Python Shell. SyntaxError: multiple statements found while compiling a single statement
    – cbunn
    Dec 16 '13 at 19:07

I know you asked for a solution using excel, but here is a short ruby script to parse this data. To use, you'll have to install ruby (https://www.ruby-lang.org/en/downloads/). Once set up, put the data and the script in the same folder and run: ruby munge.rb

My result from running this on a portion of the sample data provided: https://gist.github.com/tomay/ac536aad29cc50c7e06b
(note: this example is geocoded using the excellent ruby geocoder gem, see code sample on same gist page)

I haven't tested this extensively, so use with care. Ask if you run into issues.

require 'date'
# open files
infile = File.new("January_Data.txt","r")
outfile = File.new("result.csv","w")

# write header
header = "Address,City,State,Type,Location,Date,Day of Week,Time Received,Time Dispatched,Time Arrived,Time Cleared\n"
outfile << header

# read file lines
result = {}
infile.each do |line|
  values = line.split(" ")
  next if (values.length < 2 || line.chomp == "As Observed:")
    if values[0] == "Addr:"
      result["address"] = line.split(",")[0].gsub("Addr: ","")
    elsif values[0] == "Type:"
      result["location"] = values.pop.split(":").last
      result["type"] = values.join(" ")
    elsif values[2].split(":")[0] == "Received"
      result["date"] = values[1]
      result["day"] = Date.strptime(result['date'], '%m/%d/%y').strftime('%A')
      result["time_received"] = "#{values[2].split(":")[1]}:#{values[2].split(":")[2]}"
      result["time_dispatched"] = "#{values[3].split(":")[1]}:#{values[3].split(":")[2]}"
      result["time_arrived"] = "#{values[4].split(":")[1]}:#{values[4].split(":")[2]}"
      result["time_cleared"] = "#{values[5].split(":")[1]}:#{values[5].split(":")[2]}"
    elsif values[0] == "Des:"
      # write result
      outfile.write "#{result['address']},San Luis Obispo,CA,#{result['type']},#{result['location']},#{result['date']},#{result['day']},#{result["time_received"]},#{result["time_dispatched"]},#{result["time_arrived"]},#{result["time_cleared"]}\n"
      result = {}
    puts "Line was: #{line}"

# close files
  • This looks very promising! How can I then turn the output into a CSV?
    – cbunn
    Nov 14 '13 at 23:03
  • the output is already formatted as csv. The line starting outfile.write "#{result['address']},San Luis Obispo,CA, outputs each field separated by commas
    – toms
    Nov 14 '13 at 23:34
  • Awesome. I've installed Ruby on my machine and I have put your script and my data into the same folder but I haven't been able to get it to run for me. Does it matter where the folder is resting? Sorry for the obvious lack of programming experience.
    – cbunn
    Nov 15 '13 at 18:49
  • folder location doesn't matter. probably more efficient to troubleshoot in chat, created a room: chat.stackexchange.com/rooms/11521/…
    – toms
    Nov 15 '13 at 19:12

Given the fields that are of interest to you are in 3 different typical lines, and given your lack of programming skills, I might suggest you make a first pass at removing useless lines.

You may be able to do that with a better text editor. As an example, the following command in Vim or GVim will delete all lines not containing either of these 3 conditions:

  • 3 or more digits at beginning of line (record numbers) (regex pattern: ^\d\d\d\+)
  • Lines beginning with Type (regex pattern: ^Type)
  • Lines beginning with Addr (regex pattern: ^Addr)

The magic command is :v/^\d\d\d\+\|^Type\|^Addr/d

will then transform your file in this form:

More notes on the above command at the end of the post

120131082 01/31/12 Received:20:48 Dispatched:20:51 etc. 
Type: COLL NON                    Location:LZ2                          
Addr: 297 MADONNA; PANERA BREAD, San Luis Obisp  Clearance Code:Report Filed in

From there you'll have to find a way to join the 3 recurring lines into a single one. This could be done very mechanically, search the VBA Excel threads for hints/solutions this one here about reshaping is directly applicable!. At least from there, you'll be closer to something you can parse within Excel. Good luck !

Info complement on Vim command:

:v/ some_pattern /d

  1. That is a "reVerse grep then Delete" command. As such, it will delete all lines not matching the pattern between slashes.

  2. The patterns are in regular expression form regex which BTW are supported in most programming languages and good text editors. In Vim's case the "pipe" character meaning "or" needs to be escaped with a backslash: \|.

  • Nice - it always amazes me what Vim can do. Could you edit your answer to explain the magic a little more, what each component of the command is?
    – Simbamangu
    Nov 15 '13 at 5:05
  • @Simbamangu I added a bit of info on the Vim command. The commmand appearance is as ugly as it is magic... One needs to progressively build up these commands. For one-off's, I am always amazed to see how far one can get with a good text editor.
    – jjap
    Nov 15 '13 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.