Download a large amount of data in CSV from Rails
I've just had to re-write an export script because the application now needed to be able to download 14000 records in CSV format.
Originally I was reading all the records (with multiple joins) and then creating a csv string and then writing it to the response using send_data.
The original code
This is a simplified version of my code
accounts = Account.find(:all)
csv_string = FasterCSV.generate do |csv|
accounts.each do |account|
csv << [account.id, account.name]
end
end
send_data csv_string, :filename => 'accounts.csv',
:type => 'text/csv',
:disposition => 'attachment'
My first thought was to stream the data using
send_file_headers! :filename => 'accounts.csv',
:type => 'text/csv',
:disposition => 'attachment'
render :text => Proc.new {|response, output|
...
}
The problem with this is that send_file_headers! requires that the :length option be set. I also was running into the problem of loading 14000 records into memory before iterating over them.
For the memory problem, I considered using the new Model.find_in_batches but I needed to join tables and use some complicated conditions.
The solution
I finally settled on chunked reading of the data from the database and a more explicit use of render :text => Proc...
require 'fastercsv'
def download
filename = 'accounts.csv'
headers.merge!(
'Content-Type' => 'text/csv',
'Content-Disposition' => "attachment; filename=\"#{filename}\"",
'Content-Transfer-Encoding' => 'binary'
)
@performed_render = false
render :status => 200, :text => Proc.new { |response, output|
headings = ["ID", "Name"]
output.write FasterCSV.generate_line(headings)
last_account_id = 0
while last_account_id do
accounts = Account.find(:all,
:conditions => ["accounts.id > ?", last_account_id],
:order => 'accounts.id',
:limit => 1000)
last_account_id = accounts.size > 0 ? accounts[-1].id : nil
accounts.each { |account|
data = [account.id, account.name]
output.write FasterCSV.generate_line(data)
}
end
}
end
I hope this useful for you, if you have any questions, please ask in the comments.
Comments
Have your say