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

Last updated on December 26, 2022

In one of my recent projects, I am receiving Excel files from external APIs and I have to parse and validate data, and then I have to insert it into a 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 the following rails command to create a 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 −

http://localhost:3000

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 
    end
end

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

$ gem install roo

How to run the task

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

$ rake parse_excell