Today I needed to migrate data from a Drupal site to Ruby on Rails. It is pretty easy to export data from my Drupal database (MySQL) into CSV format that I can use to import into my Rails application (Postgre SQL). So that’s where I started. I’m running Drupal 6.x, here is my database query to pull nodes out of the database. I ran this in phpMyAdmin then exported the results to a CSV file.

SELECT * FROM www_node n
	INNER JOIN www_content_type_release t
	INNER JOIN www_location l
	INNER JOIN www_location_instance i
	INNER JOIN www_node_revisions r
	ON t.nid=n.nid AND n.nid=i.nid AND i.lid=l.lid AND r.nid=n.nid
	WHERE n.type="release"

Back in Rails, I already have my application setup to support the press releases I’ll be importing and I won’t be covering that code here, it’s a standard scaffolding type MVC. You’ll need to update the code below to match your application naming.

---

First, we need a model to store the uploaded file and some related information. To upload and attach the CSV data, I’m going to use the Paperclip plugin, thanks to jimneath.org for the blog post on integrating Paperclip into your rails application. If you have more questions on Paperclip, please refer to their documentation or jimneath.org.

script/plugin install git://github.com/thoughtbot/paperclip.git

Continuing on with the code, you’ll need to generate a model for you data.

ruby script/generate model import

Edit the model import.rb

class Import < ActiveRecord::Base
  # Paperclip
  has_attached_file :csv
  validates_attachment_presence :csv
	validates_attachment_content_type :csv, :content_type => ['text/csv','text/comma-separated-values','text/csv','application/csv','application/excel','application/vnd.ms-excel','application/vnd.msexcel','text/anytext','text/plain']
end

Edit the migration

class CreateImports < ActiveRecord::Migration
	def self.up
		create_table :imports do |t|
			t.string :datatype
			t.integer :processed, :default => 0
			t.string :csv_file_name
			t.string :csv_content_type
			t.integer :csv_file_size
			t.timestamps
		end
	end

	def self.down
		drop_table :imports
	end
end

Update your database:

rake db:migrate

---

Next, lets generate a controller to upload files and process the data

ruby script/generate controller imports

In imports_controller.rb I’m going to add methods to process the data I’ll be importing as well as a private methods to read the CSV file and store the records in the database. Note: I installed the gem, fastercsv | http://fastercsv.rubyforge.org/ to parse the CSV data.

class ImportsController < ApplicationController
  before_filter :login_required #protect controller from anonymous users

  def new
    @import = Import.new
	end

	def create
    @import = Import.new(params[:import])

    respond_to do |format|
      if @import.save!
        flash[:notice] = 'CSV data was successfully imported.'
        format.html { redirect_to(@import) }
      else
        flash[:error] = 'CSV data import failed.'
        format.html { render :action => "new" }
      end
    end
	end

	def show
    @import = Import.find(params[:id])
	end

	def proc_csv
    @import = Import.find(params[:id])
    lines = parse_csv_file(@import.csv.path)
    lines.shift #comment this line out if your CSV file doesn't contain a header row
    if lines.size > 0
      @import.processed = lines.size
      lines.each do |line|
        case @import.datatype
        when "releases"
          new_release(line)
        end
      end
      @import.save
      flash[:notice] = "CSV data processing was successful."
      redirect_to :action => "show", :id => @import.id
    else
      flash[:error] = "CSV data processing failed."
      render :action => "show", :id => @import.id
    end
	end

private

  def parse_csv_file(path_to_csv)
    lines = []

    #if not installed run, sudo gem install fastercsv
    #http://fastercsv.rubyforge.org/
    require 'fastercsv'

    FasterCSV.foreach(path_to_csv) do |row|
      lines << row
    end
    lines
  end

	def new_release(line)
    params = Hash.new
    params[:release] = Hash.new
    params[:release]["title"] = line[0]
		params[:release]["rdate"] = line[1]
    params[:release]["body"] = line[3]
    params[:release]["notes"] = line[4]
    release = Release.new(params[:release])
    release.save
	end

end

---

Last, we need to create two views, the first allows you to upload the CSV file, the second shows the results of the upload and allows you to process the data. Note: At this point you should probably reload your web server so the fastercsv gem and paperclip plugin are available.

new.html.erb

Upload a CSV file to import into the database

<% form_for @import, :html => { :multipart => true } do |f| %>

  <%= f.file_field :csv %>
<select name="import[datatype]" size="1">
  <option value="releases"> Press Releases </option>
  </select>

    <%= f.submit "Import" %>

<% end %>

show.html.erb

Results of your CSV Upload

<%= @import.csv.path %>

<% if @import.processed > 0 %>

Success! This import contained <%=  @import.processed %> records

<% else %>

<%= link_to "process", import_proc_path(@import.id) %>

<% end %>

Update your routes.rb, add the following line:

map.resources :imports
map.import_proc '/import/proc/:id', :controller => "imports", :action => "proc_csv"

That’s pretty much it. You can alter/expand on this to support multiple models in your application. Just make sure to pay close attention to detail when your mapping columns in the CSV file to values in your model. You may want to add a call somewhere to destroy old CSV import records since you likely don’t need to store the CSV data indefinately. Also, there could be a lot more error checking to this process to make sure the upload is valid data and each row is processed correctly. I hope to follow up this post soon with a Part 2 which adds more data processing details. Last, I know there is a lot of stuff placed in the controller, it’s something i’ve been in the habit of doing. Any input on moving processing to the model would be welcome.