Last Updated: February 25, 2016
·
1.08K
· samd

Convert latin1 to UTF-8 in SQLite using Ruby

For some unknown reason I had an SQLite database which used latin1 encoded strings rather than utf-8. Here's an example of how to convert them using ruby:

require 'sqlite3'
require 'iconv'

i = Iconv.new('UTF-8', 'LATIN1')

db = SQLite3::Database.new "test.db"
stm = db.prepare "SELECT CompanyName, CustomerID FROM Customers"
rs = stm.execute

rs.each_hash { |row| puts row }

rs.reset
rs.each_hash do |row|
  update_str = "UPDATE Customers SET CompanyName = '%s' WHERE CustomerID = '%s';" % [i.iconv(row["CompanyName"]).gsub("'","''"), row["CustomerID"]]
  puts update_str
  update_stm = db.prepare update_str
  update_stm.execute
  update_stm.close
end

stm.close
db.close