Ruby: cannot parse Excel file exported as CSV in OS X

Question

I'm using Ruby's CSV library to parse some CSV. I have a seemingly well-formed CSV file that I created by exporting an Excel file as CSV.

However CSV.open(filename, 'r') causes a CSV::IllegalFormatError.

There are no rogue commas or quotation marks in the file, nor anything else that I can see that might cause problems.

I suspect the problem could be to do with line endings. I am able to parse data entered manually via a text editor (Aquamacs). It is just when I try with data exported from Excel (for OS X) that problems occur. When I open up the exported CSV in vim, all the text appears on one line, with ^M appearing between lines.

From the docs, it seems that you can provide open with a row separator; however I am unsure what it should be in this case.

Answer 1

Try: CSV.open('filename', 'r', ?,, ?\r)

As cantlin notes, for Ruby 2 it's:

CSV.new('file.csv', 'r', :col_sep => ?,, :row_sep => ?\r)

I'm pretty sure these will DTRT for you. You can also "fix" the file itself (in which case keep the old open) with the following vim command: :%s/\r/\r/g

Yes, I know that command looks like a total no-op, but it will work.

Answer 2

Stripping \r characters seemed to work for me

CSV.parse(File.read('filename').gsub(/\r/, ' ')) do |row|
  ...
end

Answer 3

Another option is to open the CSV file or the original spreadsheet in Excel and save it as "Windows Comma Separated" rather than "Comma Separated Values". This will output the file with line endings that FasterCSV is able to understand.