when we import csv data, how eliminate "invalid byte sequence in UTF-8"

Ruby 1.9 CSV has new parser that works with m17n. The parser works with Encoding of IO object in the string. Following methods: ::foreach, ::open, ::read, and ::readlines could take in optional options :encoding which you could specify the the Encoding.

For example:

CSV.read('/path/to/file', :encoding => 'windows-1251:utf-8')

Would convert all strings to UTF-8.

Also you can use the more standard encoding name 'ISO-8859-1'

CSV.read('/..', {:headers => true, :col_sep => ';', :encoding => 'ISO-8859-1'})

CSV.parse(File.read('/path/to/csv').scrub)

I answered a similar question that deals with reading external files in 1.9.2 with non-UTF-8 encodings. I think that answer will help you a lot: Character Encoding issue in Rails v3/Ruby 1.9.2

Note that you need to know the source encoding for you to convert it anything reliably. There are libraries like the one I linked to in my other answer that can help you determine this.

Also, if you aren't loading the data from a file, you can convert the encoding of a string in 1.9.2 quite easily:

'string'.encode('UTF-8')

However, it's rare that you're building a string in another encoding, and it's best to convert it at the time it's read into your environment if possible.

Ruby 1.9 can change string encoding with invalid detection and replacement:

str = str.encode('UTF-8', :invalid => :replace)

For unusual strings such as strings loaded from a file of unknown encoding, it's wise to use #encode instead of a regex, #gsub, or #delete, because these all need the string to be parsed-- but if the string is broken, it can't be parsed, so those methods fail.

If you get a message like this:

error ** from ASCII-8BIT to UTF-8

Then you're probably trying to convert a binary string that's already in UTF-8, and you can force UTF-8:

str.force_encoding('UTF-8')

If you know the original string is not in binary UTF-8, or if the output string has illiegal characters, then read up on Ruby encoding transliterations.

If you are using Rails, you can try to fix it with the following

'Your string with strange stuff #@~'.mb_chars.tidy_bytes

It removes you the invalid utf-8 chars and replaces it with valid ones. More info: https://apidock.com/rails/String/mb_chars

Upload the CSV file to Google Docs Spreadsheet and re-download it as a CSV file. Import and voila! (Worked in my case)

Presumably Google converts it to the wanted format..

Source: Excel to CSV with UTF-8 Encoding

As mentioned by someone else, scrub works well to clean this up in Ruby 2.1+. If you have a large file you may not want to read the whole thing into memory, so you can use scrub like this:

data = IO::read(file_path).scrub("")
CSV.parse(data, :col_sep => ',', :headers => true)  do |row|
   puts row
end

I am using MAC and I was having the same error:

rescue in parse:Invalid byte sequence in UTF-8 in line 1 (CSV::MalformedCSVError)

I added :encoding => 'ISO-8859-1' that resolved my error and csv file could be read.

results = CSV.read("query_result.csv",{:headers => true, :encoding => 'ISO-8859-1'})

:headers => true : If set to :first_row or true, the initial row of the CSV file will be treated as a row of headers. If set to an Array, the contents will be used as the headers. If set to a String, the String is run through a call of ::parse_line with the same :col_sep, :row_sep, and :quote_char as this instance to produce an Array of headers. This setting causes #shift to return rows as CSV::Row objects instead of Arrays and #read to return CSV::Table objects instead of an Array of Arrays.

irb(main):024:0> rows = CSV.new(StringIO.new("a,b,c\n1,2,3"), headers: true)
=> <#CSV io_type:StringIO encoding:UTF-8 lineno:0 col_sep:"," row_sep:"\n" quote_char:"\"" headers:true>
irb(main):025:0> rows = CSV.new(StringIO.new("a,b,c\n1,2,3"), headers: true).to_a
=> [#<CSV::Row "a":"1" "b":"2" "c":"3">]
irb(main):026:0> rows.first['a']
=> "1"

In above example you can clearly see that this also enables us to use data as hashes. The only thing you would need to be careful about while using headers: true that it won't allow any duplicate headers as keys are unique in hashes.

Only do this

anyobject.to_csv(:encoding => 'utf-8')