#396 Importing CSV and Excel

Dec 08, 2012 | 10 minutes | Plugins

Allow users to import records into the database by uploading a CSV or Excel document. Here I show how to use Roo to parse these files and present a solution for validations.

Click to Play Video ▶

Download:
source codeProject Files in Zip (90.8 KB)
mp4Full Size H.264 Video (24.6 MB)
m4vSmaller H.264 Video (12 MB)
webmFull Size VP8 Video (14.8 MB)
ogvFull Size Theora Video (29.5 MB)

Browse Source Code

Resources

          Gemfile
        
gem 'roo'

          products/index.html.erb
        
<h2>Import Products</h2>

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

          config/routes.rb
        
resources :products do
  collection { post :import }
end

          config/application.rb
        
require 'csv'
require 'iconv'

          products_controller.rb
        
def import
  Product.import(params[:file])
  redirect_to root_url, notice: "Products imported."
end

          models/product.rb
        
def self.import(file)
  spreadsheet = open_spreadsheet(file)
  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.slice(*accessible_attributes)
    product.save!
  end
end

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

See the project with validation handling