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

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

mysqlrutf-8sasiso-8859-1


Solution 1:[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)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Robert L.

Related Questions