Import Records from CSV and Excel in Rails 5

We will continue working on the project we created in Exporting Records in CSV and Excel Formats in Rails 5. Add:

gem 'roo'

to Gemfile and run bundle. In products/index.html.erb, add file upload field:

<h2>Import Products</h2>
<%= form_tag import_products_path, multipart: true do %>
  <%= file_field_tag :file %>
  <%= submit_tag "Import" %>
<% end %>

Define route to import data from the uploaded file.

resources :products do
  collection do
    post :import
  end
end

This defines the following route.

import_products POST   /products/import(.:format)   products#import

Implement import action in products controller.

def import
  Product.import(params[:file])

  redirect_to root_url, notice: 'Products imported.'
end

This gives the error:

uninitialized constant Roo::Csv

Change:

Roo::Csv

to

Roo::CSV

In product model, change:

spreadsheet = Roo::Spreadsheet.open(file.path)

We get the error, ActiveRecord::NotFound exception. Change the import line that finds a record like this:

product = find_by(id: row["id"]) || new

The entire method now looks like this:

def self.import(file)
  spreadsheet = Roo::Spreadsheet.open(file.path)
  header = spreadsheet.row(1)
  (2..spreadsheet.last_row).each do |i|
    row = Hash[[header, spreadsheet.row(i)].transpose]
    product = find_by(id: row["id"]) || new
    product.attributes = row.to_hash
    product.save!
  end
end  

Make sure to name the headers have the same value as the column name in the database, so the header should be id, name and released_on. Delete this:

def self.open_spreadsheet(file)
  case File.extname(file.original_filename)
  when ".csv" then Roo::CSV.new(file.path, nil, :ignore)
  when ".xls" then Roo::Excel.new(file.path, nil, :ignore)
  when ".xlsx" then Roo::Excelx.new(file.path, nil, :ignore)
  else raise "Unknown file type: #{file.original_filename}"
  end
end

This uses Roo Gem version 2.4.0. The railscast uses Roo gem 1.10.1. You can download the code for this article from exp.


Related Articles

Watch this Article as Screencast

You can watch this as a screencast Import Records from CSV and Excel in Rails 5


Software Compatibility Best Practices

I spoke to some of the most talented and experienced software developers. I have created a guide that is filled with valuable insights and actionable ideas to boost developer productivity.

You will gain a better understanding of what's working well for other developers and how they address the software compatibility problems.

Get the Guide Now