How to import relational data from excel file

Question

I'm following the railcast tutorial https://www.youtube.com/watch?v=_NSBm_Q431Y&t=487s on importing data using an excel file to the database using the gem roo. Importing and exporting data work fine, but at the time of importing relational data it shows me the following error:

unknown attribute 'PzaXCja' for Producto.
Extracted source (around line #73):

      row = Hash[[header, spreadsheet.row(i)].transpose]
      producto = find_by_Clave(row["Clave"]) || new
      producto.attributes = row.to_hash.slice(*row.to_hash.keys) 
      producto.save!
    end
  end

this are my methods in the model "producto":

  has_one :productosxpza, class_name: "Productosxpza", foreign_key: "Producto"
  accepts_nested_attributes_for :productosxpza

  def self.to_csv(options = {})#exportar
    CSV.generate(options) do |csv|
      csv << column_names
      all.each do |producto|
        csv << producto.attributes.values_at(*column_names)
      end
    end
  end

  def self.import(file)#importar
    spreadsheet = open_spreadsheet(file)
    header = spreadsheet.row(1)
    (2..spreadsheet.last_row).each do |i|
      row = Hash[[header, spreadsheet.row(i)].transpose]
      producto = find_by_Clave(row["Clave"]) || new
      producto.attributes = row.to_hash.slice(*row.to_hash.keys) #*row.to_hash.keys para rails 4 que sustituye el attr_accesible de rails 3
      producto.save!
    end
  end

  def self.open_spreadsheet(file)#importar
    case File.extname(file.original_filename)
     when '.csv' then Roo::Csv.new(file.path, packed: false, file_warning: :ignore)
     #when '.xls' then Roo::Excel.new(file.path, packed: false, file_warning: :ignore)
     when '.xlsx' then Roo::Excelx.new(file.path, packed: false, file_warning: :ignore)
     #else raise "Unknown file type: #{file.original_filename}"
     else raise "El formato debe ser .xlsx ó .csv"


     end
  end

Do print out what the variable row is Its probably not in the correct format for `producto.attributes = { key: value }` — Ho Man, Jan 23 '17 at 06:32
@HoMan That method would allow me to create records with associations? — LuisC, Jan 23 '17 at 22:05
@HoMan When I do this: producto.attributes = {Clave: Clave} I get the following error: uninitialized constant Producto::Clave — LuisC, Jan 24 '17 at 20:02
The key should be the attribute name. So if the product has an attribute called `name` then it should be `producto.attributes = {name: 'Clave'}` — Ho Man, Jan 25 '17 at 01:29
@HoMan Ok thanks. as for the question, how could I create related data?, product has a productoxpza. I try this: producto.attributes = {Clave: row["Clave"], PzaXCja: row["PzaXCja"]} but I get this error: unknown attribute 'PzaXCja' for Product. — LuisC, Jan 25 '17 at 05:27

score 1 · Answer 1 · answered Jan 25 '17 at 06:15

1

If I got you correctly, you're wanting to set attributes on productosxpza but via producto right? This should work.

producto.productosxpza_attributes = { ... }

answered Jan 25 '17 at 06:15

Ho Man

2,308
11
16

Correct it work, import the data in the related table, but now I have a small error when I upload an existing record does not update it as before but it sends me this error: Cannot insert the value NULL into column 'Producto', table 'AdvanceControl_Copy.dbo.ProductosXPzas'; column does not allow nulls. UPDATE fails.: EXEC sp_executesql N'UPDATE [productosxpzas] SET [Producto] = @0 WHERE [productosxpzas].[IDP] = @1; SELECT @@ROWCOUNT AS AffectedRows', N'@0 varchar(50), @1 int', @0 = NULL, @1 = 165 – LuisC Jan 25 '17 at 06:53
I try this to update also the productosxpza: productosxpza = find_by_Producto(row["Clave"]) || new producto.productosxpza_attributes = {PzaXCja: row["PzaXCja"], Producto: row["Clave"]} producto.save! #(producto in productosxpza is the foreign key) but I get the same error – LuisC Jan 25 '17 at 07:01

How to import relational data from excel file

1 Answers1