no implicit conversion of Symbol into Integer using Roo gem / XLS, XLSX, CSV File imports

Question

I am using Roo Gem in rails 5 to handle my data imports.

My CSV import is working fine, but I think its more efficient to use XLS and XLSLX as the end user would be better equipped to use these and I can provide a styled import template for them to use.

the error I receive when I attempt to import a sheet is as follows:

  Parameters: {"utf8"=>"✓", "authenticity_token"=>"X1k7+P2+YQtxJiKzuHZeErDCybH5jjCC1k/Q4OQwVL3hZ+ZEBdkxy8b9vXzGeHXhiMAKbukxvKsVbrQHh4FDFA==", "file"=>#<ActionDispatch::Http::UploadedFile:0x007fadab626f00 @tempfile=#<Tempfile:/var/folders/5z/7q_phgfd14d1cpgcpgwrgrj80000gp/T/RackMultipart20160905-12928-1hbqlew.xlsx>, @original_filename="User_import_excel.xlsx", @content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", @headers="Content-Disposition: form-data; name=\"file\"; filename=\"User_import_excel.xlsx\"\r\nContent-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\r\n">, "commit"=>"Import"}
  User Load (0.3ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT $2  [["id", 1], ["LIMIT", 1]]
Completed 500 Internal Server Error in 16ms (ActiveRecord: 0.3ms)



TypeError (no implicit conversion of Symbol into Integer):

app/models/user.rb:58:in `new'
app/models/user.rb:58:in `open_spreadsheet'
app/models/user.rb:44:in `import'
app/controllers/users_controller.rb:71:in `import'
  Rendering /Users/developer/.rvm/gems/ruby-2.3.1/gems/actionpack-5.0.0.1/lib/action_dispatch/middleware/templates/rescues/diagnostics.html.erb within rescues/layout
  Rendering /Users/developer/.rvm/gems/ruby-2.3.1/gems/actionpack-5.0.0.1/lib/action_dispatch/middleware/templates/rescues/_source.html.erb
  Rendered /Users/developer/.rvm/gems/ruby-2.3.1/gems/actionpack-5.0.0.1/lib/action_dispatch/middleware/templates/rescues/_source.html.erb (2.6ms)
  Rendering /Users/developer/.rvm/gems/ruby-2.3.1/gems/actionpack-5.0.0.1/lib/action_dispatch/middleware/templates/rescues/_trace.html.erb
  Rendered /Users/developer/.rvm/gems/ruby-2.3.1/gems/actionpack-5.0.0.1/lib/action_dispatch/middleware/templates/rescues/_trace.html.erb (1.4ms)
  Rendering /Users/developer/.rvm/gems/ruby-2.3.1/gems/actionpack-5.0.0.1/lib/action_dispatch/middleware/templates/rescues/_request_and_response.html.erb
  Rendered /Users/developer/.rvm/gems/ruby-2.3.1/gems/actionpack-5.0.0.1/lib/action_dispatch/middleware/templates/rescues/_request_and_response.html.erb (1.1ms)
  Rendered /Users/developer/.rvm/gems/ruby-2.3.1/gems/actionpack-5.0.0.1/lib/action_dispatch/middleware/templates/rescues/diagnostics.html.erb within rescues/layout (49.8ms)

Another problem I am facing is when I Try to import a CSV file that has previous users in it to update them in bulk, it just gives me an error that says the email is already taken. If i could get assistance with this error too that would be fantastic!

My users controller import action:

  def import
    User.import(params[:file])
    redirect_to users_path, notice: "Users successfully imported"
  end

My Users Model

  # Accessible Attributes
  def accessible_attributes
    [:email, :f_name, :l_name, :telephone]
  end

  # XLSL Importer
  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(*row.to_hash.keys)
      product.save!
    end
  end

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

Please let me know if you require any other information. Any assistance with this would be greatly appreciated!

EDIT # 1

score 2 · Accepted Answer · answered Sep 06 '16 at 05:43

2

So it turns out that I needed to change

when ".xls" then Roo::Excel.new(file.path, nil, :ignore)

to

when ".xlsx" then Roo::Excelx.new(file.path, file_warning: :ignore)

and then I had to add

gem 'roo-xls'

in order to import xls files.

answered Sep 06 '16 at 05:43

Shawn Wilson

1,311
14
40

score 0 · Answer 2 · answered Mar 03 '21 at 09:58

Using the most recent version of Roo, the code below works for me assumming file = params[:uploaded_file].path somewhere in your controller)

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)
  when ".csv" then Roo::CSV.new(file, headers: true) 
  when ".xls" then Roo::Excel.new(file, :ignore)
  when ".xlsx" then Roo::Excelx.new(file, :ignore)
  else raise "Unknown file type: #{file}"
  end
end

no implicit conversion of Symbol into Integer using Roo gem / XLS, XLSX, CSV File imports

2 Answers2