Blank lines in CSV from mySQL export fields are causing error during ruby CSV parsing

Question

I have some gnarly mySQL CSV exports from an old system that I'm parsing and loading into a new Ruby on Rails application.

Below is an example:

"1","1","When a ticket is marked as Resolved, revert the assigned_to to the one who started it",,"7","1","1.00","0.00","2",NULL,NULL,"1","2009-06-04 16:40:37","2009-06-04 16:40:37",NULL,"0000-00-00 00:00:00";"2","2","Email notifications when ticket is assigned to someone",,"1","1","1.00","0.00","1",NULL,NULL,"1","2009-06-04 16:41:21","2009-06-04 16:41:21",NULL,"0000-00-00 00:00:00";"3","1","When a ticket is marked as Resolved, revert the assigned_to to the one who started it - and notify",,"7","1","1.00","0.00","2",NULL,NULL,"1","2009-06-09 18:10:47","2009-06-09 18:10:47",NULL,"0000-00-00 00:00:00";"4","3","Change Password Capability","Fix the forgot password capability (and for bonus points, add capability for user to change once logged in.","7","1","0.00","0.00","1",NULL,NULL,"9","2009-06-09 18:13:45","2009-06-09 18:13:45",NULL,"0000-00-00 00:00:00";"5","4","Manager View","Don't need listed:
  Milestone
  Status

Do need listed:
  Assigned To
  Position (since we're not assigning case numbers)","7","1","0.00","0.00","1",NULL,NULL,NULL,"2009-06-09 18:16:32","2009-06-09 18:16:32",NULL,"0000-00-00 00:00:00";"6","5","TICKETS: Remove Position / Assign ID","Don't really need to assign a position, instead would be better to automatically assign a ticket number and be able to sort on that.

Also, when you don't assign a position to a ticket, it breaks the system (or at least it doesn't show up and causes an error in the Manager View)","7","1","0.00","0.00","1",NULL,NULL,"9","2009-06-09 18:19:10","2009-06-09 18:19:10",NULL,"0000-00-00 00:00:00";"7","6","Manager View","Don't need listed:
- Milestone
- Status

Do need listed:
- Case ID (preferred)
- Position (until case id implemented)","7","1","0.00","0.00","1",NULL,NULL,"9","2009-06-09 18:24:07","2009-06-09 18:24:07",NULL,"0000-00-00 00:00:00";"8","5","TICKETS: Remove Position / Assign ID","Don't really need to assign a position, instead would be better to automatically assign a ticket number and be able to sort on that.

Also, when you don't assign a position to a ticket, it breaks the system (or at least it doesn't show up and causes an error in the Manager View)","7","1","0.00","0.00","1",NULL,NULL,NULL,"2009-06-09 18:35:00","2009-06-09 18:35:00",NULL,"0000-00-00 00:00:00";"9","7","Ability to \"assign\" projects to users","Some way, even manual in the database, to indicate which projects a user may access","7","1","0.00","0.00","1",NULL,NULL,"9","2009-06-09 18:45:16","2009-06-09 18:45:16",NULL,"0000-00-00 00:00:00";

The fields are enclosed by double quotes, terminated by a comma, and the rows are terminated by a semicolon. As you can hopefully see, on particular field has hard returns(?) within the field. Thats how they show up in the CSV file, not as new line characters.

My ruby test code to parse the CSV:

  csv_file_path1 = 'data/file.csv'

  CSV.foreach( csv_file_path2, { :row_sep => ";" } ) do |row|
    puts row[1]
  end

When I run this via rake task, I get output:

 1
 2   
 3
 4
 5
 6
 7
 8   
 rake aborted!
 Missing or stray quote in line 9
 ...

Why can't it parse through the row with hard returns in the field? Thanks.

EDIT: Updated to show more of the CSV.

Answer 1

The problem in this case is the double quote escaping, not the newlines. You have a field that contains the string \"assign\" which should instead be escaped as ""assign"". Making that change causes the following to run properly:

require 'csv'
CSV.parse(DATA, :row_sep => ";") do |row|
  puts row
end

__END__
"1","1","When a ticket is marked as Resolved, revert the assigned_to to the one who started it",,"7","1","1.00","0.00","2",NULL,NULL,"1","2009-06-04 16:40:37","2009-06-04 16:40:37",NULL,"0000-00-00 00:00:00";"2","2","Email notifications when ticket is assigned to someone",,"1","1","1.00","0.00","1",NULL,NULL,"1","2009-06-04 16:41:21","2009-06-04 16:41:21",NULL,"0000-00-00 00:00:00";"3","1","When a ticket is marked as Resolved, revert the assigned_to to the one who started it - and notify",,"7","1","1.00","0.00","2",NULL,NULL,"1","2009-06-09 18:10:47","2009-06-09 18:10:47",NULL,"0000-00-00 00:00:00";"4","3","Change Password Capability","Fix the forgot password capability (and for bonus points, add capability for user to change once logged in.","7","1","0.00","0.00","1",NULL,NULL,"9","2009-06-09 18:13:45","2009-06-09 18:13:45",NULL,"0000-00-00 00:00:00";"5","4","Manager View","Don't need listed:
  Milestone
  Status

Do need listed:
  Assigned To
  Position (since we're not assigning case numbers)","7","1","0.00","0.00","1",NULL,NULL,NULL,"2009-06-09 18:16:32","2009-06-09 18:16:32",NULL,"0000-00-00 00:00:00";"6","5","TICKETS: Remove Position / Assign ID","Don't really need to assign a position, instead would be better to automatically assign a ticket number and be able to sort on that.

Also, when you don't assign a position to a ticket, it breaks the system (or at least it doesn't show up and causes an error in the Manager View)","7","1","0.00","0.00","1",NULL,NULL,"9","2009-06-09 18:19:10","2009-06-09 18:19:10",NULL,"0000-00-00 00:00:00";"7","6","Manager View","Don't need listed:
- Milestone
- Status

Do need listed:
- Case ID (preferred)
- Position (until case id implemented)","7","1","0.00","0.00","1",NULL,NULL,"9","2009-06-09 18:24:07","2009-06-09 18:24:07",NULL,"0000-00-00 00:00:00";"8","5","TICKETS: Remove Position / Assign ID","Don't really need to assign a position, instead would be better to automatically assign a ticket number and be able to sort on that.

Also, when you don't assign a position to a ticket, it breaks the system (or at least it doesn't show up and causes an error in the Manager View)","7","1","0.00","0.00","1",NULL,NULL,NULL,"2009-06-09 18:35:00","2009-06-09 18:35:00",NULL,"0000-00-00 00:00:00";"9","7","Ability to ""assign"" projects to users","Some way, even manual in the database, to indicate which projects a user may access","7","1","0.00","0.00","1",NULL,NULL,"9","2009-06-09 18:45:16","2009-06-09 18:45:16",NULL,"0000-00-00 00:00:00";