Writing ISO8859 data to MySQL in R (error:"could not run statement: Invalid utf8 character string")

Question

I need to import an SAS Transfer File (.xpt) to MySQL. Because I don't own SAS, I tried a) first importing the .xpt in R b) then using dbWriteTable from the library RMySQL to create a table from the data frame.

The problem is that the data frame contains ISO8859 characters and MySQL (or dbWriteTable?) expects UTF-8. (Detail: Precisely a degree character, which is B0 in ISO8859 and C2B0 in UTF8) I receive the following output:

dbWriteTable(con,"new_table",mydata,overwrite=T) Fehler in .local(conn, statement, ...) : could not run statement: Invalid utf8 character string: 'Hallo'>

So I tried converting the data with iconv like iconv(mydata,"ISO8859-1","UTF8"), but it only seems to work vice-versa (it correctly converts UTF8 to ISO8859, but not the other way round).

Then I was trying a lot with

SET character_set_client = latin1;
SET character_set_results = latin1;
SET character_set_connection = latin1;

but without any luck.

Any help would be so appreciated

Answer 1

I found a workaround I want to share:

As iconv only works UTF8->ISO8859, this is no use, but you can:

• write the data frame to a temp file using fileEncoding="utf8"
• read it back

• then use dbWriteTable

  write.table(mydata,file="tmp.txt", fileEncoding ="utf8")
  mydata_utf8 <- read.table(file="tmp.txt",encoding="utf8") dbWriteTable(con,"new_table",mydata_utf8,overwrite=T)