Escape Comma from CSV in Ruby

Question

I'm creating a CSV download in my Ruby application which has some Dollar amount fields. These fields has data in cyrrency format like $35,456 and when such data comes in CSV it get seperated into two fiedls , data after comma moved to next column in CSV.

Here is my code to render CSV

Sell Date, Sell Amount
- @rows.each do |row|
  = "#{row[0]},#{number_to_currency(row[1], :precision => 2)}"

Here is the action used to return CSV

def fifolog
  @rows = Report.fifolog()
respond_to do |format|
  format.csv { render csv: {:rows =>@rows }}
end
end

Is there any escape character i can use to escape "," in ruby

thanks

Joshua Pinter · Accepted Answer · 2015-04-29T16:06:07.650

Use Ruby's built-in `to_csv` method.

If you haven't already done so, you'll need to require 'csv'.

Sell Date, Sell Amount
- @rows.each do |row|
  = [ row[0], number_to_currency(row[1], :precision => 2) ].to_csv( row_sep: nil ).html_safe

to_csv is available right on the Array and does all the escaping you'd expect it to do.

row_sep: nil prevents the \n at the end of each row since you're already doing that with each. Try it without that and you'll see that you get an extra blank line. If you were just generating a single CSV string then you'd need to keep the \n to separate the rows.

html_safe prevents the " characters from showing up in your CSV file.

That should do it!

JP

It's worth noting you need to `require 'csv'` before these methods are available. — tadman, Apr 29 '15 at 16:02

Trevor · Answer 2 · 2012-09-07T16:03:10.243

6

Use double quotes around values that have a comma:

"$35,456"

Edit: Using FasterCSV, change the following lines:

respond_to do |format|
  format.csv { render csv: {:rows =>@rows }}

to

csv_data = FasterCSV.generate do |csv| 
    csv << ["Sell Date", "Sell Amount"] 

    @rows.each do |row| 
      csv << [row[0],number_to_currency(row[1], :precision => 2)] 
    end 
  end 

  send_data csv_data, 
            :type => 'text/csv', 
            :disposition => "attachment; filename=file.csv"

edited Sep 07 '12 at 16:03

answered Sep 07 '12 at 13:06

Trevor

6,659
5
35
68

i tried and it dispaley &quote; in Excel, but this may be wrong syntax, i used this = "\"#{row[0]}\",\"#{number_to_currency(row[1], :precision => 2)}\".. – Anil D Sep 07 '12 at 13:09
That is the correct syntax. I tried it and it displays properly in Open Office. Try opening the raw CSV file in Notepad or vi and see if it actually contains the double quotes. – Trevor Sep 07 '12 at 13:15
If the CSV is coming from a Rails app, then you'll need to set the content-type: `send_data csv_data, :type => "text/plain", :filename=>"file.csv", :disposition => 'attachment'` – Trevor Sep 07 '12 at 13:18
1

i open it in Notepad and Textpad and its has &quot, i m using Ubuntu and when opened MS Excel , gives same result – Anil D Sep 07 '12 at 13:19
1

Another option for handling CSV and escaping is FasterCSV (http://fastercsv.rubyforge.org/) – Trevor Sep 07 '12 at 13:21
Trevor, i edited my question, added Action used, please check and suggest where i need to add various attributes for CSV, thanks – Anil D Sep 07 '12 at 13:25

Escape Comma from CSV in Ruby

2 Answers2

Use Ruby's built-in to_csv method.

Use Ruby's built-in `to_csv` method.