Ruby导入CSV报Unquoted fields do not allow \r or \n错误如何解决

报错根因 #

你遇到的CSV::MalformedCSVError: Unquoted fields do not allow \r or \n (line 1)报错本质原因有两个：

• 预处理阶段没有完全清除换行符格式差异，Windows导出的CSV常带BOM头或混合换行符，触发CSV解析器的严格校验
• 行尾带逗号的非标格式，和Ruby默认CSV解析的严格规则不兼容

修正后的预处理代码 #

def csv_replace_empty_string
  Dir.foreach(Rails.root.join('db', 'csv_export')) do |filename|
    next if ['.', '..', 'extract_db_into_csv.sh', 'import_csv.rb'].include?(filename)
    file_path = Rails.root.join('db', 'csv_export', filename)
    # 二进制读取避免编码兼容问题
    content = File.read(file_path, mode: 'rb')
    # 统一所有换行符为\n
    content.gsub!(/\r\n?/, "\n")
    # 清除UTF-8 BOM头，解决首行解析报错问题
    content.gsub!(/\A\xEF\xBB\xBF/, '')
    # 替换空双引号为带空格的引号，避免空值解析异常
    content.gsub!(/(?<=,|^)""(?=,|$)/, '" "')
    # 统一行尾结构，对齐表头字段数量
    header_field_count = content.lines.first.split(',').count
    content = content.lines.map do |line|
      stripped_line = line.strip
      next if stripped_line.empty?
      # 去除行尾多余逗号
      normalized_line = stripped_line.chomp(',')
      # 补全字段数量和表头一致，避免字段数量不匹配报错
      fields = normalized_line.split(',', header_field_count)
      (fields + [''] * (header_field_count - fields.size)).join(',')
    end.compact.join("\n") + "\n"
    File.write(file_path, content)
  end
end

修正后的导入代码 #

核心是开启liberal_parsing宽松解析参数，适配非标CSV格式：

def import_csv_into_db
  Dir.foreach(Rails.root.join('db', 'csv_export')) do |filename|
    next if ['.', '..', 'extract_db_into_csv.sh', 'import_csv.rb'].include?(filename)
    file_path = Rails.root.join('db', 'csv_export', filename)
    model_class = File.basename(filename, File.extname(filename)).classify.constantize
    CSV.foreach(
      file_path,
      headers: true,
      skip_blanks: true,
      row_sep: "\n",
      quote_char: '"',
      liberal_parsing: true # 宽松解析，支持行尾空字段、非标格式
    ) do |row|
      # 统一处理空值，避免空字符串入库
      row_hash = row.to_hash.transform_values { |val| val == ' ' ? nil : val&.strip }
      model_class.create!(row_hash)
      puts "Insert on table #{model_class.name} complete"
    end
  end
end

兜底方案 #

如果调整参数后仍有换行符报错，可手动逐行读取后解析，跳过CSV的自动行分割逻辑：

def import_csv_into_db
  Dir.foreach(Rails.root.join('db', 'csv_export')) do |filename|
    next if ['.', '..', 'extract_db_into_csv.sh', 'import_csv.rb'].include?(filename)
    file_path = Rails.root.join('db', 'csv_export', filename)
    model_class = File.basename(filename, File.extname(filename)).classify.constantize
    lines = File.readlines(file_path).map(&:strip).reject(&:empty?)
    headers = CSV.parse_line(lines.first, liberal_parsing: true)
    lines[1..].each do |line|
      row = CSV.parse_line(line, headers: headers, liberal_parsing: true)
      row_hash = row.to_hash.transform_values { |val| val == ' ' ? nil : val&.strip }
      model_class.create!(row_hash)
      puts "Insert on table #{model_class.name} complete"
    end
  end
end

内容的提问来源于stack exchange，提问作者Pro_grammer