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

score 1 · Accepted Answer · answered Aug 23 '15 at 18:27

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

This works great, which is awesome! Thank you! I will accept the answer. But for the sake of my own learning, what exactley is haopening when `row.keys.each { |k| row[ MAPPING[k] ] = row.delete(k) if MAPPING[k] }`? It looks like `if MAPPING[k] is defined` <-- meaning that if the key is already present, then do `row[ MAPPING[k] ] = row.delete(k)` which translates to delete the existing record. Is that what's happening on this line? — , Aug 23 '15 at 22:40
Glad it worked for you. The problem is that the Roo CSV library is returning a hash for each row of the CSV file with the keys of the hash set to the column headers in the csv file. We need to map those to your database column names. That line loops through each key of the csv row and if it has a mapping defined in the MAPPING constant it 'deletes' the key from the row hash, and assigns a new key with the same value but with our mapped key. — Lewis Buckley, Aug 23 '15 at 22:46

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

1 Answers1