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.

Step#1

Add the gem in your gem file

gem ‘roo’

Then run the bundle

bundle install

Step#2

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

Step#3

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 = Excel.new(“#{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)

end
end

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