Parsing csv file in Ruby

Question

I'm expiriencing issue with parsing my CSV file and I cannot resolve this for a while now. My pipe seperated CSV file has a special case that fails the parsing. Here is my code :

CSV.parse(data, {:headers => true, :header_converters => [:symbol, :downcase], :col_sep => '|'}).each do |row|
   if row[:name]
      counter += 1
   end

Here is the case that produces Message: Illegal quoting in line 2 :

|test "Some quoted name"|2|12|Machine|

But this one works and other cases work:

|"Some quoted name"|2|12|Machine|

How do I get passed this one?

Answer 1

That message is technically correct. Quotes have special meaning for the CSV format - they would allow you to embed separator characters in the data. Any quotes used within a field therefore need to be escaped if they are part of the data, or the CSV parser should be informed to use some other character for quoting, in which case it will treat any " that it sees as literal data.

If you don't need to support pipes actually within each field, and have some other unused character you can shift this problem off to, Ruby's CSV can be made to consume your (slightly) malformed csv format:

CSV.parse(data, {:col_sep => '|', :quote_char => "%" })

Otherwise, the correct quoting for your problem line is

|"Some ""quoted name"""|2|12|Machine|