Recently I was trying to find ways to import data from excel spreadsheets into the database in a rails project. We needed to save the data from the client’s excel file into the database. Here’s how it can be done using a rake task.

  1. The file will have to be in CSV format, so you’ll have to open it with any spreadsheet application like Microsoft Excel (or Google Docs in my case) and save it as CSV. Put the file in your project folder.
  2. FasterCSV is a ruby gem that lets you easily parse CSV files and is a faster alternative to ruby’s standard CSV library. Install it using the command: 
    gem install fastercsv
  3. Create a rake task that reads the CSV file using the fastercsv gem and saves the data in the database. Your code should look something like this: 
    namespace :db do

  desc "load user data from csv"
  task :load_csv_data  => :environment do
    require 'fastercsv'

    FasterCSV.foreach("data.csv") do |row|
      User.create(
        :user_name => row[0],
        :email => row[1],
        :password => row[2]
      )
    end
  end
end

    You can replace the User model here with whatever model you’re importing from the CSV file.

  4. Save the task as a rake file in lib/tasks folder and name it import_csv_data.rake or something to that effect.
  5. Go to the shell and run the task by: 
    rake db:load_csv_data

When I came across this problem, my first reaction was to look for a ruby library that reads spreadsheets directly. But that’s a challenge for another day. For this particular project we can manually save the spreadsheets as CSV.

I did find a library called roo (http://roo.rubyforge.org/) that can access the contents of a spreadsheet directly and it might be worth looking into if you need to read an uploaded spreadsheet directly.

Have you had to do something similar in your projects? What other method would you suggest to accomplish this task?