CODEWITHSUNDEEP
ruby-on-railsrubycsv
MWeber
MWeber

Reputation: 613

Ruby CSV not reading comma-formatted numbers in quoted strings

I'm using ruby's CSV class (ruby 2.1.5) in a Rails app to load records from an uploaded csv file. Users are creating the csv files from Excel with a "Save As", and depending on the format of the numbers, they might be saved as quoted strings with commas -- in which case the part of the number after the comma is dropped.

If the input value is "3,500", then 3500 should be saved, but instead it is 3.

I realize it is possible to clean this up in Excel, but it also seems like something that should be easily handled (and I would get a major WTF from the users if I have to tell them the program can't handle this basic case.) Also, since the headers of the csv file match the column names in the database, I haven't had to write column-specific handlers - I just do an attributes assignment. I'm hoping to keep it that way because I have quite a few more affected columns than what I'm including in my example.

Input record:

recordid,valcurrent,valdate
11015,"3,500",6/7/2013

Processing function

def import_csv(file)
  CSV.foreach(file.path, headers: true, header_converters: :symbol, skip_blanks: true, converters: :all) do |row|
    # hash the input row 
    row_hash = row.to_hash
    # create a new row with the hash in original_record
    fl = self.forecast_lines.create(original_record: row_hash.to_s)
    # write the hash to the record attributes
    fl.attributes = row_hash
    fl.save
  end
end

Original record hash:

Original record: {:recordid=>"11015", :valcurrent=>"3,500", :valdate=>"6/7/2013"}

The data type for valcurrent is float. But the valcurrent value that gets saved to the database is not 3500.0, but 3.0.

Upvotes: 1

Views: 1824

Answers (2)

daryn
daryn

Reputation: 926

You can add a custom converter that handles your number columns correctly. Not sure if this covers all of your possible formatting options, but it'd look something like this:

Create a lambda:

comma_numbers = ->(s) {(s =~ /^\d+,/) ? (s.gsub(',','').to_f) : s}

Add it to your converters:

CSV::Converters[:comma_numbers] = comma_numbers

The new converter is not included in converters: :all so add it as an array: 

converters: [:all, :comma_numbers]

Upvotes: 5

Nakul Agrawal
Nakul Agrawal

Reputation: 31

The problem is not with CSV but how Ruby converts string to float.

In Ruby:

"3,500".to_f => 3.0

That's the reason 3.0 is getting stored in database. You should change your import_csv method to handle commas.

Also I don't think you should be doing row_hash.to_s in create. Create method as such accepts a hash as a parameter.

Upvotes: 3

Related Questions