Creating Postcode Based Locations – Rails

My goal for today was to populate a record with all of the Australian postcodes and their corresponding suburb and state descriptions. To do this I had to complete the following:

  • 1: Download the postcode/location csv
  • 2: Create new model to contain locations
  • 3: Create a script to import a csv
  • 4: Run the script in order to populate the database
  • 5: Cleanse the data

Step #1: Download the .csv
I came across the following files after browsing a few of the Australian tech forums. I chose to use the Corra ones due to the claim that there are no license restrictions.

Once you’ve chosen your .csv, just save it in your project folder i.e. C:\my_app\

Step #2: Create a Model for your Locations
Secondly, you’ll need to create a new migration for your locations. I’ve simply called mine Code:

def change
      create_table :codes do |t|
        t.string "Pcode"
        t.string "Locality"
        t.string "State"
        t.string "Comments"
        t.string "DeliveryOffice"
        t.string "PresortIndicator"
        t.string "ParcelZone"
        t.string "BSPnumber"
        t.string "BSPname"
        t.string "Category"
        t.timestamps
     end
end

Step #3: Create a Script to Import the .csv
The third step is to create a script that allows you to import the csv. This is made simple thanks to a comment left by “Gianluca” on the following blog post:
http://erikonrails.snowedin.net/?p=212

Simply create a new file within your tasks folder and add the following code:

#my_app/lib/tasks/import.rake
 
require "csv"
desc "Import CSV file into an Active Record table"
task :csv_model_import, [:filename, :model] => :environment do |task,args|
  firstline=0
  keys = {}
 
  CSV.foreach(args[:filename]) do |row|
    if (firstline==0)
      keys = row
      firstline=1
      next
    end
 
    params = {}
 
    keys.each_with_index do |key,i|
      params[key] = row[i]
    end
 
    Module.const_get(args[:model]).create(params)
  end
end

Step #4: Run the script in order to populate the database
To run the script simply run the following command:

chris@chris-VirtualBox:~/my_app$ rake csv_model_import[codes.csv,Code]

Step #5: Cleanse the data
Cleansing the data is a little tedious, however one tip is to remove all locations that do not have a category value of “Delivery Area”.

Ahwell, that’s all I’ve got for now – let me know if you have any trouble.

3 thoughts on “Creating Postcode Based Locations – Rails

  1. david balmer

    I am following your code with my own example data. I cannot get by the message “Rake aborted! Don’t know how to build task ‘csv_model_import.

    .csv file is:
    smith,psychiatry
    balmer,orthopaedics
    white,dermatology

    migration up is:

    class CreateDoctors < ActiveRecord::Migration
    def change
    create_table :doctors do |t|
    t.string :name
    t.string :specialty

    t.timestamps
    end
    end
    end

    model is:

    class Doctor :environment do |task,args|
    firstline=0
    keys = {}

    CSV.foreach(args[:filename]) do |row|
    if (firstline==0)
    keys = row
    firstline=1
    next
    end

    params = {}

    keys.each_with_index do |key,i|
    params[key] = row[i]
    end

    Module.const_get(args[:model]).create(params)
    end
    end

    Using Rails 3.2.8 and Ruby 1.9.3

    Reply
    1. Chris Owens Post author

      Hey David,

      Did you create the rake task in step 3? If so, can you post a copy of that for me?

      Thanks,
      chris

      Reply
  2. david balmer

    Sorry previous response should have shown Model as:

    class Doctor < ActiveRecord::Base
    attr_accessible :doctors_attributes
    end

    Reply

Leave a Reply