Invalid Byte Sequence in UTF-8 from Excel file

Question

(Ruby 2.5) I have a method that reads and parses a csv file that's being uploaded via Alchemy CMS

def process_csv(csv_file, current_user_id, original_filename)
    lock_importer

    errors           = []
    index            = 0
    string_converter = lambda { |field| field.strip }
    total            = CSV.foreach(csv_file, headers: true).count
    csv_string = csv_file.read.encode!("UTF-8", "iso-8859-1", invalid: :replace)

    CSV.parse(csv_string, headers: true, header_converters: :symbol, skip_blanks: true, converters: [string_converter] ) do |row|
    # do other stuff
end

but when I try to upload a csv file that has a column (name) with a string that contains special characters then I receive the Invalid Byte Sequence in UTF-8 error. I'm trying to test the value N'öt Réal Stô'rë.

I've tried a few solutions that I found on the web but no luck - any suggestions?

What is `csv_file`? A File-object? Why you dont read it as `csv_file.read(encoding: "UTF-8:iso-8859-1", invalid: :replace)` — knut, Oct 14 '19 at 20:52
`csv_file` is just a hash. Doesn't `csv_file.read(encoding: "UTF-8:iso-8859-1", invalid: :replace)` pretty much do the same thing? @knut — Doolan, Oct 14 '19 at 21:05
Hash does not support `read`, so `csv_file`can't be a Hash. You use `csv_file.read.encode!`. So first you read `csv_file` (whatever this does. I still don't know what `csv_file`is). The result is probably a String in an unknown encoding. This is encoded - maybe wrong. My `csv_file.read(encoding: "UTF-8:iso-8859-1", invalid: :replace) ` expects a File in UTF-8. But again, this is just a guess. — knut, Oct 15 '19 at 21:14
By the way: What is `string_converter`? - Why do you need it? — knut, Oct 15 '19 at 21:57
@knut maybe it is just a file object then? Just attempting to fix this bug for another team. `string_converter` does some lambda stuff - i updated the code — Doolan, Oct 18 '19 at 03:49
If you don't know what ist is, then add a `p csv_file` and `p csv_file.class`, then you know what you get and then it may be possible to giv an answer. As I wrote in my first comment, I guess it ia a File-object. — knut, Oct 18 '19 at 19:58

score 0 · Answer 1 · answered Oct 18 '19 at 20:30

It's unclear what your csv_fileis. I guess it is a File-object.

Sometimes I got csv from Excel as a UTF-16. So let's try an example:

I have a csv-file stored in UTF-16BE with the following content:

line;comment;UmlautÄ
1;Das ist UTF-16 BE;Ä
2;öüäÖÄÜ;Ä

If I execute the following code:

require 'csv'
def process_csv(csv_file)
    csv_string = csv_file.read#.encode!("UTF-8", "iso-8859-1", invalid: :replace)
    CSV.parse(csv_string, headers: true, skip_blanks: true, col_sep: ';') do |row|
      p row # do other stuff
    end
end

process_csv(File.open('example_utf16BE.txt'))

then I get also a Invalid byte sequence in UTF-8-error.

If I use

process_csv(File.open('example_utf16BE.txt', 'rb', encoding: 'BOM|utf-16BE'))

then everything works.

So I guess, you get a File-object in a wron encoding and the code csv_file.read.encode!("UTF-8", "iso-8859-1", invalid: :replace) is a code part to repair this problem.

What you can do:

Add to you code:

    p csv_file
    p csv_file.external_encoding

You should get

#<File:example_utf16BE.txt>
#<Encoding:UTF-16BE>

Now check, if the file (in my example: example_utf16BE.txt has really the encoding of the 2nd line.

If not, try to adapt the File-object creation. If this is not possible, then you can try to use csv_file.set_encoding 'utf-8' to change the encoding before you read the content.

Invalid Byte Sequence in UTF-8 from Excel file

1 Answers1