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.
[email protected]