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:
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
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