0

My data contains special characters like German umlauts.

p=structure(list(ppl_code = c(992621L, 992381L, 992136L, 991989L, 
991898L, 991759L, 991681L, 991593L, 991294L, 991036L, 990934L, 
990751L, 990535L, 990411L, 990182L, 989507L), proj_name = c("klo", 
"Dalbygda", "Oosterhorn", "Hån", "Yatir", "Montigny la Cour", 
"Valle Hermoso", "Acciona Honawad - 120 MW", "Apfeltrang", "RiaBlades", 
"General Acha", "Lindau-Böhlitz", "Apfeltrang", "Alcazar Round 2", 
"Peckelsheim", "Linnich 3")), .Names = c("ppl_code", "proj_name"
), row.names = 15:30, class = "data.frame")

When I try to write it into MySQL database :

conn <- dbConnect(
       drv = RMySQL::MySQL(),
       dbname = "mydb",
       host = "#####",
       username = "#####",
       password = "#####")

dbWriteTable(conn, value = p, name = "MyTable",row.names=FALSE)

I'm getting the Encoding error :

could not run statement: Invalid utf8 character string: 'Lindau-B'

I have checked several posts regarding this issue like here and here but they are all general explanation without a clear solution ! can anybody help me with a clear query that could solve this issue ?

Haribo
  • 2,071
  • 17
  • 37
  • The utf-8 is choking on the umlaut over the o. It is not a recognized character in the english version, so you need to find a way of making the word acceptable to sql either remove that and replace with an o or find another encoding paradigm – sconfluentus May 07 '18 at 16:22
  • @sconfluentus, But if I write my table into a csv file and again read it, it would work : `write.csv(p, file = "tmp.csv", fileEncoding = "utf8", quote = FALSE, row.names = FALSE) dbWriteTable(conn, value = "tmp.csv", name = "test1", append = TRUE, row.names = FALSE, sep = ",", quote='\"', eol="\r\n") ` I would like to find a way to avoid this stupid csv conversion ! – Haribo May 07 '18 at 17:36
  • so it may not be in UTF-8 try converting in R instead of export, this link has useful information: https://stackoverflow.com/questions/23699271/force-character-vector-encoding-from-unknown-to-utf-8-in-r – sconfluentus May 07 '18 at 18:27

1 Answers1

0

You need to announce that UTF-8 is being used.

Tool -> Global Options -> Code -> Saving and put UTF-8

rs <- dbSendQuery(con, 'SET NAMES utf8')
Rick James
  • 135,179
  • 13
  • 127
  • 222