CSV - Unquoted fields do not allow \r or \n (line 2)

Question

Trying to parse a CSV file, but still getting the error message Unquoted fields do not allow \r or \n (line 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.

EDIT sample of CSV file:

A;B;C
1234;...

Is there any way to do it?

Many thanks!

Solution 1

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.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:

Auto-discovery reads ahead in the data looking for the next \r\n, \n, or \r sequence. A sequence will be selected even if it occurs in a quoted field, assuming that you would have the same line endings there.

Solution 2

Simpler solution if the CSV was touched or saved by any program that may have used weird formatting (such as Excel or Spreadsheet):

1. Open the file with any plain text editor (I used Sublime Text 3)
2. Press the enter key to add a new line anywhere
3. Save the file
4. Remove the line you just added
5. Save the file again
6. Try the import again, error should be gone

Solution 3

For me I was importing LinkedIn CSV and got the error.

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

Solution 4

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"})

Solution 5

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.

I tried the SmarterCSV gem 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.

opts = { col_sep: ';', file_encoding: 'iso-8859-1', skip_lines: 5 }
SmarterCSV.process(file, opts).each do |row|
  p row[:someheader]
end

View more solutions

Comments

[user984621]

Hi Linuxios, I updated the original post

[Linuxios]

Did you set the record separator to ;?

[Sam Axe]

That example is NOT a csv file. It is a delimited text file. Similar structure, but thats not enough. Big difference. CSV = Comma-Separated Values, and besides specifying the delimiter as a comma there are other very specific data formatting rules that a csv must conform to. A delimited text file does not have to conform to these rules, though it can choose to.

[Linuxios]

@Boo: Exactly. user, you'd do better using split and some ad hoc stuff.

[user984621]

I am a bit confused now... I am trying follow your example and when I print puts csv, I get this: <#CSV io_type:File io_path:"file.csv" encoding:ASCII-8BIT lineno:0 col_sep:";" row_sep:"\n" quote_char:"\""> - but how can I from this hash get the data?

[jslivka]

The open method opens an IO block, so you can do something like this for the hash: CSV.open('file.csv', :row_sep => :auto, :col_sep => ";") do |csv| csv.each { |a,b,c| puts "#{a},#{b},#{c}" } end

[grant]

This worked for me. I was on a mac and had two CSVs I had downloaded that would not work before but worked after saving. Incidientally, they both had a blank row at the top of the file. Not sure if it was deleted that row or saving the file that fixed it for me. Regardless, thank you!

[Apoorv Parijat]

No idea what this did but worked. Must have trimmed trailing whitespace characters.

[madav]

this helped me, had some highlighting in my csv, while the above fix did not work i cleared formatting and resaved to csv from google sheets

[Paulo Fidalgo]

Where did you find the option for 'rb:bom|UTF-16LE'?

[Danil Gaponov]

@PauloFidalgo I don't remember, it was more than 4 years ago. It took me quite a while to read that CSV.