I''m dumping my mysql data to a csv file using the following command:
sql.execute(
"SELECT * FROM `#{name}`
INTO OUTFILE ''#{outfile}''
FIELDS ESCAPED BY ''\\\\'' TERMINATED BY '',''
ENCLOSED BY ''\"'' LINES
TERMINATED BY ''\\n''")
I''ve tried every possible combination of line terminations, enclosing,
and field escaping, and no matter what I do I always get the following
error when FasterCSV tries to parse the file:
Unclosed quoted field on line 1.
....
Sometimes it comes on line 2, sometimes line 4. I''ve gotten it to work
before using phpMyAdmin to dump the file, but this is really driving me
mad. I''m on Mac Leopard with Rails 2.1.
A huge thanks in advance to anyone who can help with this!
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
So I dumped the table using phpMyAdmin, using "CSV for Excel" and "Windows" selected, and then ran FasterCSV over it and it worked.. Then I compared my dumped file with the phpMyAdmin one and found they were *exactly* the same as far as I could tell.. yet my file didn''t work. Could it be line endings? I''ve tried many combinations such as \n, \r\n, \n\r... -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Ended up having to use a slower and different method, but at least it
works:
records =
Object.const_get("#{name.titlecase.singularize}").find(:all)
columns = records.first.class.columns.map {|x| x.name }
FasterCSV.open(outfile, ''a'', { :force_quotes => true,
:row_sep =>
"\r\n" }) do |csv|
for record in records
csv << columns.map {|column| record.attributes[column] }
end
end
Anyone have suggestions to speed this up?
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Optimized it further:
records = ActiveRecord::Base.connection.execute("SELECT * FROM
#{name}")
FasterCSV.open(outfile, ''a'', { :force_quotes => true,
:row_sep =>
"\r\n" }) do |csv|
records.each do |row|
csv << row
end
end
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
KathysKode-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
2008-Jun-02 14:27 UTC
Re: FasterCSV will not parse my data
Nathan, Would you be so kind as to explain more about these steps? Firstly, you seem to be opening a connection to an MySQL table...why the select statement and not the tablename? Secondly, what is the ''a'' about in the FasterCSV line? I will soon be tasked with this challenge and would be most grateful to hear more. Thank you, Kathleen KathysKode-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org On Jun 1, 5:22 pm, "Nathan W." <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Optimized it further: > > records = ActiveRecord::Base.connection.execute("SELECT * FROM > #{name}") > FasterCSV.open(outfile, ''a'', { :force_quotes => true, :row_sep => > "\r\n" }) do |csv| > records.each do |row| > csv << row > end > end > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
KathysKode-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote:> Nathan, > Would you be so kind as to explain more about these steps? > Firstly, you seem to be opening a connection to an MySQL table...why > the select statement and not the tablename? > Secondly, what is the ''a'' about in the FasterCSV line? > I will soon be tasked with this challenge and would be most grateful > to hear more. > Thank you, > Kathleen > KathysKode-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org > > On Jun 1, 5:22�pm, "Nathan W." <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>Well, for me I am moving over multiple tables, so I needed the table name to be dynamic. Furthermore, I also need it to be quick as I have 20,000+ rows in one table, as well as a large amount in others, thus why I did the SQL select and didn''t use the Object.const_get() (which returns the object from ActiveRecord). The ''a'' stands for append and it simply means if there is no file there already, it will create it. If there is one, it will append to it. I wanted this as I delete the file before I run that code. If you need anything else just ask. My only minor problem now is that the data is being encoded weirdly, apostrophe''s are coming out as odd characters so if anyone knows why I''d love to hear. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---