dbWriteTable ignores UTF-8 encoding

Question

I am trying to write my dataframe into mysql table using UTF-8 encoding. The encoding seems to not working properly. When I open the table in a Workbench environment the UTF-8 encoding is not set as it should. Strange characters appears.

mysql connection:

conn = dbConnect(MySQL(), user='user', password='pw', dbname='dbname', host='127.0.0.1')

setting UTF-8:

dbSendQuery(conn, 'SET NAMES utf8')

the dataframe need to be inserted into mysql table:

id = c(1, 2, 3); vnt = c("Individualioji įmonė", "Akcinė Bendrovė", "Mažoji bendrija")
df = data.frame(id, vnt)

Writing df to mysql table

dbWriteTable(conn,  value = df, name = "mysql_df", row.names=F, append = TRUE)

My question, is it possible to set charset to UTF-8 using dbWriteTable function?

score 0 · Answer 1 · edited May 23 '17 at 11:54

0

You have "Mojibake". Example: ž turned into Å¾ because latin1 was involved somewhere. You have not provided enough info for me to point specifically at what to fix, but this Question and Answer should help. (Search for Mojibake.)

edited May 23 '17 at 11:54

Community

1
1

answered Mar 15 '17 at 23:52

Rick James

135,179
13
127
222

The desired result would be a vector of vnt = c("Individualioji įmonė", "Akcinė Bendrovė", "Mažoji bendrija") with latin letters. I have no problem with encoding in R, but when i send it to mysql it fails to pick a correct encoding. – Aleksandr Mar 16 '17 at 07:28

score 0 · Accepted Answer · answered Mar 16 '17 at 16:46

0

Solved. The solution is so simple. Right before writing data to mysql you need to overwrite dbConnect object:

conn = dbConnect(MySQL(), user='', password='', dbname='', host='127.0.0.1')

answered Mar 16 '17 at 16:46

Aleksandr

1,814
11
19

dbWriteTable ignores UTF-8 encoding

2 Answers2