3

I'm trying to import CSV and Excel files into a rails 4 project (with validation) using the Roo gem, based on http://railscasts.com/episodes/396-importing-csv-and-excel.

I've made some changes to account for Rails4 instead of Rails3 and for changes to Roo, and my ProjectImporter model now looks like:

class ProductImport
  include ActiveModel::Model
  attr_accessor :file

  def initialize(attributes = {})
    attributes.each { |name, value| send("#{name}=", value) }
  end

  def persisted?
    false
  end

  def save
    if imported_products.map(&:valid?).all?
      imported_products.each(&:save!)
      true
    else
      imported_products.each_with_index do |product, index|
        product.errors.full_messages.each do |message|
          errors.add :base, "Row #{index + 2}: #{message}"
        end
      end
      false
    end
  end

  def imported_products
    @imported_products ||= load_imported_products
  end

  def load_imported_products
    spreadsheet = open_spreadsheet
    spreadsheet.default_sheet = spreadsheet.sheets.first
    puts "!!! Spreadsheet: #{spreadsheet}"
    header = spreadsheet.row(1)
    (2..spreadsheet.last_row).map do |i|
      row = Hash[[header, spreadsheet.row(i)].transpose]
      product = Product.find_by(id: row['id']) || Product.new
      product.attributes = row.to_hash.slice(*['name', 'released_on', 'price'])
      product
    end
  end

  def open_spreadsheet
    case File.extname(file.original_filename)
      when ".csv" then
        Roo::CSV.new(file.path, nil)
      when '.tsv' then
        Roo::CSV.new(file.path, csv_options: { col_sep: "\t" })
      when '.xls' then
        Roo::Excel.new(file.path, nil, :ignore)
      when '.xlsx' then
        Roo::Excelx.new(file.path, nil, :ignore)
      when '.ods' then
        Roo::OpenOffice.new(file.path, nil, :ignore)
      else
        raise "Unknown file type #{file.original_filename}"
    end
  end
end

When I try to run an import (using test CSV data), it fails on header = spreadsheet.row(1) with the error undefined method '[]' for nil:NilClass. The extra puts statement I've included confirms that spreadsheet itself isn't nil: it gives !!! Spreadsheet: #<Roo::CSV:0x44c2c98>. But if I try to call almost any of the expected methods on it, such as #last_row, it gives me the same undefined method error.

So what am I doing wrong?

skaffman
  • 398,947
  • 96
  • 818
  • 769
digitig
  • 1,989
  • 3
  • 25
  • 45
  • 1
    It likely tells you exactly what line the `NilClass` error is occurring. Please provide that. – deefour Oct 30 '14 at 16:33
  • I have already provided that. As I wrote, "it fails on `header = spreadsheet.row(1)`". That's the 4th line of `load_imported_products`, immediately after the debugging `puts` I inserted to check `spreadsheet` wasn't `nil`. – digitig Nov 04 '14 at 09:15
  • 1
    `.row(1)` is not the method `[]`. I am asking for the full call stack when the error is raised. `[]` is likely coming from within Roo - you should see a line in the call stack referencing that. `.row(1)` is just the failing API call you're making that triggers the error. – deefour Nov 04 '14 at 13:10

1 Answers1

7

I had the same problem, it seems a problem about file enconding, I used this code and it was fixed.

def open_spreadsheet
    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, nil, :ignore)
        else raise "Unknown file type: #{file.original_filename}"           
    end 
end

I hope that helps for you.

Alexander
  • 71
  • 1
  • 2