|
Peter Marklund's Home |
Rails Recipe: CSV Export
A common requirement from customers is the ability to export tabular data to a CSV file that can be imported into Excel. Ruby on Rails uses the standard Ruby CSV library to import test fixtures that are in CSV format. However, there is a runner up CSV library called FasterCSV which as the name suggests is faster and also has a much cleaner API. I was hesitant to use FasterCSV in production since it is still in Beta but after finding the discussion about including FasterCSV in the Ruby standard library I decided to give it a try.
The first step was to download the latest FasterCSV tgz file and extract into RAILS_HOME/vendor/fastercsv. I then added the following line to config/environment.rb:
require 'fastercsv/lib/faster_csv'
The action that exports the CSV data looks roughly like the following:
def csv
@list = @group.lists.find(params[:id])
csv_string = FasterCSV.generate do |csv|
csv << ["Type", "Name", ... more attribute names here ... ]
@list.records.each do |record|
csv << [record['type'],
record['name'],
... more values here ...]
end
end
filename = @list.name.downcase.gsub(/[^0-9a-z]/, "_") + ".csv"
send_data(csv_string,
:type => 'text/csv; charset=utf-8; header=present',
:filename => filename)
end
In the corresponding controller test I check that the CSV response can be parsed with the other CSV parser, that there is a header row with expected attributes, that the number of data rows is correct, and finally that one of the rows has the correct values:
def test_csv
get_success :csv, :id => lists(:peterlist).id
reader = CSV::Reader.create(@response.body)
header = reader.shift
['Type', 'Name', ... more attributes here ...].each do |attribute|
assert header.include?(attribute)
end
rows = []
reader.each do |row|
rows << row
end
assert_equal rows.size, lists(:peterlist).size
item = lists(:peterlist).items.first
item_row = rows.select { |row| row[1] == item.contact.name }.first
assert_equal item_row[0], 'Contact'
assert_equal item_row[1], item.name
... assertions for the other attribute values here ...
end
To see what CSV export from a controller looks like with the Ruby CSV library - check out this blog post.
Comments
ben said about 1 year ago:
You need to add some header garbage in for IE for this type of thing to work… if you’re interested…
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
Peter Marklund said about 1 year ago:
Thanks Ben, didn’t know about the IE headers…
Flornet said about 1 year ago:
Yep, fast to setup, and working… just nice!
I just made a tweak, I added an option on the FasterCSV.generate function :
csv_string = FasterCSV.generate(:col_sep => ”;”) do |csv|
Microsoft Excel seems to understand it better than ”,”.
Thanks a lot
nephish said about 1 year ago:
Hey there,
thanks for this a lot !
i know this is months after it was written, but just only needed it now.
thanks again
Marston A. said about 1 year ago:
Great post, I'm just needing this as well.
Question:
Can I just paste that IE header stuff within the send_file method? Where exactly does it go?
C. Bedard said 9 months ago:
For those who have run into MS Excel's UTF8 encoding problems with CSV, you can do the following, if you will deal only with latin-based languages:
<code>
...
require 'iconv'
...
c = Iconv.new('ISO-8859-15','UTF-8')
send_data(c.iconv(csv_string),
:type => 'text/csv; charset=iso8859-1; header=present',
:filename => filename)
</code>
Note that it will not work if the UTF8 source contains characters that cannot be converted to iso8859-1
neharohan.chopra@gmail.com said 8 months ago:
Hi,
Is there a way to get the data from database and export it to CSV directly and avoid the time taken by the ruby processor to process the data.
Neha
lastobelus said 3 months ago:
Re: You need to add some header garbage in for IE for this type of thing to work…
...
headers[“Content-type”] = “text/plain”
um, actually that does NOT work (i think it used to, was considered a bug, and has been hotfixed)
text/csv works, but probably better is to use application/octet-stream
cimbalta said about 1 month ago:
Thanks to Peter and Ben. The code is helpful
Matthew Bass said about 1 year ago:
Peter, thanks for the code examples. I have a client who needed a CSV export done and Ruby’s CSV library was acting strangely on their production box versus my local system. Turns out the version of Ruby on the server was about a year older than my local version. I think that’s why the code I wrote locally didn’t work once it was deployed. Switching to FasterCSV solved my problems, though the export is still slower than I’d like. At least it’s working now!