Thursday, June 24, 2010

Model to CSV in Rails

If you have to produce a report/CSV of data you're storing with Rails, you may eventually be required to create a CSV report of those model attributes in CSV format.

A generic CSV implementation to convert an ActiveRecord model array to CSV may work at first:

require 'fastercsv'
...
def to_csv( records )
  csv_string = FasterCSV.generate do |csv|
    did_header = false      
    records.each do |record|
      unless did_header
        csv << record.attribute_names.collect! {|attr_name| attr_name.titleize} 
        did_header = true
      end        
      values = []
      record.attribute_names.each do |attr_name|
        value = record[attr_name]
        value = value.join(', ') if value.respond_to?(:join)
        value = value.strftime('%Y-%m-%d') if value.respond_to?(:strftime)
        values.push value
      end
      csv << values      
    end
  end
  csv_string
end
...
It would be great if something like that would suffice!

But eventually, they may ask for something like the report columns being in the order they were gathered in and to include additional fields, etc. At this point, you may end up defining all of the fields (and possibly their headers) in arrays in one of your classes. Then you end up with something similar to:

require 'fastercsv'
...
def to_csv( records )
  csv_string = FasterCSV.generate do |csv|
    csv << SomeModel.csv_headers
    records.each do |record|
      csv << record.csv_data
    end
  end
  csv_string
end
...
And in the Model you may have something like:
def self.csv_headers
  [
    "Name",
    "Date",
    "Favorite Albums"
  ]
end

def csv_data
  [ 
    self.some_associated_model.try(:display_name),
    self.some_date.try(:strftime, '%m-%d-%Y'),
    self.some_array.try(:join, ', ')
  ]
end
Of course, you are probably going to be doing quite a bit of copying and pasting at this point, which is prone to error. I find it helpful to clean up the datasets using find/replace in a text editor and then compare side-by-side in Excel or a similar spreadsheet utility (sort by name on each column individually just to make sure you didn't miss any fields, etc.). If you have regexp functionality in an editor like TextMate, use it. It is a great time to hone your RegEx skills. For example, to convert some_attribute_name to "Some Attribute Name" may partially involve capitalizing each word after the other substitutions, which you can do via a find:
(\w+)
and replace with:
\u$1
then check "Regular Expression", then do Replace and Find until you have converted the set of labels. Then go back and make corrections (like changing "Us" to "US" and "By" to "by", etc.).

No comments: