Missing or stray quote in line 1 (CSV::MalformedCSVError)

Question

I'm having issues importing this CSV file in ruby/rails

The error message I'm getting is this:

Missing or stray quote in line 1 (CSV::MalformedCSVError)

But I'm not sure what's happening because my CSV looks perfectly fine. Here's sample data below:

"lesley_grades","lesley_id","last","first","active","site","cohort","section","sections_title","faculty","completed_term_cred","term","sec_start_date","sec_end_date","grade","stc_cred","active_program","most_recent_program","intent_filed","stc_term_gpa","sta_cum_gpa","start_term","prog_status","last_change_date"
,1234456,John,Doe,TRUE,"Baltimore, MD",0002012,14/FA_ERLIT_6999_U15AA,Directed Independent Study,"Jane Hicks , Jill Saunders",2,14/FA,9/3/14,12/17/14,B-,2,EME.2270.TCBAL.01,EME.2270.TCBAL.01, ,3.3,3.148,12/SU,A,9/2/14
,1234455,John,Doe,TRUE,"Baltimore, MD",0002012,14/FA_ERSPD_6999_U15AG,Directed Independent Study,"Jane Hicks , Jill Saunders",3,14/FA,9/3/14,12/17/14,A-,3,EME.2270.TCBAL.01,EME.2270.TCBAL.01, ,3.3,3.148,12/SU,A,9/2/14

To give context, effectively the csv looks like this, with the lesley_grades as the first column. The over CSV script file will look for the first column and check that active an Active Record object, then it stores it the db with that exact same model name, assuming all migrations are pre-set.

lesley_grades   lesley_id   last   first    active  
                 1234556    Doe    John     TRUE    
                 1123445    Doe    John     TRUE

Here's part of the code that's causing me issues

def import!(csv)
 csv_reader = CSV.parse(csv)
 ActiveRecord::Base.transaction do
  csv_reader.each do |row|
    set_record_class_and_columns(row) if header_row?(row)

    if columns_mapping_defined? && record_class_defined? && record_row?(row)
      import_row(row)
    end
  end
  if imports_failed?
    puts 'Aborting importing and rolling back...'
    show_errors
    raise ActiveRecord::Rollback
  end
end

end

It can't get passed this line csv_reader = CSV.parse(csv)

before I put the quotes in the headers I was getting this error

Unquoted fields do not allow \r or \n (line 1). (CSV::MalformedCSVError)

UPDATE

The CSV gets started from the command line like this:

rails runner scripts/import_csv.rb < lesley_grades.csv

which then gets initialized here

CSVImporter.new.import!($stdin)

But as @smathy suggests I changed the method to CSV.parse(csv.gsub /\r/, '')

but now the def import! method to take in a gsub block produces this error

in `import!': undefined method `gsub' for #<IO:<STDIN>> (NoMethodError)

Not sure how to make CSV an object?

Any suggestions or refactoring to make this work? Thanks all

Solution 1

Your CSV data came from Windows and has CRLF (ie. "\r\n") line endings instead of "\n", you'll need to strip out the "\r"s before trying to parse it:

CSV.parse(csv.gsub /\r/, '')

Update

After additional info from OP:

CSV.parse(csv.read.gsub /\r/, '')

Solution 2

This error can also be caused by double quotes that do not come at the beginning of a field and are not escaped with two double quotes.

The error would occur in third field in the following example:

"Issue", "posted by Gary", "He said "I'm having issues importing" ","12345"

The double quotes in "I'm having issues" will be caught by the regular-expression (/[^"]"[^"]/) found in stray_quote, a variable in the csv.rb file used to raise the MalformedCSVError error on line 1863.

To get around this, you'll need to escape the double quotes with another double quote like so:

"He said ""I'm having issues importing"" "

Hope this helps.

Solution 3

You may also encounter this issue if the CSV column separation character is not correctly set.

By default, Ruby assumes ,. This is true for most open source software, like OpenOffice. Microsoft Excel, in the contrary, uses ; when exporting to CSV.

Therefore, use the col_sep option like so:

CSV.parse(csv, col_sep: ';')

Comments

[gary1410]

So, it apparently gets passed the previous error but now csv.gsub can't work because csv isn't an object? It's coming from the command line. Check above updates. Thanks so far with this...it too me 2 hours to even get passed that previous error.

[gary1410]

Oh, wow great! Thanks! I can't believe I missed that. And of course, if you're implying to give you the green check - then of course!

[smathy]

I wasn't implying that, although it's a good call now :) Take a read of What should I do when someone answers my question? - summary: upvoting is the way to say thanks.

[tommyalvarez]

This reads the whole file contents into memory, in large files will degrade performance. Is there no other way??

[smathy]

Recent versions of Ruby don't seem to stumble over \r\n anymore, did you try just using CSV.foreach or friends?