Import data from excel to mysql in rails3 using roo gem

Roo gem allows us to access the contents of

  • Open-office spreadsheets (.ods)
  • Excel spreadsheets (.xls)
  • Google (online) spreadsheets
  • Excel’s new file format .xlsx

Following is a basic example to demonstrate how to get the datas from an excel sheet and save to your database. Suppose you have an excel sheet named student_details.xls which contain the student details such as “Name”,”Phone”,”Address”.We have to save them in our database but it will take a huge time if we insert them manually.Here we can user “roo” gem to fetch the datas from excel sheet and save to database in a fly.


Add the gem in your gem file

gem ‘roo’

Then run the bundle

bundle install


Place your excel sheet in the rails application such as in public folder.


Now its time to change your controller like below.

Add the following line in your controller

require ‘roo’

Create a new method where you will fetch the data from excel sheet and save it in your database.

def  fetch_excel_data

ex =“#{Rails.root}/public/student_details.xls”)
ex.default_sheet = ex.sheets[1] #Mention the sheet number
3.upto(1000) do |line| #start and end of row
name = ex.cell(line,’A’)
phone = ex.cell(line,’B’)
adr = ex.cell(line,’C’)

@student = Student.create(:name => name,:phone => phone,:address => adr)


when you call this method the roo gem will fetch the datas accordingly and save to your database.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s