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?

Does this looks like something you might be interested in? stackoverflow.com/questions/8073920/… — oldhomemovie
– oldhomemovie, Commented May 23, 2013 at 11:23

Neil Slater · Accepted Answer · 2013-05-24 09:23:03Z

4

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|

edited May 24, 2013 at 9:23

answered May 23, 2013 at 11:27

Neil Slater

27.4k6 gold badges80 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anand Shah · Accepted Answer · 2013-05-23 11:44:38Z

1

The reason for the error is, the row is Malformed CSV. Have a look here, it says:

Each of the embedded double-quote characters must be represented by a pair of double-quote characters.

1997,Ford,E350,"Super, ""luxurious"" truck"

Fields containing a line-break, double-quote, and/or commas should be quoted. (If they are not, the file will likely be impossible to process correctly).

With that in mind, the following works:

CSV.parse('|test "Some quoted name"|2|12|Machine|', {:col_sep => '|', :quote_char => "'"}).each do |row|
   pp row
end
# => [nil, "test \"Some quoted name\"", "2", "12", "Machine", nil]

edited May 23, 2013 at 11:44

answered May 23, 2013 at 11:36

Anand Shah

15k16 gold badges77 silver badges113 bronze badges

Collectives™ on Stack Overflow

Parsing csv file in Ruby

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related