How to convert array of ActiveRecord models to CSV?
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)
Comments
-
Henley almost 2 years
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?
-
Stefan over 10 yearsYou can just use
csv << user.attributes.values
-
Henley over 10 yearsHow can I render this CSV string in my controller? In other words I want the page to return a CSV to the user.
-
rudolph9 over 10 years@HenleyChiu Checkout comma, it enables you to render CSV data the "rails-way"
respond_to { |format| format.csv { render :csv => User.limited(50) }}
-
Mike Glenn almost 7 yearsThis fails with a "NoMethodError: undefined method 'model' for ActiveRecord::Relation" if you use it on a named scope instead of .all. Anyone have a suggested fix?
-
Mario Olivio Flores almost 7 yearsHum... In what context are you calling this? Perhaps you're loading this before AR had actually loaded somehow? Since model is an attribute on AR::R, maybe it is not set when the results are empty. Are you sure there are records in your returned result? You could also try calling
all
on your named scope, but I'd be surprised if they helped. -
Mike Glenn over 6 yearsIts the same context that the .all.to_csv works in, I just change .all to a named scope I have defined in the model. it has something to do with the scope creating a proxy object, but I was unable to get much further in digging into the issue. Its not a show stopper for me but it would be nice to figure out a way to make it work, since it is a generic and very elegant solution.
-
davkutalek about 5 yearsThis is the best answer:
find_each
batches your sql queries so that you don't use massive amounts of memory -
davkutalek about 5 yearsThis will load all your users into memory. If you have a ton of records @YukiInoue has the better answer
-
rudolph9 about 5 years@davkutalek good point. I just updated may answer to make use of
#find_each
like @Yukilnoue . Thank you for pointing that out apidock.com/rails/ActiveRecord/Batches/find_each