Ruby unable to parse a CSV file: CSV::MalformedCSVError (Illegal quoting in line 1.)

Ubuntu 12.04 LTS

Ruby ruby 1.9.3dev (2011-09-23 revision 33323) [i686-linux]

Rails 3.2.9

Following is the content of my received CSV file:

"date/time","settlement id","type","order id","sku","description","quantity","marketplace","fulfillment","order city","order state","order postal","product sales","shipping credits","gift wrap credits","promotional rebates","sales tax collected","selling fees","fba fees","other transaction fees","other","total"
"Mar 1, 2013 12:03:54 AM PST","5481545091","Order","108-0938567-7009852","ALS2GL36LED","Solar Two Directional 36 Bright White LED Security Flood Light with Motion Activated Sensor","1","amazon.com","Amazon","Pasadena","CA","91104-1056","43.00","3.25","0","-3.25","0","-6.45","-3.75","0","0","32.80"

However when I am trying to parse the CSV file I am getting error:

1.9.3dev :016 > options = { col_sep: ",", quote_char:'"' }
=> {:col_sep=>",", :quote_char=>"\""} 

1.9.3dev :022 > CSV.foreach("/tmp/my_data.csv", options) { |row| puts row }
CSV::MalformedCSVError: Illegal quoting in line 1.
    from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1925:in `block (2 levels) in shift'
    from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1887:in `each'
    from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1887:in `block in shift'
    from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1849:in `loop'
    from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1849:in `shift'
    from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1791:in `each'
    from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1208:in `block in foreach'
    from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1354:in `open'
    from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1207:in `foreach'
    from (irb):22
    from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/bin/irb:16:in `<main>'

Then I tried simplifying the data i.e.

"name","age","email"
"jignesh","30","jignesh@example.com"

however still I am getting the same error:

      1.9.3dev :023 > CSV.foreach("/tmp/my_data.csv", options) { |row| puts row }
  CSV::MalformedCSVError: Illegal quoting in line 1.
      from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1925:in `block (2 levels) in shift'
      from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1887:in `each'
      from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1887:in `block in shift'
      from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1849:in `loop'
      from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1849:in `shift'
      from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1791:in `each'
      from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1208:in `block in foreach'
      from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1354:in `open'
      from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/lib/ruby/1.9.1/csv.rb:1207:in `foreach'
      from (irb):23
      from /home/jigneshgohel/.rvm/rubies/ruby-1.9.3-rc1/bin/irb:16:in `<main>'

Again I tried simplifying the data like this:

name,age,email
jignesh,30,jignesh@example.com

and it works.See the output below:

  1.9.3dev :024 > CSV.foreach("/tmp/my_data.csv") { |row| puts row }
  name
  age
  email
  jignesh
  30
  jignesh@example.com
   => nil 

But I will be receiving the CSV files having quoted data so removing quotes solution is not actually I am looking for.I am unable to figure out what is causing the error: CSV::MalformedCSVError: Illegal quoting in line 1. in my earlier examples.

I have verified that in the CSV there are no leading/trailing spaces by enabling "Show whitespace characters" and "Show Line Endings" in my text editor.Also I have verified the encoding using following.

  1.9.3dev :026 > File.open("/tmp/my_data.csv").read.encoding
  => #<Encoding:UTF-8> 

Note: I tried using CSV.read too but same error with that method.

Can anybody please help me getting out of the problem and make me understand where it is going wrong?

=====================

I just found following post at: http://www.ruby-forum.com/topic/448070 and tried following:

  file_data = file.read
  file_data.gsub!('"', "'")
  arr_of_arrs = CSV.parse(file_data)

  arr_of_arrs.each do |arr|
    Rails.logger.debug "=======#{arr}"
  end

and got the following output:

   =======["\xEF\xBB\xBF'date/time'", "'settlement id'", "'type'", "'order id'", "'sku'", "'description'", "'quantity'", "'marketplace'", "'fulfillment'", "'order city'", "'order state'", "'order postal'", "'product sales'", "'shipping credits'", "'gift wrap credits'", "'promotional rebates'", "'sales tax collected'", "'selling fees'", "'fba fees'", "'other transaction fees'", "'other'", "'total'"]
    =======["'Mar 1", " 2013 12:03:54 AM PST'", "'5481545091'", "'Order'", "'108-0938567-7009852'", "'ALS2GL36LED'", "'Solar Two Directional 36 Bright White LED Security Flood Light with Motion Activated Sensor'", "'1'", "'amazon.com'", "'Amazon'", "'Pasadena'", "'CA'", "'91104-1056'", "'43.00'", "'3.25'", "'0'", "'-3.25'", "'0'", "'-6.45'", "'-3.75'", "'0'", "'0'", "'32.80'"]

which messed up reading the data properly as the default col_sep used is a comma character. However I tried using quote_char option like this:

  arr_of_arrs = CSV.parse(file_data, :quote_char => "'")

but it ended up the following error:

   CSV::MalformedCSVError (Illegal quoting in line 1.):

Thanks, Jignesh

