importing records from .csv file creates completely blank rows in database

Question

All source code is hosted on my GitHub in the cloud_contacts repo.

I am adapting Rails Cast #396 to work with Rails 4. I've got it working -sort of- and it will import a file full of test data. It will then create a record in the database for each corresponding line (or contact) in my .csv file.

I can also add new contacts manually in my view, which was functionality that I buil myself, unrelated to the Rails Cast.

My question is, why is every contact record coming in as blank? I believe that the problem is with the import method of my Contact model, and perhaps that when each record gets transposed the column names from the header are not matching the column names in my database. If this is the case (and I am totally guessing on that...) how do I fix it?

All source code is hosted on my GitHub in the cloud_contacts repo.

But in case you hate Christmas and GitHub and don't want to look at the repo, here is my Contact.rb model:

class Contact < ActiveRecord::Base
  # attr_accessible :first_name, :last_name, :email_address

  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]
      contact = find_by_id(row["id"]) || new
      contact.attributes = row.to_hash.slice(*accessible_attributes)
      # contact.attributes = row.to_hash.slice(*row.to_hash.keys)
      contact.save!
    end
  end

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

  private

  def self.accessible_attributes
    [:first_name, :last_name, :email_address]
  end

end

Here is a subset of the data in my csv file:

First Name  Last Name   Email Address   Phone Number    Company Name
Gerhard Kautzer gerhardkautzer@cronabayer.com   1-207-643-1816  Hodkiewicz-Lynch
Myra    Crona   myracrona@schinner.info (724)196-9470 x998  Champlin-Hahn
Josh    Donnelly    joshdonnelly@macejkovic.us  081-799-3139 x248   Casper Group
Verna   Farrell vernafarrell@schillercorkery.name   731.101.6219    Rosenbaum-Hane
Lauriane    Stracke laurianestracke@tremblayturner.biz  1-033-511-1831 x471 Prohaska-Sporer
Kaya    Luettgen    kayaluettgen@christiansen.name  (511)745-9273   Wyman, Trantow and Hane
Steve   Davis   stevedavis@shields.info 787.315.2611 x747   Kuhic-Lowe
Citlalli    Pfeffer citlallipfeffer@lemkeblanda.co.uk   329-584-6962 x047   Gorczany and Sons
Litzy   Turcotte    litzyturcotte@weber.name    1-084-641-4078 x4410    Hintz-Schmitt
River   Lockman riverlockman@shieldsgrant.ca    1-967-129-8359  Rowe LLC
Juvenal Berge   juvenalberge@nienow.co.uk   037-748-7238    Hane Inc

Answer 1

I've made a few changes to your Contact model, please see my comments below.

Ps. You might find it helpful to use the better_errors gem to give you visibility into what your variables look like at any point.

class Contact < ActiveRecord::Base

  MAPPING = {
    "First Name" => "first_name",
    "Last Name" => "last_name",
    "Email Address" => "email_address"
  }

  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]
      # Convert the keys from the csv to match the database column names
      row.keys.each { |k| row[ MAPPING[k] ] = row.delete(k) if MAPPING[k] }
      # Remove company and phone number fields as these aren't in the database:
      create(row.except!('Company Name', 'Phone Number'))
    end
  end

  def self.open_spreadsheet(file)
    case File.extname(file.original_filename)
      # You're using a tab seperated file, so specify seperator as a tab with \t
      when ".csv" then Roo::CSV.new(file.path, csv_options: {col_sep: "\t"})
      when ".xls" then Roo::Excel.new(file.path)
      when ".xlsx" then Roo::Excelx.new(file.path)
      else raise "Unknown file type: #{file.original_filename}"
    end
  end

end