Creating a rake task and Importing data from a file to a table in Rails 5

Introduction

Sometimes, we need to have some tasks automated and need to run every time when we want it. In Rails, we can have it as rake tasks and we can run it any time when we want. And after all the above, its so easy and simple to do it

Creating a rake file

In our application, we should always separate the code which is not dependent on the domain of the application.

For example,

If I am doing a file upload which is a common action, I should keep the code as a common code and use it globally across the application.

These type of codes, I would classify inside the lib folder of the application.

In our instance, we need create a rake for a specific task.

So, create a .rake file inside the /lib/tasks/ folder. For example, create a import.rake file inside the folder for our import task.

It should be in lib/tasks/import.rake

Defining the tasks in the file

Let us look at the structure of the rake file,

# Example rake file structure
# Define a namespace for the task
namespace :import do
  # Give a description for the task
desc “Import Data from Excel”
  # Define the task
task excel: :environment do

# Your task goes here
  end
end

So, this is the structure of the rake file and we have a command to run the task,

rake <namespace>:<task>

Example,

rake import:excel

We have seen how to create a rake file and structure of the rake file and how to run the task. Next, we will see on how to import data from an excel to the table in our app

Requirements

To perform the excel operations, ruby has an awesome gem spreadsheet

Install the gem,

gem install spreadsheet

Or include the gem in your Gemfile,

gem 'spreadsheet'

Have the excel file in the root of your application or have it in the location from where you run the commands (Not compulsory but will be useful)

Using the spreadsheet gem

Trying it out,

Just type irb or pry in your terminal and you will get into the irb console. Now we need to extract content from the .xls file

Require the gem,

require 'spreadsheet'

Declare the file_name variable which store the location of the file

file_name = "Your file location goes here"

Open the file from the location,

excel_data = Spreadsheet.open filename

Select the sheet from the excel file which you want, it is normally the first sheet,

sheet = excel_data.worksheet 0

Now we need to loop through the columns and get the data from each cells,

In ruby style,

sheet.each do |item|
  cell_value = { first: item[0], second: item[1], third: item[2] }
puts cell_value
end

This is the method to loop through the excel file and get the data from it

Storing the data in the table of our app

Now we can look on how to store that in a table. Say we have a table called Users and it has attributes such as name, city, id

sheet.each do |item|
  cell_value = { name: item[0], city: item[1], id: item[2] }
  User.create(cell_value)
end

By this way we can store it in the table as soon as it is extracted.

And now the Final touch…

Now we combine both the rake task and spreadsheet gem and create a rake task to extract the contents of the given file and store it in the table,

Create an import.rake file in lib/tasks directory and copy the contents in it,

require 'spreadsheet'
# The rake task to upload the bank excel file into the branch table
namespace :import do
desc "Import Users from Excel"
task users: :environment do
filename = File.join Rails.root, "user.xls"
excel = Spreadsheet.open filename
sheet = excel.worksheet 0
sheet.each do |item|
value = { name: item[0], city: item[1], id: item[2] }
User.create(value)
end
end
end

This should do it. It will create a rake task and perform the actions. You can run it by,

rake import:users

Lot more can done in rake tasks and also with the spreadsheet gem.

Keep exploring !!! :-)