The CSV text-file format is a common choice for both import and export when performing data migrations. The Ruby FasterCSV library (gem install fastercsv) does a great job working with this format.
What if you want to generate CSV files for download within a Rails application? FasterCSV provides a way to stream output to a file as you feed individual data rows to the csv object using <<. You could generate a file on your server and then return it to the client using send_file. However, looking at the implementation of send_file, I discovered a way to have FasterCSV send its formatted output directly to the HTTP response. See the “stream_csv” method below, which nicely encapsulates all the setup code for reuse by multiple actions that export data this way.
require 'fastercsv'
class ReportController
# example action to return the contents
# of a table in CSV format
def export_users
users = User.find(:all)
stream_csv do |csv|
csv << ["first","last","id","email"]
users.each do |u|
csv << [u.first,u.last,u.id,u.email]
end
end
end
private
def stream_csv
filename = params[:action] + ".csv"
#this is required if you want this to work with IE
if request.env['HTTP_USER_AGENT'] =~ /msie/i
headers['Pragma'] = 'public'
headers["Content-type"] = "text/plain"
headers['Cache-Control'] = 'no-cache, must-revalidate, post-check=0, pre-check=0'
headers['Content-Disposition'] = "attachment; filename=\"#{filename}\""
headers['Expires'] = "0"
else
headers["Content-Type"] ||= 'text/csv'
headers["Content-Disposition"] = "attachment; filename=\"#{filename}\""
end
render :text => Proc.new { |response, output|
csv = FasterCSV.new(output, :row_sep => "\r\n")
yield csv
}
end
end
Functional Test
Here’s a simple test for the above controller. It tests to make sure that the csv request is a success and that the response matches ‘csv_data’.
<pre>
class ReportControllerTest < Test::Unit::TestCase
fixtures :users
def setup
@controller = ReportController.new
@request = ActionController::TestRequest.new
@response = ActionController::TestResponse.new
end
def test_export_users
get :export_users
assert_response :success
assert_kind_of Proc, @response.body
require 'stringio'
output = StringIO.new
assert_nothing_raised { @response.body.call(@response, output) }
assert_equal csv_data, output.string
end
private
def csv_data
"first,last,id,email\r\nQuentin,Smith,1,<a href="mailto:quentin@example.com">quentin@example.com</a>\r\nAaron,Jones,2,<a href="mailto:aaron@example.com">aaron@example.com</a>"
end
end
Alternative Cache-Control
If the above doesn’t work for you, try setting Cache-Control to private when IE:
headers['Cache-Control'] = 'private'
UTF8 Encoding Issues
Ms Excel does not properly handle UTF8-encoded CSV files. If you’re dealing with Latin-based languages only, you can convert the csv stream to ISO-8859-1 using Iconv:
require 'fastercsv'
%{color:red}require 'iconv'%
class ReportController
....
private
def stream_csv
.....
.....
# replace the render call with this:
output = StringIO.new
csv = FasterCSV.new(output, :row_sep => "\r\n")
yield csv
c = Iconv.new('ISO-8859-15','UTF-8')
render :text => c.iconv(output.string)
end
end
The CSV text-file format is a common choice for both import and export when performing data migrations. The Ruby FasterCSV library (gem install fastercsv) does a great job working with this format.
What if you want to generate CSV files for download within a Rails application? FasterCSV provides a way to stream output to a file as you feed individual data rows to the csv object using <<. You could generate a file on your server and then return it to the client using send_file. However, looking at the implementation of send_file, I discovered a way to have FasterCSV send its formatted output directly to the HTTP response. See the “stream_csv” method below, which nicely encapsulates all the setup code for reuse by multiple actions that export data this way.
require 'fastercsv'
class ReportController
# example action to return the contents
# of a table in CSV format
def export_users
users = User.find(:all)
stream_csv do |csv|
csv << ["first","last","id","email"]
users.each do |u|
csv << [u.first,u.last,u.id,u.email]
end
end
end
private
def stream_csv
filename = params[:action] + ".csv"
#this is required if you want this to work with IE
if request.env['HTTP_USER_AGENT'] =~ /msie/i
headers['Pragma'] = 'public'
headers["Content-type"] = "text/plain"
headers['Cache-Control'] = 'no-cache, must-revalidate, post-check=0, pre-check=0'
headers['Content-Disposition'] = "attachment; filename=\"#{filename}\""
headers['Expires'] = "0"
else
headers["Content-Type"] ||= 'text/csv'
headers["Content-Disposition"] = "attachment; filename=\"#{filename}\""
end
render :text => Proc.new { |response, output|
csv = FasterCSV.new(output, :row_sep => "\r\n")
yield csv
}
end
end
Functional Test
Here’s a simple test for the above controller. It tests to make sure that the csv request is a success and that the response matches ‘csv_data’.
<pre>
class ReportControllerTest < Test::Unit::TestCase
fixtures :users
def setup
@controller = ReportController.new
@request = ActionController::TestRequest.new
@response = ActionController::TestResponse.new
end
def test_export_users
get :export_users
assert_response :success
assert_kind_of Proc, @response.body
require 'stringio'
output = StringIO.new
assert_nothing_raised { @response.body.call(@response, output) }
assert_equal csv_data, output.string
end
private
def csv_data
"first,last,id,email\r\nQuentin,Smith,1,<a href="mailto:quentin@example.com">quentin@example.com</a>\r\nAaron,Jones,2,<a href="mailto:aaron@example.com">aaron@example.com</a>"
end
end
Alternative Cache-Control
If the above doesn’t work for you, try setting Cache-Control to private when IE:
headers['Cache-Control'] = 'private'
UTF8 Encoding Issues
Ms Excel does not properly handle UTF8-encoded CSV files. If you’re dealing with Latin-based languages only, you can convert the csv stream to ISO-8859-1 using Iconv:
require 'fastercsv'
%{color:red}require 'iconv'%
class ReportController
....
private
def stream_csv
.....
.....
# replace the render call with this:
output = StringIO.new
csv = FasterCSV.new(output, :row_sep => "\r\n")
yield csv
c = Iconv.new('ISO-8859-15','UTF-8')
render :text => c.iconv(output.string)
end
end