Encoding Issue when fetching data from MySQL DB into R

Question

I'm using the "RMySQL" library in R to load data from a local MySQL DB into R:

con <- dbConnect(MySQL(), user="root", password="****", dbname="twitterdata", host="localhost")
dataframe <- dbGetQuery(con, "SELECT id, plaintext, category FROM table")

When I inspect the dataframe, I see a lot of unformatted characters such as the slanted apastrophe (´) which shows up as â€™.

After some research, I discovered that according to this site, some special characters (including the slanted apastrophe) are not part of the ISO-8859-1 standard but of the Windows-1252 standard.

When I run

Sys.getlocale("LC_CTYPE")

in R, it says:

"German_Austria.1252"

Doesn't it already say that I'm on the correct encoding?! In my DB (Default Charset: UTF-8), the apostrophe is encoded well.

I also tried to add a parameter to the dbConnect statement DBMSencoding="utf-8" but with no effect.

When I run

Encoding(x)

in R (where x is the character vector - a sentence), the answer is

"unknown"

Does anybody know now to solve this issue?

Thanks a lot!

Does this help? Afaik, a ' should be part of iso-8859-x as well as utf-8. You probably need to encode it correctly within R. — lukeA
– lukeA, Commented Jun 3, 2015 at 12:08
Yes the ' is part of the iso-8859-x, but not the ´ and the `. The interesing thing is that when I write the data to a file, it's correctly shown again. — user944351
– user944351, Commented Jun 3, 2015 at 12:25
So, did you try iconv(dataframe [, 1], from = "UTF-8", to = "latin1") as suggested? It's hard to debug without having access to the actual data... — lukeA
– lukeA, Commented Jun 3, 2015 at 13:48
Oh man! That works! I don't know how I could overlooked that! Thanks a lot!!! — user944351
– user944351, Commented Jun 3, 2015 at 14:23
Okay, doesn't work either. When I do that, some entries simply become "NA"... — user944351
– user944351, Commented Jun 3, 2015 at 14:42

Márcio Mocellin · Accepted Answer · 2019-06-05 19:41:21Z

1

Do it:

con <- dbConnect(MySQL(), user="root", password="****", dbname="twitterdata", host="localhost", encoding = "latin1")

answered Jun 5, 2019 at 19:41

Márcio Mocellin

2912 gold badges6 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Encoding Issue when fetching data from MySQL DB into R

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related