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.
1 2 3 rails new roo_app rails generate model Post title body author rails db:create db:migrate
roo gem and run
1 gem ‘roo’
As mentioned earlier, you will be importing data from an Excel sheet through a rake task. Go ahead and generate one.
1 rails g task posts import
lib/tasks/posts.rake, put the following code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 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, body: record, author: record) puts post.title puts post.body puts post.author end end end
Test your implementation.
1 2 3 4 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!