January 13, 2020
Gabriel Martin
Tags:
Importing data from an Excel spreadsheet doesn’t need to be difficult. While Rails doesn’t include a native utility to handle these file types, there are several gems that make it quite easy to read/write Excel spreadsheets.
In this post, we will create a rake task and use the roogem to read from an Excel file and import data into the database.
And install.
Now that we’ve added roo, we can start working on the actual feature.
Generate the rake task.
Now that our task is created, let’s update the description. We’ll also add a simple puts command and ensure that it runs.
Run this command in your console.
To keep it simple, I have a very basic user schema. Where a user has two fields, name and email. I’ve added the spreadsheet file at lib/data.xlsx which contains several rows, each representing a new user to be created.
As you can see, the first row of the spreadsheet represents the headers. Everything else is the actual data that we need. Let’s start implementing roo so we can map over this data and create the users.
First, we will require roo and open the spreadsheet within the task.
Next, lets grab the first row of the spreadsheet since we know this is the header row. We’ll use this later to create a hash when mapping over the rows with the data we need.
Now we can map over the spreadsheet rows and extract the user data. Here is what the code looks like.
Let’ walk through this.
Let’s start with Array#transpose. I’m not going to dive into the transpose method but it essentially turns columns into rows when you have a multi-dimensional array. Here’s a quick visual.
Then we check to see if a user already exists with the current email address. If it does, we print some text to the console and move on to the next iteration without saving.
And finally, if the user doesn’t already exist, we can create a new user instance with the fields generated from the current row and save the new user in the database.
The complete code should look something like this.
That’s it! Now you can run this task and import users from the spreadsheet.
While this approach uses a very simple data structure, it’s a good starting point even for more complex situations.
