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.
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).to_s group_label = workbook.row(row_number).to_s vendor = workbook.row(row_number).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 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