Ruby CSV不会在带引号的字符串中读取逗号格式的数字 [英] Ruby CSV not reading comma-formatted numbers in quoted strings

问题描述

我在Rails应用程序中使用ruby的CSV类（ruby 2.1.5）从上传的csv文件加载记录。用户正在使用另存为从Excel创建csv文件，并且根据数字的格式，它们可能会保存为带逗号的引号字符串 - 在这种情况下，逗号后的数字部分将被删除。 / p>

如果输入值为3,500，则 3500 应该保存，而是 3 。

我意识到可以在Excel ，但它也似乎应该是容易处理的东西（如果我不得不告诉他们，程序不能处理这种基本情况下，我会得到一个重要的WTF从用户）。此外，由于csv文件的头匹配在数据库中的列名，我没有必要写列特定的处理程序 - 我只是做一个属性分配。我希望保持这样，因为我有更多的影响列比我包含在我的例子。

输入记录：

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

处理函数

  def import_csv 
 CSV.foreach（file.path，headers：true，header_converters：：symbol，skip_blanks：true，converters：：all）do | row | 
＃hash输入行
 row_hash = row.to_hash 
＃使用original_record中的散列创建一个新行
 fl = self.forecast_lines.create（original_record：row_hash.to_s） 
＃将散列写入记录属性
 fl.attributes = row_hash 
 fl.save 
 end 
 end 
  

原始记录哈希：

  recordid =>11015，：valcurrent =>3,500，：valdate =>6/7/2013​​} 
  

valcurrent的数据类型为float。但是保存到数据库的价值不是 3500.0 ，而是 3.0 。

解决方案

您可以添加自定义转换器来正确处理您的数字列。不确定是否涵盖所有可能的格式选项，但它看起来像这样：

创建lambda：

  comma_numbers =  - >（s）{（s =〜/ ^ \d +，/）？ （s.gsub（'，'，''）。to_f）：s} 
  

您的转换器：

  CSV :: Converters [：comma_numbers] = comma_numbers 
  pre> 
 
 
新转换器不包含在转换器中：：all将其作为数组添加：
 转换器：[：all，：comma_numbers] 
  
 
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.
解决方案
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]


                        
这篇关于Ruby CSV不会在带引号的字符串中读取逗号格式的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！