How to encode csv file in Roo (Rails) : invalid byte sequence in UTF-8

Question

I am trying to upload a csv file but getting invalid byte sequence in UTF-8 error. I am using 'roo' gem.

My code is like this :

def upload_results_csv file

    spreadsheet = MyFileUtil.open_file(file)
    header = spreadsheet.row(1) # THIS LINE RAISES THE ERROR

    (2..spreadsheet.last_row).each do |i|
      row = Hash[[header, spreadsheet.row(i)].transpose]
      ...
      ...
end

class MyFileUtil

  def self.open_file(file)
    case File.extname(file.original_filename)
      when ".csv" then
        Roo::Csv.new(file.path,csv_options: {encoding: Encoding::UTF_8})
      when ".xls" then
        Roo::Excel.new(file.path, nil, :ignore)
      when ".xlsx" then
        Roo::Excelx.new(file.path, nil, :ignore)
      else
        raise "Unknown file type: #{file.original_filename}"
    end
  end

end.

I don't know how to encode csv file. Please help!

Thanks

What is the encoding of the csv file?

Patrick Oscity
– Patrick Oscity

2014-03-11 12:13:45 +00:00
Commented Mar 11, 2014 at 12:13 — Patrick Oscity
– Patrick Oscity, Commented Mar 11, 2014 at 12:13

Patrick Oscity · Accepted Answer · 2014-03-11 13:45:37Z

7

To safely convert a string to utf-8 you can do:

str.encode('utf-8', 'binary', invalid: :replace, undef: :replace, replace: '')

also see this blog post.

Since the roo gem will only take filenames as constructor argument, not plain IO objects, the only solution I can think of is to write a sanitized version to a tempfile and pass it to roo, along the lines of

require 'tempfile'

def upload_results_csv file
    tmpfile = Tempfile.new(file.path)
    tmpfile.write(File.read(file.path).encode('utf-8', 'binary', invalid: :replace, undef: :replace, replace: ''))
    tmpfile.rewind

    spreadsheet = MyFileUtil.open_file(tmpfile, file.original_filename)
    header = spreadsheet.row(1) # THIS LINE RAISES THE ERROR

    # ...
ensure
    tmpfile.close
    tmpfile.unlink
end

You need to alter MyFileUtil as well, because the original filename needs to be passed down:

class MyFileUtil
  def self.open_file(file, original_filename)
    case File.extname(original_filename)
      when ".csv" then
        Roo::Csv.new(file.path,csv_options: {encoding: Encoding::UTF_8})
      when ".xls" then
        Roo::Excel.new(file.path, nil, :ignore)
      when ".xlsx" then
        Roo::Excelx.new(file.path, nil, :ignore)
      else
        raise "Unknown file type: #{original_filename}"
    end
  end
end

edited Mar 11, 2014 at 13:45

answered Mar 11, 2014 at 12:17

Patrick Oscity

54.9k19 gold badges149 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Junaid Over a year ago

Thank p11y for quick reply. I am trying to encode csv file not string. csv_file_name.encode('utf-8') or csv_file_name.force_encoding() do not work for me.

Patrick Oscity Over a year ago

You must encode the string that comes from the file before it is parsed by the csv library, probably in the method MyFileUtil.open_file

Patrick Oscity Over a year ago

@Junaid, please have a look at my edit and see if it works for you.

Junaid Over a year ago

Sounds interesting. I will try your solution. Thanks @p11y

hraynaud Over a year ago

@PatrickOscity This worked like a charm for me. Saved me a lot of headaches. Thanks!

Collectives™ on Stack Overflow

How to encode csv file in Roo (Rails) : invalid byte sequence in UTF-8

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related