How to avoid importing nil object when reading spreadsheet with roo on Rails 5.2?

Question

My application manages hierarchical classifications based on lists of values (dictionnaries). At some point, I need to import the parent-child relationships from an Excel sheet, and create persisted ValuesToValues objects.

Based on Ryan Bates' RailsCast 396, I created the import model in which the main loop is:

(2..spreadsheet.last_row).map do |i|
  # Read columns indexes
  parent = header.index("Parent") +1
  level = header.index("Level") +1
  code = header.index("Code") +1

  # Skip if parent is blank
  next if spreadsheet.cell(i, parent).blank?

  # Count links
  @links_counter += 1

  parent_values_list_id = values_lists[((spreadsheet.cell(i, level).to_i) -1)]
  child_values_list_id = values_lists[spreadsheet.cell(i, level).to_i]
  parent_value_id = Value.find_by(values_list_id: parent_values_list_id, code: spreadsheet.cell(i, parent).to_s).id
  child_value_id = Value.find_by(values_list_id: child_values_list_id, code: spreadsheet.cell(i, code).to_s).id
  link_code = "#{parent_values_list_id}/#{spreadsheet.cell(i, parent)} - #{child_values_list_id}/#{spreadsheet.cell(i, code)}"
  link_name = "#{spreadsheet.cell(i, parent)} #{spreadsheet.cell(i, code)}"

  link = ValuesToValues.new( playground_id: playground_id,
                                classification_id: @classification.id,
                                parent_values_list_id: parent_values_list_id,
                                child_values_list_id: child_values_list_id,
                                parent_value_id: parent_value_id,
                                child_value_id: child_value_id,
                                code: link_code,
                                name: link_name
                              )
end

The issue is that, when encourtering a root value -without a parent value- the loop creates a nil object, which does not pass the later validation.

How can I build the loop in order to consider only rows where the Parent cell is not empty?

score 0 · Accepted Answer · answered Oct 21 '20 at 05:55

I finally decided to manage my own array of imported values instead of using the array based on the filtered sheet rows.

I added the following code around the main loop:

# Create array of links
linked_values = Array.new

# start loading links for each values list
(2..spreadsheet.last_row).map do |i|
...

and

...
  linked_values << link
end

linked_values

Then linked_values array is returned, which only contains valid links records.

How to avoid importing nil object when reading spreadsheet with roo on Rails 5.2?

1 Answers1