Full Stack LAMP - MEAN Developer, Python developer. Certified Azure Developer. Freelance programmer/consultant/trainer.

How to create a rake task to parse Excel file and insert into a table in ROR

In one of my recent project, I am receiving Excel files from external APIs and I have to parse, validate data and then I have to insert into database table. So I have created a task to automate this process, this task will run every time when there is a change in the directory (Whenever I receive a new file). In this post, I am not going to share the actual implantation, but I will share a part of it here.

Create Project

Fire up the terminal and run following rails command to create project, here excel-parse is project name:

$ rails new excel-parse-demo

It will generate a excel-parse-demo rail project; we will discuss it later. Currently, we have to check if the environment is set up or not. Next, use the following command to run the web server on your machine.

$ cd excel-parse-demo
$ rails server

Now open your browser and type the following −


It should display a message, something like, “Welcome aboard” or “you’are on Rails”.

Create a Task

Once you are done with the setup, let’s create a file called parse_excell.rake in app\lib\task(you can also use the rails g task command to generate a task file.) and copy-paste the below code:

task :parse_excell => :environment do
    require 'roo'
    workbook = Roo::Excelx.new(File.join(__dir__,"/groups.xlsx")) # read xlsx file.
    workbook.default_sheet = workbook.sheets.first # select first sheet
    GroupMapper.delete_all # delete all before insert 
    ((workbook.first_row + 1)..(workbook.last_row)).each do |row_number| # start loop from the second row, as first row has headings and map the data to local varaibles
        group =  workbook.row(row_number)[2].to_s
        group_label = workbook.row(row_number)[3].to_s
        vendor = workbook.row(row_number)[0].to_s
        group_map = GroupMapper.new(:cuisine => cuisine, :group => group_label,:vendor => vendor)  # insert data into table, you can also do bulk inset if you have more data to be inserted.
        group_map .save 

Let me go over the code, to read .xlsx file we are using roo gem, and inorder to run this task you have to install this gem, to install run below command,

$ gem install roo

How to run task

From the root of the project run the task just like another rails task.

$ rake parse_excell 

Leave a Reply