Importing Excel Data into a Ruby on Rails Application

So you've got some Excel data you would like to import into your Ruby on Rails project? 

As a software engineer you may not be responsible for data integrity and quality control. You're given a spreadsheet and your job is to import it and render the information into a web application. But what if you are given bad data? A little bit of basic quality control can go a long way in saving you from future bugs in your application. You can save yourself some time and trouble just by taking a quick peak at your data before importing it. 

Paw Patrol Puppy GIF By WesternCPE

Ok, now that you have ensured your data is valid and well-structured let's get it into our database!

First, set up your application and your MVC (models, views, controllers) in your CLI (command line interface).

    1 rails new my_app

    2 rails generate resource Product product_title description price:integer (insert

        the rest of your column names)

    3 rails db:create db:migrate

There are many gems available to work with spreadsheets in Ruby, I chose to use Roo. 

    

    1 gem install roo

    2 bundle update

Create a rails task in the appropriate database folder manually or using your CLI.

    

    $ rails g task products import

This will create a rake file within your "./db/lib/tasks" folder. Here is the task I created to import my Products data:

namespace :products do

desc "This task will import products from excel sheet and insert them in products table"

task import: :environment do

sheet = Roo::Spreadsheet.open('./db/malas-data-20220207.xlsx')

(2..sheet.last_row).each do |row|

record = sheet.row(row)

product = Product.create(product_title: record[0], description: record[1], 

        price: record[2], currency_code: record[3], quantity: record[4], tags: record[5], 

         materials: record[6], image1: record[7], image2: record[8], image3: record[9], 

         image4: record[10], image5: record[11], image6: record[12], image7: record[13], 

         image8: record[14], image9: record[15], image10: record[16], sku: record[17], 

         status: record[18])

puts product.product_title

end

end

end

Using your IRB or rails console check which rails tasks are available to you by running the command 

    

    $ rails _T

        OR

    $ rails --tasks

If you see a task with the name  rails products:import   then we are in good shape! Let's run this task in our command line.

    

    $ rails products:import

Check your rails console to ensure your Products are coming through:

    

       2.7.4 :001 > Product.count         (5.3ms) SELECT COUNT(*) FROM "products"         => 24

CONGRATULATIONS! Now you know how to fetch data from Excel sheets in Ruby. 

Fetch Mean Girls GIF By Paramount Movies

Stay tuned for the next post on "What Happens When Excel Decides to Prettify Your Data For You When You Don't Want It To (and More Reasons to Use .CSV Files and Not .XLSX Files)" ...