0

I'm getting errors when I import an excel file when a cell is blank, I need to be able to save " "

 select * from clients where doc_nat = '1234'
 Insert into clients (doc_nat,name_nat,doc_jur,name_jur) 
 values ('1234','cr7','','')

 select * from clients where doc_jur = '1234'
 Insert into clients (doc_nat,name_nat,doc_jur,name_jur) 
 values ('','','5678','Messi')

Excel file:

|doc_nat|  |name_nat|  |doc_jur|  |name_jur|
 1234          Cr7         
                         56789      Messi

Excel description:

Excel description

Model Client.rb

COLUMNS_TO_STRING = ["doc_nat","name_nat", "doc_jur","name_jur" ]

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]
    row = clean_for row, COLUMNS_TO_STRING

    if row["doc_nat"].present?
      record = find_or_create_by(row["doc_nat"]) || new
    else
      record = find_by_doc_jur(row["doc_jur"]) || new
    end    

    record.attributes = row.to_hash.slice(*row.to_hash.keys)
    record.save!
  end
end

def self.clean_for row_as_hash, string_columns_array
  row_as_hash.each do |key, value|
    if string_columns_array.include?key
      row_as_hash[key] = value.to_i.to_s
    end
  end
end

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

I get this message:

element size differs (2 should be 4)

My logs:

Processing ClientController#import (for 127.0.0.1 at 2016-05-13 09:39:14) [POST]
Parameters: {"authenticity_token"=>"test, "file"=>#<File:/tmp/RackMultipart20160513-4217-6ijga7-0>, "commit"=>"Import"}

IndexError (element size differs (3 should be 4)):
 app/models/client.rb:38:in `transpose'
 app/models/client.rb:38:in `import'
 app/models/client.rb:37:in `each'
 app/models/client.rb:37:in `import'

The solution was adding a character in blank spaces:

 #excel file
 |doc_nat|  |name_nat|  |doc_jur|  |name_jur|
    1234          Cr7        .         . 
       .           .       56789      Messi  

 #sql log description
 select * from clients where doc_nat = '1234'
 Insert into clients (doc_nat,name_nat,doc_jur,name_jur) 
 values ('1234','cr7','0','0')

 select * from clients where doc_jur = '1234'
 Insert into clients (doc_nat,name_nat,doc_jur,name_jur) 
 values ('0','0','5678','Messi')

I want to save like this or maybe save blank characters:

    if row["doc_nat"].present?
      record = find_or_create_by(row["doc_nat"]) || new
      record = record.doc_jur = ""
      record = record.num_jur = ""
    else
      record = find_by_doc_jur(row["doc_jur"]) || new
      record = record.doc_nat = ""
      record = record.num_nat = ""
    end

And I also tried:

    if row["doc_nat"].present?
      record = find_or_create_by(row["doc_nat"]) || new
      record = record.doc_jur = row["doc_jur"]
      record = record.num_jur = row["num_jur"]
    else
      record = find_by_doc_jur(row["doc_jur"]) || new
      record = record.doc_nat = row["doc_nat"]
      record = record.num_nat = row["num_nat"]
    end
Carlos Morales
  • 1,137
  • 3
  • 15
  • 38
  • Would you mind to `puts row.inspect` immediately after `row = Hash[[header, spreadsheet.row(i)].transpose]` and post the output here? – Aleksei Matiushkin May 13 '16 at 14:59
  • row = Hash[[header, spreadsheet.row(i)].transpose] puts row.inspect i get the same log that I posted: Processing ClientController#import (for 127.0.0.1 at 2016-05-13 09:39:14) [POST] Parameters: {"authenticity_token"=>"test, "file"=>#, "commit"=>"Import"} IndexError (element size differs (3 should be 4)): app/models/client.rb:38:in `transpose' app/models/client.rb:38:in `import' app/models/client.rb:37:in `each' app/models/client.rb:37:in `import' – Carlos Morales May 13 '16 at 15:05
  • Try to use `row = header.zip(spreadsheet.row(i)).to_h` instead of `row = Hash[[header, spreadsheet.row(i)].transpose]`. – Aleksei Matiushkin May 13 '16 at 15:08
  • I have this error undefined method `to_h' for [ ["doc_nat",1234],["name_nat","Cr7"], ["doc_jur", nil], ["name_jur", nil] ]:Array – Carlos Morales May 13 '16 at 16:23
  • Then you probably should have specified explicitly that you’re using `ruby-1.9` in tags. Refer to the answer provided by @maximus. – Aleksei Matiushkin May 13 '16 at 16:26
  • I'm using ruby 1.8 and rails 2.3.5 – Carlos Morales May 13 '16 at 16:30

1 Answers1

1

Instead of transpose in your import method for converting excel row to hash with header values as keys,

row = Hash[[header, spreadsheet.row(i)].transpose]

do,

row_data =  hash[header.zip spreadsheet.row(i)]

It will take care of missing values

 2.1.0:005> Hash[['a','b'].zip [1]]
 => {"a"=>1, "b"=>nil}

where as 

2.1.0 :007 > Hash[[['a','b'],[1]].transpose]
=> IndexError: element size differs (1 should be 2)
from (irb):7:in `transpose'
from (irb):7
maximus ツ
  • 7,949
  • 3
  • 25
  • 54