quote_chars = %w(" | ~ ^ & *)
begin
  @report = CSV.read(csv_file, headers: :first_row, quote_char: quote_chars.shift)
rescue CSV::MalformedCSVError
  quote_chars.empty? ? raise : retry 
end

it's not perfect but it works most of the time.

N.B. CSV.parse takes the same parameters as CSV.read, so either a file or data from memory can be used

Bug #2966: CSV reports illegal quoting on line, This raises a MalformedCSVError, "Illegal quoting on line #{lineno + 1}." I understand CSV file that fails parsing, sgoings (Seth Goings), 03/16/2010 01:​22 AM  But I will be receiving the CSV files having quoted data so removing quotes solution is not actually I am looking for.I am unable to figure out what is causing the error: CSV::MalformedCSVError: Illegal quoting in line 1. in my earlier examples.

Anand, thank you for the encoding suggestion. This solved the illegal quoting problem for me.

Note: If you want the iterator to skip over the header row add headers: :first_row, like so:

CSV.foreach("test.csv", encoding: "bom|utf-8", headers: :first_row)

Ruby CSV, Ruby unable to parse a CSV file: CSV::MalformedCSVError (Illegal quoting in line 1.)? .gsub(/,\s+\"/,',\"') 12, "N", 12, "Pacific/Majuro" 12,"N", 12,"Pacific/Majuro".

I just had an issue like this and discovered that CSV does not like spaces between the col-sep and the quote character. Once I removed those everything went fine. So I had:

12,  "N",  12, "Pacific/Majuro"

but once I gsubed out the spaces using

.gsub(/,\s+\"/,',\"')

resulting in

12,"N",  12,"Pacific/Majuro"

everything went fine.

Class: CSV (Ruby 2.6.1), The existing file is stored on Dropbox, so I have to use open method. The problem is that I get an error in this line: CSV.parse(open(doc.file.url), 

from this thread pass the option :quote_char => "|"

CSV.read(filename, :quote_char => "|")

User Vadym Tyemirov, Ruby unable to parse a CSV file: CSV::MalformedCSVError (Illegal quoting in line 1.) Question. Ubuntu 12.04 LTS. Ruby ruby 1.9.3dev 

I had a problem with the trademark character that was throwing this error.

The trademark character translates to \"! in UTF-8, so it was the open-ended quotation symbol that was throwing the error. So I did this:

.gsub!("\"!", "")

And then I tried creating my CSV object and it worked fine.

Why the CSV standard library is broken, broken, broken (and how to , From a file: all at once arr_of_rows = CSV.read("path/to/file.csv", **options) Core extensions for converting one line csv_string = ["CSV", "data"].to_csv # to CSV If a quote cannot be found within the limit CSV will raise a MalformedCSVError, However, this limit can cause a legitimate parse to fail and thus is set to nil , or 

Ruby unable to parse a CSV file: CSV::MalformedCSVError (Illegal quoting in line 1.) stackoverflow.com · 23 · Is there a case statement in Haml-Coffee?

Ruby unable to parse a CSV file: CSV::MalformedCSVError (Illegal quoting in line 1.) - Stack Overflow. Ubuntu 12.04 LTS Ruby ruby 1.9.3dev (2011-09-23 

how to fix it)” [1]. Why not let's make ruby the best it can be for data wrangling - starting with the humbl… starting with the humble comma-separated values (​CSV) format - the #<CSV::MalformedCSVError: Illegal quoting in line 1.> begin After using the csv std library I'm getting all these parse errors

Comments
  • Used the sample data you've provided and the parsing works fine. Not getting any CSV::MalformedCSVError: Illegal quoting in line 1 error.
  • In my edited section the output contains following: "\xEF\xBB\xBF'date/time'".Is it creating some problem? I don't know what does it represent.Thanks.
  • The Unicode characters at the start of the file are BOM (Byte Order Mark). You can try sub!(/^\xEF\xBB\xBF/, '') or CSV.foreach("test.csv", encoding: "bom|utf-8")
  • Thanks Anand I shall try using your encoding suggested solution.Meanwhile with my temporary solution in Edit section when used header_converters like: arr_of_arrs = CSV.parse(file_data, { col_sep: ";", headers: true, header_converters: [ :symbol ] }) I got following error: Encoding::UndefinedConversionError ("\xEF" from ASCII-8BIT to UTF-8).That one mentions ASCCII-8BIT as encoding.How does that encoding matters, how those BOM characters got in there? Such errors should be clearly shown in the exception thrown by library instead of finding them by chance in to_s output.
  • The following link joelonsoftware.com/articles/Unicode.html, will help in understanding how encoding matters. As for how those BOM characters got in there, you'll need to check the origin of the CSV file received and how it was saved.
  • | resolved the issue in this.
  • Thanks! encoding: "bom|utf-8" is what solved my problem.
  • For those getting ArgumentError: unknown encoding name - bom|utf-8 with ruby 2.4+, make sure you update the csv gem to version 3 or newer (gem 'csv', '~> 3.0' in your Gemfile).
  • Note if you want to replace spaces on both sides of quoted string within comma value...gsub(/,\s+\"/,',"').gsub(/\"\s+,/,'",')
  • 👍 this seems to work with the CSV.foreach method as well
  • csv file could be in MBs, which cannot be opened like that