Read CSV file with special characters in Ruby and save it in SQL Server

I am trying to import a CSV file (UTF-8 encoding) in Ruby (2.0.0) into my database (MSSQL 2008R2, COLLATION French_CI_AS

), but special characters (French vowel accents) are not stored correctly: éèçôü

becomes éèçôü

(or some other similar skeleton).

I am using this piece of code to read the file:

CSV.foreach(file, col_sep: ';', encoding: "utf-8") do |row|
   # ...


I tried a different encoding parameters in CSV ( utf-8

, iso-8859-1

, windows-1252

), but no one has kept the special characters correctly.

Before you ask, my database collation supports these characters as we have successfully imported data containing those used by PHP importers. If I delete the data using puts

or file logger everything works right.

Is there something wrong with my code, or do I need to specify something else (like encoding a ruby ​​class file, for example)?


EDIT: Data persistence is done using the PHP REST API which works great with accented characters. It stores data as it is received.

In Ruby, I parse my data, store it in an object, and then post a JSON encoded object in the body of my PUT request. But if I use the SQL query directly from Ruby, the problem remains:

query = <<-SQL
    UPDATE MyTable SET MyTable_title = '#{row_data['title']}' WHERE MyTable_id = '#{row_data['id']}'
res = db.execute query



source to share

1 answer

I thought it had something to do with the type of encoding in your CSV file, so I started digging into that. I found that the Windows-1252 encoding will insert control characters.

You can read more about this here: Converting special characteristics like ¼ and à back to their original latin letters in C #



All Articles