FasterCSV will not parse my data

Rails
1

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!

2

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…

3

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?

4

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

5

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

On Jun 1, 5:22 pm, “Nathan W.” [email protected]

6

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

On Jun 1, 5:22�pm, “Nathan W.” [email protected]

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.