Parsing CSV string in ruby

Question

I have the following string - it's not exactly comma separated but has the same effect as a csv dataset:

response = "Date;Amount;Account;User\n2014-12-01;12.01;abcxyz;user1\n2014-12-01;10.09;fine;user2\n\r\n\t\t\r\n"

I tried running the following to parse it:

CSV.parse(response, :col_sep => ";", :row_sep => :auto)

but I get the following error:

CSV::MalformedCSVError: Unquoted fields do not allow \r or \n

Any idea why this would be happening?

I also tried doing a response.gsub!("\t", "") to see if that was the issue, but it didn't seem to help.

I just noticed that the CSV.new documentation mentions :auto as a supported value for row_sep, but not col_sep. Because it's picking up this whitespace, it stays there, and you get this error because the fields weren't quoted. — Nicky McCurdy
– Nicky McCurdy, Commented Dec 20, 2014 at 7:20
I think it's because you swapped row_sep and col_sep without swapping the ; and whitespace characters in response. — Nicky McCurdy
– Nicky McCurdy, Commented Dec 20, 2014 at 7:33

Arup Rakshit · Accepted Answer · 2014-12-20 07:36:01Z

16

I got it work with the use of #strip :

require 'csv'

response = "Date;Amount;Account;User\n2014-12-01;12.01;abcxyz;user1\n2014-12-01;10.09;fine;user2\n\r\n\t\t\r\n"

CSV.parse(response.strip, :col_sep => ';') do |row|
  p row
end

output :

arup$ ruby a.rb
["Date", "Amount", "Account", "User"]
["2014-12-01", "12.01", "abcxyz", "user1"]
["2014-12-01", "10.09", "fine", "user2"]

answered Dec 20, 2014 at 7:36

Arup Rakshit

119k30 gold badges270 silver badges328 bronze badges

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

1 Comment

Astm Over a year ago

it's working well

roob · Accepted Answer · 2014-12-20 07:58:22Z

6

This will give you each row in an array.

CSV.parse( response.gsub( /[\r\t]/, '' ), col_sep: ";" )
=> [["Date", "Amount", "Account", "User"], ["2014-12-01", "12.01", "abcxyz", "user1"], ["2014-12-01", "10.09", "fine", "user2"], [], []]

Unless you want to merge all rows into a single line, you need to leave the \n for the parser to interpret as a new row.

answered Dec 20, 2014 at 7:58

roob

1,1367 silver badges11 bronze badges

Comments

Nicky McCurdy · Accepted Answer · 2014-12-20 07:38:48Z

1

An easy way to fix this is to replace any consecutive whitespace characters with a single newline before you parse the string. Then you can use the newline as your row separator, instead of setting it to :auto. This should make CSV parsing faster (since it takes more time for :auto to guess your delimiter), though performance is technically also negatively affected by the additional call to gsub.

CSV.parse(response.gsub(/\s+/, "\n"), col_sep: ';', row_sep: "\n")

answered Dec 20, 2014 at 7:38

Nicky McCurdy

19.8k5 gold badges63 silver badges91 bronze badges

Collectives™ on Stack Overflow

Parsing CSV string in ruby

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related