Ruby-on-rails CSV - 未加引号的字段不允许 \r 或 \n（第 2 行）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11548637/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CSV - Unquoted fields do not allow \r or \n (line 2)
提问by user984621
Trying to parse a CSV file, but still getting the error message Unquoted fields do not allow \r or \n (line 2)..
尝试解析 CSV 文件，但仍然收到错误消息Unquoted fields do not allow \r or \n（第 2 行）。.
I found here at SO similar topic, where was a hint to do following:
我在这里找到了类似的主题，其中提示执行以下操作：
CSV.open('file.csv', :row_sep => "\r\n") do |csv|
but his unfortunately doesn't works me... I can't change the CSV file, so I would need to fix it in the code.
但不幸的是，他对我不起作用......我无法更改 CSV 文件，所以我需要在代码中修复它。
EDITsample of CSV file:
编辑CSV 文件示例：
A;B;C
1234;...
Is there any way to do it?
有什么办法吗？
Many thanks!
非常感谢！
采纳答案by jslivka
First of all, you should set you column delimiters to ';', since that is not the normal way CSV files are parsed. This worked for me:
首先，您应该将列分隔符设置为“;”，因为这不是解析 CSV 文件的正常方式。这对我有用：
CSV.open('file.csv', :row_sep => :auto, :col_sep => ";") do |csv|
csv.each { |a,b,c| puts "#{a},#{b},#{c}" }
end
From the 1.9.2 CSV documentation:
来自 1.9.2 CSV 文档：
Auto-discovery reads ahead in the data looking for the next
\r\n,\n, or\rsequence. A sequence will be selected even if it occurs in a quoted field, assuming that you would have the same line endings there.
自动发现在寻找下一个数据预读
\r\n，\n或\r序列。假设您在那里有相同的行结尾，即使它出现在带引号的字段中，也会选择一个序列。
回答by Mike S
Simpler solution if the CSV was touched or saved by any program that may have used weird formatting (such as Excel or Spreadsheet):
如果 CSV 被任何可能使用奇怪格式的程序（例如 Excel 或电子表格）触摸或保存，则更简单的解决方案：
- Open the file with any plaintext editor (I used Sublime Text 3)
- Press the enter key to add a new line anywhere
- Save the file
- Remove the line you just added
- Save the file again
- Try the import again, error should be gone
- 使用任何纯文本编辑器打开文件（我使用了 Sublime Text 3）
- 按回车键在任意位置添加新行
- 保存文件
- 删除您刚刚添加的行
- 再次保存文件
- 再次尝试导入，错误应该消失了
回答by David Silva Smith
For me I was importing LinkedIn CSV and got the error.
对我来说，我正在导入 LinkedIn CSV 并出现错误。
I removed the blank lines like this:
我删除了这样的空行：
def import
csv_text = File.read('filepath', :encoding => 'ISO-8859-1')
#remove blank lines from LinkedIn
csv_text = csv_text.gsub /^$\n/, ''
@csv = CSV.parse(csv_text, :headers => true, skip_blanks: true)
end
回答by Cimm
I realize this is an old post but I recently ran into a similar issue with a badly formatted CSV file that failed to parse with the standard Ruby CSV library.
我意识到这是一篇旧帖子，但我最近遇到了一个类似的问题，格式错误的 CSV 文件无法使用标准的 Ruby CSV 库进行解析。
I tried the SmarterCSVgem which parsed the file in no time. It's an external library so it might not be the best solution for everyone but it beats parsing the file myself.
我尝试了SmarterCSVgem，它立即解析了文件。它是一个外部库，因此它可能不是每个人的最佳解决方案，但它胜过自己解析文件。
opts = { col_sep: ';', file_encoding: 'iso-8859-1', skip_lines: 5 }
SmarterCSV.process(file, opts).each do |row|
p row[:someheader]
end
回答by Danil Gaponov
In my case I had to provide encoding, and a quote char that was guaranteed to not occur in data
在我的情况下，我必须提供编码，以及保证不会出现在数据中的引号字符
CSV.read("file.txt", 'rb:bom|UTF-16LE', {:row_sep => "\r\n", :col_sep => "\t", :quote_char => "\x00"})
回答by Michael
In my case, the first row of the spreadsheet/CSV was a double-quoted bit of introduction text. The error I got was: /Users/.../.rvm/rubies/ruby-2.3.0/lib/ruby/2.3.0/csv.rb:1880:in `block (2 levels) in shift': Unquoted fields do not allow \r or \n (line 1). (CSV::MalformedCSVError)
就我而言，电子表格/CSV 的第一行是一段双引号引言文本。我得到的错误是：/Users/.../.rvm/rubies/ruby-2.3.0/lib/ruby/2.3.0/csv.rb:1880:in `block (2 levels) in shift': Unquoted字段不允许 \r 或 \n（第 1 行）。(CSV::MalformedCSVError)
I deleted the comment with " characters so the .csv ONLY had the .csv data, saved it, and my program worked with no errors.
我删除了带有 " 字符的评论，所以 .csv 只有 .csv 数据，保存它，我的程序没有错误。
回答by Markus Andreas
If you have to deal with files coming from Excel with newlines in cells there is also a solution.
如果您必须处理来自 Excel 的文件在单元格中带有换行符，那么还有一个解决方案。
The big disadvantage of this way is, that no semicolons or no double quotes in strings are allowed.
这种方式的最大缺点是，不允许在字符串中使用分号或双引号。
I choose to go with no semicolons
我选择不带分号
if file.respond_to?(:read)
csv_contents = file.read
elsif file_data.respond_to?(:path)
csv_contents = File.read(file.path)
else
logger.error "Bad file_data: #{file_data.class.name}: #{file_data.inspect}"
return false
end
result = "string"
csv_contents = csv_contents.force_encoding("iso-8859-1").encode('utf-8') # In my case the files are latin 1...
# Here is the important part (Remove all newlines between quotes):
while !result.nil?
result = csv_contents.sub!(/(\"[^\;]*)[\n\r]([^\;]*\")/){ + ", " + }
end
CSV.parse(csv_contents, headers: false, :row_sep => :auto, col_sep: ";") do |row|
# do whatever
end
For me the solution works fine, if you deal with large files you could run into problems with it.
对我来说，该解决方案工作正常，如果您处理大文件，则可能会遇到问题。
If you want to go with no quotes just replace the semicolons in the regex with quotes.
如果你想不带引号，只需用引号替换正则表达式中的分号。
回答by Steven Yap
Another simple solution to fix the weird formatting caused by Excel is to copy and paste the data into Google spreadsheet and then download it as a CSV.
解决由 Excel 引起的奇怪格式的另一个简单解决方案是将数据复制并粘贴到 Google 电子表格中，然后将其下载为 CSV。
