How to convert array of ActiveRecord models to CSV?

I got an array of ActiveRecord models that I wish to convert to a CSV. I tried researching gems like FasterCSV, but they just seem to work with strings and arrays, not ActiveRecord models.

In short, I want to convert:

user1 = User.first
user2 = User.last
a = [user1, user2]

TO:

   id,username,bio,email
    1,user1,user 1 bio,user1 email
    1,user2,user 2 bio,user2 email

Is there an easy Rails way to do this?

Solution 1:

The following will write the attributes of all users to a file:

CSV.open("path/to/file.csv", "wb") do |csv|
  csv << User.attribute_names
  User.find_each do |user|
    csv << user.attributes.values
  end
end

Similarly you could create a CSV string:

csv_string = CSV.generate do |csv|
  csv << User.attribute_names
  User.find_each do |user|
    csv << user.attributes.values
  end
end

Solution 2:

@rudolph9's answer is really awesome. I just want to leave a note for people who need to do this task periodically: making it as a rake task would be a good idea!

lib/tasks/users_to_csv.rake

# usage:
# rake csv:users:all => export all users to ./user.csv
# rake csv:users:range start=1757 offset=1957 => export users whose id are between 1757 and 1957
# rake csv:users:last number=3   => export last 3 users
require 'csv' # according to your settings, you may or may not need this line

namespace :csv do
  namespace :users do
    desc "export all users to a csv file"
    task :all => :environment do
      export_to_csv User.all
    end

    desc "export users whose id are within a range to a csv file"
    task :range => :environment do |task, args|
      export_to_csv User.where("id >= ? and id < ?", ENV['start'], ENV['offset'])
    end

    desc "export last #number users to a csv file"
    task :last => :environment do |task, arg|
      export_to_csv User.last(ENV['number'].to_i)
    end

    def export_to_csv(users)
      CSV.open("./user.csv", "wb") do |csv|
        csv << User.attribute_names
        users.each do |user|
          csv << user.attributes.values
        end
      end
    end
  end
end

Solution 3:

This might be off the original question but solve the problem. If you plan to make all or some of your Active Record models be able to convert to csv, you can use ActiveRecord concern. An example is shown below

module Csvable
  extend ActiveSupport::Concern 

  class_methods do
    def to_csv(*attributes)
      CSV.generate(headers: true) do |csv| 
        csv << attributes 

        all.each do |record| 
          csv << attributes.map { |attr| record.send(attr) }
        end 
      end
    end
  end
end

The attribute provided will be used as the header for the CSV and it is expected that this attribute corresponds to methods name in the included class. Then you can include it in any ActiveRecord class of your choice, in this case, the User class

class User 
  include Csvable 

end

Usage

User.where(id: [1, 2, 4]).to_csv(:id, :name, :age)

Note: This only works for ActiveRecord relation and not for arrays

Solution 4:

If you need something quick and dirty, not so much for production as just grabbing some data for a non-technical user, you could paste this in console:

require 'csv'
class ActiveRecord::Relation
  def to_csv
    ::CSV.generate do |csv|
      csv << self.model.attribute_names
      self.each do |record|
        csv << record.attributes.values
      end
    end
  end
end

Then do: User.select(:id,:name).all.to_csv

If you were going to production, I'd probably turn this into a decorator around ActiveRecord::Relation and more precisely ensuring that the order of your fields/attributes.

Solution 5:

with julia_builder you can configure a csv export pretty easily.

class UserCsv < Julia::Builder
  # specify column's header and value
  column 'Birthday', :dob
  # header equals 'Birthday' and the value will be on `user.dbo`

  # when header and value are the same, no need to duplicate it.
  column :name
  # header equals 'name', value will be `user.name`

  # when you need to do some extra work on the value you can pass a proc.
  column 'Full name', -> { "#{ name.capitalize } #{ last_name.capitalize }" }

  # or you can pass a block
  column 'Type' do |user|
    user.class.name
  end
end

and then

users = User.all
UserCsv.build(users)