Cannot parse a CSV file - "CSV::MalformedCSVError: Unquoted fields do not allow \r or \n"

Question

I am trying to parse uploaded CSV files. Here's the full error message:

[Worker(host:PC.local pid:69594)] Job ImportJob (id=4) FAILED (3 prior attempts) with CSV::MalformedCSVError: Unquoted fields do not allow \r or \n (line 1).

And the CSV files looks like this:

A1;A2;A3;A4;A5
B1;B2;B3;B4;B5
C1;C2;C3;C4;C5

And this is how I loop through the data in the CSV file:

data = SmarterCSV.process(
  file,
  {
    :col_sep => ';',
    :chunk_size => 100,
    :remove_empty_values => false,
    :remove_empty_hashes => false
  }
) do |d|

What's the problem here? I've tried to put the headline to the file, even tried to remove it (the version posted above), but in both cases I get the same error. How do I get rid of it?

What machine are you working on (windows? mac?) was the file created on that machine? — Uri Agassi, Jun 17 '14 at 17:05
Mac. I created the CSV file from an XLS file (that was created on Windows probably). — user984621, Jun 17 '14 at 17:06
I have seen something similar with a CSV file that was exported out of MS Excel. To fix it I essentially just did a `gsub` to swap out any carriage returns. — Rob Di Marco, Jun 17 '14 at 17:07

Isaac Betesh · Answer 1 · 2014-06-17T18:11:43.187

3

Assuming the CSV file was created on Windows, it's not surprising that the line endings are "\r\n". Try explicitly making that your row separator:

:row_sep => "\r"

Putting it all together:

data = SmarterCSV.process(file, 
  :col_sep => ';', 
  :row_sep => "\r", 
  :chunk_size => 100, 
  :remove_empty_values => false, 
  :remove_empty_hashes => false) do |d|

edited Jun 17 '14 at 18:11

answered Jun 17 '14 at 17:09

Isaac Betesh

2,935
28
37

Unfortunately, still the same error (even restart of the app didn't help). – user984621 Jun 17 '14 at 17:23
2

What about making the row_sep `\r\n`? – Isaac Betesh Apr 13 '15 at 17:08

score 1 · Answer 2 · answered Jul 02 '15 at 00:20

1

I encountered this exact problem but with the built-in CSV module.

I solved it by using the IO read mode as text: 'rt', not 'r':

require 'csv'

CSV.open(file_name, 'rt').each do |row|
  # blah, blah
end

answered Jul 02 '15 at 00:20

Chris Reay

21
1

Cannot parse a CSV file - "CSV::MalformedCSVError: Unquoted fields do not allow \r or \n"

2 Answers2