How to import data from an Excel file in Rails

3 min readOct 8, 2022

With the roo gem

I had to do this recently for work and I was surprised at how easy it was, so I decided to make it even easier for you and make a blog post about it.

In this post, I will use the gem roo.

Demo project setup

To make it as clear and easy as possible, I will simply import users.

The users table looks like this:

users table in schema.rb

Import data from an Excel file

Create the file

You obviously need an Excel file for this to work. If you do not have Microsoft Office 365, don’t worry just use Google Sheets and download it as an Excel file (that’s how I did it).

Each column is for a row in your database. The one I will use looks like this:

Installing the gem

Add the gem to your Gemfile and run bundle install.

gem "roo", "~> 2.9.0"

(Or whatever the current version is for you)

Create the import method

The finished method looks like this and is inside the UsersController.rb :

Let’s go through it step by step:

The first line opens the file and makes it accessible to us. The xlsx_path will be explained later in the rake task section.

xlsx = Roo::Spreadsheet.open(path[:xlsx_path])

Then, it chooses the first sheet xlsx.sheet(0) and iterates over the rows .each_with_index. The round brackets are not necessary, I simply think it makes it much more readable this way.

Without the brackets, row would be a simple array with the values of the current row in it. The records would be accessible by writing row[x]. This would quickly become messy, because I don’t think row[8] would make much sense anymore. Example: row[0] would return the firstname, row[1] the lastname, etc.

With the brackets, it creates a hash which makes it more readable in my opinion; row[0] turns into row[:firstname].

xlsx.sheet(0).each_with_index(firstname: 'First name', lastname: 'Surname', username: 'Username', email: 'E-Mail', age: 'Age') do |row, row_index|  stuff..end

Next, we skip over the first row since it’s just the headers and we also go to the next row if a user with that username already exists.

next if row_index == 0 || User.find_by(username: row[:username]).present?

Lastly, we create a new user with the information we receive.

User.create(
  firstname: row[:firstname],
  lastname: row[:lastname],
  username: row[:username],
  email: row[:email],
  age: row[:age]
)

Create a rake task to run the method

Run the generator to create a rake task:

rails g task import user_data

You will pass the path to the .xlsx file as an argument. Inside the task, we call the import_data method and pass the path to it:

The command to run the rake task is as follows:

rake import:user_data['path']

And that’s it! Much easier than I at first thought.

Thank you for reading!