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.