csvruby-on-rails-5roo

ActiveRecord::AssociationTypeMismatch Rails CSV Import


I am using gem roo to import CSV data. It works smoothly, until the point where there is an association, and am hoping that roo can translate the string into the corresponding integer value in the association. In my case, I have a Staff model which belongs to State.

class State < ApplicationRecord
    has_many :staffs

end
class Staff < ApplicationRecord
    belongs_to :state

end

This means that I have state_id column in the staffs table. In my CSV, however, the end user has the names of the states, which correspond to the ones in the states tables. When I try to import the CSV, I get the error:

ActiveRecord::AssociationTypeMismatch in StaffsImportsController#create
State(#134576500) expected, got "Texas" which is an instance of String(#20512180)

The highlighted source is:

staff.attributes = row.to_hash

Is it possible for gem roo to translate 'Texas' in the csv file to, say, id 2, instead of the end user doing a lot of translation work before uploading the data?

Here is staffs_imports.rb

class StaffsImport
  include ActiveModel::Model
  require 'roo'

  attr_accessor :file

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

  def persisted?
    false
  end

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

  def load_imported_staffs
    spreadsheet = open_spreadsheet
    header = spreadsheet.row(1)
    (2..spreadsheet.last_row).map do |i|
      row = Hash[[header, spreadsheet.row(i)].transpose]
      staff = Staff.find_by_national_id(row["national_id"]) || Staff.new
      staff.attributes = row.to_hash
      staff
    end
  end

  def imported_staffs
    @imported_staffs ||= load_imported_staffs
  end

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

end

And finally the staff_imports_controller.rb:

class StaffsImportsController < ApplicationController

  def new
    @staffs_import = StaffsImport.new
  end

  def create
    @staffs_import = StaffsImport.new(params[:staffs_import])
    if @staffs_import.save
      flash[:success] = "You have successfully uploaded your staff!"
      redirect_to staffs_path
    else
      render :new
    end
  end
end

Any help/clues will be highly appreciated.


Solution

  • I managed to get a solution to this, thanks to a wonderfully detailed question and great answer provided here Importing CSV data into Rails app, using something other then the association "id"