Wednesday, December 8, 2010

Loading CSV files using Rails 3.0.3

While watching the Falcons vs Bucs game on NFL Instant Replay on the NFL Network (that channel absolutely rocks), I decided to take up a challenge and build a very simple RoR app that uploads a CSV file that contains a list of Cities and corresponding zipcodes and store it in a database. The app will have simple CRUD operations for City/Zipcode as well as an upload view for uploading the file to the server. From the start I decided to have two controllers (one for CRUD'ing the City/Zip and one for Uploading the file). And here we go...

9:18 PM

Since I want full CRUD for City, I decided to run the scaffold generator to generate the controller, views, model, functional and unit tests. So I droped down to a terminal window and ran the following commands:

$ rails new cityupload
$ cd cityupload
$ rails g scaffold city name:string zipCode:string
$ rake db:migrate

This sets up a rails project named cityupload, generates a CityController, City model and views CRUD'ing a city. I also ran rake to generate the city table with the corresponding fields for the city model

9:19 PM

Since I want a separate controller for handling the uploading of the file I decided to use the controller generator. The following creates a UploadCitiesController class with two actions (index and uploadfile) and corresponding views that matches the action methods (index.html.erb and uploadfile.html.erb). My index action will actually post the file to the server and the uploadfile action will parse the file and persist the data to the database

$ rails g controller UploadCities index uploadfile

9:20 PM

Before opening the project in NetBeans I decided to run irb to test whether or not I can CRUD cities from the command line. $ represents my cursor

$ rails console
$ city = City.create(:name => 'Atlanta', :zipcode => '30349')
 => # 
$ city.new_record?
=> false
$ City.all
=> [#<city id: 1, name: "Atlanta", zipcode: "30349", created_at: "2010-12-08 02:41:20", updated_at: "2010-12-08 02:41:20">]
$ City.count
=> 1

The above verifies that all is well with saving objects to the database with ActiveRecord. NOTE: My default database is MySql. Now let's implement the UploadController. A friend suggested I use the FastCSV, however Ruby 1.9.2 already redesigned the CSV class to make FastCSV obsolete. In fact, I get a warning to use CSV instead of FastCSV when starting WEBrick.

11:45 PM

The uploadfile action takes a file and uses CSV to iterate all the rows in the file and creates and saves a new City instance to the database, then I display all the cities in the uploadfile.html.erb template file. It actually took me a while to figure out how to use CSV, but once I figured out the correct method to use and arguments to pass it was a breeze.

require 'csv'

class UploadCitiesController < ApplicationController

  def uploadfile
    cities_upload_file = params[:upload]
    @cities =
    if cities_upload_file == nil
      @notice = "Please select a file!"


    #note: There's no exception logic for now.  If CSV fail here an
    #error is thrown to the client
    values =, "r").read
    index = 0

    values.each do |row|
      city = => row[0], :zipcode => row[1])
      #add city to cities array
      @cities[index] = city
      index += 1

    cityCount = @cities.count

    @notice = "'#{cityCount}' Cities/Zipcodes successfully uploaded to server"

    respond_to do |format|

1:45 AM

I've been pulling my hair out trying to figure out why my routes aren't working. It turns out I needed to update the routes.rb file in my Configuration folder. I needed to add a match route for my uploadfile action:

match "upload_cities/uploadfile" => "upload_cities#uploadfile"

Here's my index.html.erb file for submitting the file to the server

<h1>Upload Zipcodes to server</h1>
<p id="notice"><%= notice %></p>

<%= form_tag 'upload_cities/uploadfile', :multipart => true do %>
  <p><label for="upload">Select File</label> :
  <%= file_field_tag "upload" %></p>
  <%= submit_tag "Upload File" %>
<% end %>

<%= link_to 'Back', cities_path %>

And here's my uploadfile.html.erb that displays a confirmation message if the upload and save was successful and all of the cities that were saved to the database


<h1>City/Zipcodes status</h1>
<p id="notice"><%= @notice %></p>

<% if @cities.count %>

    <th>Date Created</th>

<% @cities.each do |city| %>
    <td><%= %></td>
    <td><%= %></td>
    <td><%= city.zipcode %></td>
    <td><%= city.created_at %></td>
<% end %>

<% end %>

<%= link_to 'Back', cities_path %>

It took me about 5 hours to do this simple task in RoR but I expect that with learning a new language. I will say using the built-in generators saved me an immeasurable amount of time since I didn't have to worry about the database scripts, controller and view skeletons. So far I'm digging Rails...

No comments:

Post a Comment