How to parse spreadsheets with Roo gem in Ruby on Rails

It’s common to need to read data from .csv or .xlsx files and insert that data into the database. This is a frequent use case in web development. In this article, we will discuss this use case in Ruby on Rails applications.

We will be using roo gem and will be writing a rake task to import data and insert it into the database.

First, set up an application.

rails new roo_app
rails generate model Post title body author
rails db:create db:migrate

In Gemfile add roo gem and run bundle install.

gem ‘roo’

As mentioned earlier, you will be importing data from an Excel sheet through a rake task. Go ahead and generate one.

rails g task posts import 

In the lib/tasks/posts.rake, put the following code:

namespace :posts do
 desc "This task will import posts from excel sheet and insert them in posts table"
 task import: :environment do
   sheet = Roo::Spreadsheet.open('./Post.xlsx')

   (2..sheet.last_row).each do |row|
     record = sheet.row(row)
     post = Post.create(title: record[0], body: record[1], author: record[2])
     puts post.title
     puts post.body
     puts post.author
   end
 end

end

Test your implementation.

rails console
Post.count
(2.5ms)  SELECT COUNT(*) FROM "posts"
=> 3 

Now you know how to fetch data from Excel sheets in Ruby. Happy fetching!