Mapping columns to attributes

The main way in which an importer processes the contents of a spreadsheet is by mapping columns in it to attributes in the data model. Here we take a closer look at how to declare this mapping in an importer.

Importers are subclasses of ActiveImporter::Base. This base class provides a DSL for declaring several features of the importing process. One of the most important methods in this DSL is the column method. It receives a column header name, and an attribute name, and this establishes a mapping between the values in that spreadsheet column, and the corresponding attribute in the data model.

class EmployeeImporter < ActiveImporter::Base
  imports Employee

  column 'First name', :first_name
  column 'Last name', :last_name
end

As you can see in the example above, this importer expects to receive a spreadsheet with columns named 'First name' and 'Last name'. It will import all rows in such a spreadsheet, into the Employee data model, storing the values in the column 'First name' into the :first_name attribute of the model, and the values in the column 'Last name' into the :last_name attribute, creating one new model for each row processed successfully.

Processing the incoming value

We often need to process incoming data that has to be pre-processed before storing it in the corresponding attribute. Perhaps we need to trim excess whitespace off a string value, parse a string value converting it into some internal representation, etc. This can be achieved by providing a block to the column method, in which we process the incoming value and return the actual value to be stored in the corresponding attribute.

class EmployeeImporter < ActiveImporter::Base
  imports Employee

  column('First name', :first_name do |first_name|
    first_name.capitalize
  end

  column 'Last name', :last_name, &:capitalize
end

You can see in the example above how this works. The block of code receives as argument the incoming value (the value coming from the spreadsheet in the corresponding column). Inside the block we process this value and return the actual value we want to store in our database model. In this case we're instructing the importer to capitalize each name (in case you didn't notice, both attributes are being processed in the same way, we're just using in the second example a more concise Ruby way to specify the transformation block).

Establishing associations

Most commonly, we might need to use an incoming value to search a corresponding record from another data model, and establish the association with the model being created. Blocks like the ones shown above can help us do that:

class EmployeeImporter < ActiveRecord::Base
  imports Employee

  column 'Department', :department do |department_name|
    Department.find_by(name: department_name)
  end

  # ...
end

You can see how in this example, we're using the incoming value (the department name) to look-up in the Department data model for a department with that name. In this case, we're setting department to nil if no department is found, but we could take different approaches, such as raising an error if no such department is found, or even dynamically creating new departments when the name does not match any existing one. This all depends on what you want for your applications.

Accessing data from other columns

Sometimes, while processing a column's incoming value, you need to check on values from other columns. It turns out that you have full access to the entire spreadsheet row inside these blocks of code. The importer exposes it via the row method, which is a hash with the keys being the column headers, and the values being the actual values for each column in the current row.

Suppose we're processing a spreadsheet of employees, and it comes with columns for first and last name. But our data model has a single column called :full_name, which should contain the concatenation of the two other names.

class EmployeeImporter < ActiveImporter::Base
  imports Employee

  column('First name', :full_name do |first_name|
    last_name = row['Last name']
    [first_name, last_name].compact.join(' ')
  end
end

Note how we're mapping the 'First name' column to the :full_name attribute, but inside the block we're using the row hash to access the last name and include it in the actual value that will be stored in the model.