Forum: Ruby on Rails Looking For A Better Way To Do This

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
John K. (Guest)
on 2007-05-28 23:39
(Received via mailing list)
For efficiency sake after a bunch of experimentation I have come to
the conclusion, for me, that the best way to export 100K plus rows
into csv format is by breaking into mysql and doing an OUTFILE.  It is
easy except for one thing... managing quotes and double quotes.

Here is an example of what I need to do:

system("echo 'SELECT region_name, area_name,
CONCAT(\'=\\\"\',district_number,\'\\\"\') FROM jobs LIMIT 1000 INTO
OUTFILE \"/temp/#{file_name}\";' | mysql
-u#{ActiveRecord::Base.configurations['development']['username']}
#{ActiveRecord::Base.configurations['development']['database']}")

The problem is the CONCATs and stuff like that I am going to have to
do to format the export properly:
CONCAT(\'=\\\"\',district_number,\'\\\"\')

The CONCAT in this case does not even work.  If I go into the mysql
command prompt the following though does work:
SELECT region_name, area_name, CONCAT('=\"',district_number,'\"') FROM
jobs  LIMIT 1000 INTO OUTFILE "/temp/test.xls";

I can't manage all the escaping I might need or not need.

Their must be a better way to do this?  Can I put the mysql statement
into a file or something so I can bypass all the quote problems?

Please help? :-)

Your Friend,

John


--
John K.
removed_email_address@domain.invalid

http://www.kopanas.com
http://www.cusec.net
http://www.soen.info
This topic is locked and can not be replied to.