I''m in the process to write a fairly generic facility for exporting to
CSV. A commented excerpt of the code is below.
There are a few things I have noticed and a few open questions. The
result set I''m exporting has 4000 rows.
* The database query is practically instantaneous when run directly from
psql (commandline interface to PgSQL).
* The find method is slow, it takes 10 to 15s. I don''t know exactly
where the time is spend, probably in instantiating the objects.
* Appending to the CSV writer is slow. Even considering the time it
takes to access attributes of the objects involved, most time vanishes
inside of the CSV library.
* With some browsers (Konqueror) each export request actually results in
two exports. The first time, the browser only peeks for the
content-type, shows a "Save, Open, Cancel" dialog, and then retrieves
the data again.
* Surprisingly, it''s faster to have CSV append to a string than to an
array that is latter joined.
* My most pertinent question is, no doubt, how to speed this process up.
* Is there a sensible way to determine the user''s OS and convert data
to
the native charset and end-of-line chars?
Michael
def export_csv(options = {})
{ :charset => charset,
:field_separator => field_separator,
:record_separator => record_separator,
:filename => ''export.csv''
}.update(options)
# A ListView (bad name, it''s going to be improved) captures
# meta data for list views and exports.
# Among them is a path array, e.g. ["lastname"],
["company","name"]
# From the meta data, ListView derives pieces for the query.
column_specs = list_view_for(:action => ''list'')
order = column_specs.build_order_clause
conditions = column_specs.build_condition_clause
includes = column_specs.includes
objects = column_specs.klass.find(
:all,
:order => order,
:conditions => conditions,
:include => includes)
data = ''''
CSV::Writer.generate(data,
options[:field_separator],
options[:record_separator]) do |csv|
csv << csv_head_row(column_specs)
objects.each do |object|
csv << csv_row(column_specs, object)
end
end
end
def csv_head_row(column_specs)
column_specs.map do |column_spec|
column_spec[:title] || title_from_path(column_spec[:path])
end
end
def csv_row(column_specs, object)
column_specs.map do |column_spec|
path = column_spec[:path]
if path.length > 1
# get_at_path is defined in a mixin to AR::Base
# it allows to access the value of an attribute along a path
object.get_at_path(column_spec[:path], :default => nil)
else
# optimize the most common case
object.send(path[0])
end
end
end
--
Michael Schuerig The Fifth Rider of the Apocalypse
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org is a programmer.
http://www.schuerig.de/michael/