Store hash extracted from xlsx in database in ruby using roo gem

Question

Sorry for my bad english and for any stupid error, but I'm learning Ruby since few month. I'm trying to read from a .xlsx file usign roo gem and after I would store my rows in the database, in a already existing model. Here there is my Court model:

class Court < ApplicationRecord

  belongs_to :type

  validates :type_id, presence: true
  validates :name, presence: true
  validates :email, presence: true, uniqueness: true
  validates :responsible, presence: true
  validates :address, presence: true
  validates :telephone, presence: true, format: { with: /\A([0-9]*\-?\ ?\/?[0-9]*)\Z/ }

  geocoded_by :address
  after_validation :geocode, :if => :address_changed?

end

# == Schema Information
#
# Table name: courts
#
#  id                  :integer          not null, primary key
#  type_id             :integer
#  name                :string(255)
#  email               :string(255)
#  email_type          :string(255)
#  responsible         :string(255)
#  address             :string(255)
#  telephone           :string(255)
#  latitude            :decimal
#  longitude           :decimal
#  created_at          :datetime         not null
#  updated_at          :datetime         not null
#

and I created a task as follow, excel.rake, to read with roo all the file:

namespace :excel do

    desc "Import Courts from Excel"
    task import_courts: :environment do
      xlsx = Roo::Excelx.new(Rails.root.join('app', 'assets', 'excel', 'Tribunali.xlsx'))
      xlsx.parse(headers: true) do |hash|
        puts hash.inspect # Array of Excelx::Cell objects
        @court = Court.new
        @court.hash
        @court.save
      end
    end
end

In the file I have the same headers of the model, indeed when I launch the task I obtain the following output:

{"Tipo"=>"Tipo", "Nome"=>"Nome", "Indirizzo e-mail"=>"Indirizzo e-mail", "Tipo email"=>"Tipo email", "Responsabile"=>"Responsabile", "Indirizzo"=>"Indirizzo", "Numero Telefonico"=>"Numero Telefonico"}
{"Tipo"=>"AMM", "Nome"=>"Ministero della Giustizia", "Indirizzo e-mail"=>"[email protected]", "Tipo email"=>"PEC", "Responsabile"=>"Alfonso Bonafede", "Indirizzo"=>"Via Arenula, 70 - 00186 Roma (RM)", "Numero Telefonico"=>"06 68851"}
{"Tipo"=>"AOO", "Nome"=>"Archivio Notarile Distrettuale di Agrigento", "Indirizzo e-mail"=>"[email protected]", "Tipo email"=>"PEC", "Responsabile"=>"Daniela Portera", "Indirizzo"=>"Via S. Vito, 97/103 - 92100 Agrigento (AG)", "Numero Telefonico"=>"09 2220290"}
{"Tipo"=>"AOO", "Nome"=>"Archivio Notarile Distrettuale di Alessandria", "Indirizzo e-mail"=>"[email protected]", "Tipo email"=>"PEC", "Responsabile"=>"Susanna Cesarone Bongiorno", "Indirizzo"=>"Via Ghilini, 42 - 15121 Alessandria (AL)", "Numero Telefonico"=>"01 31254163"}
{"Tipo"=>"AOO", "Nome"=>"Archivio Notarile Distrettuale di Ancona", "Indirizzo e-mail"=>"[email protected]", "Tipo email"=>"PEC", "Responsabile"=>"Margherita Regini Santojanni", "Indirizzo"=>"Piazzale Europa, 7 - 60125 Ancona (AN)", "Numero Telefonico"=>"07 12804055"}
{"Tipo"=>"AOO", "Nome"=>"Archivio Notarile Distrettuale di Aosta", "Indirizzo e-mail"=>"[email protected]", "Tipo email"=>"PEC", "Responsabile"=>"Antonio Santoro", "Indirizzo"=>"Via Monsignor De Sales, 3 - 11100 Aosta (AO)", "Numero Telefonico"=>"01 65361395"}
{"Tipo"=>"AOO", "Nome"=>"Archivio Notarile Distrettuale di Arezzo", "Indirizzo e-mail"=>"[email protected]", "Tipo email"=>"PEC", "Responsabile"=>"Gianna Baroni Pedone", "Indirizzo"=>"Via Francesco Crispi, 58/4 - 52100 Arezzo (AR)", "Numero Telefonico"=>"05 7523243"}

But in the database I'm not able to store these data. Someone can help me please? Sorry in advance if I have forgotten something to tell you.

Answer 1

I've never worked with the "roo" gem, but looking a bit at their documentation and code. Something like this should do the job:

# header_names and attribute_names must be provided in the same order
# since we work with them based on index later on (#fetch_values and #zip)
header_names = ['Tipo', 'Nome', '...']
attribute_names = [:type, :name, :'...']

Court.transaction do
  # use headers: false to not return the headers
  xlsx.parse(headers: false).each do |row|
    values = row.fetch_values(*header_names)
    attributes = attribute_names.zip(values).to_h
    Court.create!(attributes)
  end
end

Since the header names in the sheet and attribute names of the models are different you need to convert the row returned, which should be a core Hash instance.

You can retrieve the values of the fields using fetch_values (raises an exception when a key is missing) or values_at (returns the default value when a key is missing, which when not set is nil). Then zip the headers of the model with the values and convert the whole thing back into a hash.

Then use create! to create an Court instance and save it to the database. This raises an exception when something goes wrong rolling back the transaction. You could also use create, but this doesn't raise an exception. Meaning that invalid records are silently swallowed and simply not saved.

One thing to point out is that your current output shows "Tipo"=>"AMM", where "AMM" is the type I'm guessing. However this is not an id, so you might need to fetch the type ids and maybe other ids first before attempting to save.

Something like this:

attributes_list = xlsx.parse(headers: false).map do |row|
  values = row.fetch_values(*header_names)
  attributes = attribute_names.zip(values).to_h
end

type_ids = Type.where(name: attributes_list.pluck(:type).uniq).pluck(:name, :id).to_h
# fetch other ids if needed.

attributes_list.each do |attributes|
  # remove :type and replace with :type_id (using #fetch to detect missing types)
  attributes[:type_id] = type_ids.fetch(attributes.delete(:type))
end

# create and save Court instances from the attributes
Court.transaction do
  attributes_list.each { |attributes| Court.create!(attributes) }
end