Problem with retrieving a table from Chinook dataset due to character

Question

I have created a database in MySQL with data from the Chinook dataset, which has fictitious information on customers that buy music.

One of the tables ("Invoice"), has the billing addresses, which has characters in diverse languages:

InvoiceId   CustomerId    InvoiceDate            BillingAddress    
1           2             2009-01-01 00:00:00    Theodor-Heuss-Straße 34
2           4             2009-01-02 00:00:00    Ullevålsveien 14
3           8             2009-01-03 00:00:00    Grétrystraat 63
4           14            2009-01-06 00:00:00    8210 111 ST NW

I tried to retrieve the data using R, with the following code:

library(DBI)
library(RMySQL)
library(dplyr)
library(magrittr)
library(lubridate)
library(stringi)

# Step 1 - Connect to the database ----------------------------------------
con <- DBI::dbConnect(MySQL(),
                      dbname = Sys.getenv("DB_CHINOOK"),
                      host = Sys.getenv("HST_CHINOOK"),
                      user = Sys.getenv("USR_CHINOOK"),
                      password = Sys.getenv("PASS_CHINOOK"),
                      port = XXXX)

invoices_tbl <- tbl(con, "Invoice") %>%
  collect()

The connection is ok, but when trying to visualize the data, I can't see the special characters:

> head(invoices_tbl[,1:4])
# A tibble: 6 x 4
  InvoiceId CustomerId InvoiceDate         BillingAddress              
                                                   
1         1          2 2009-01-01 00:00:00 "Theodor-Heuss-Stra\xdfe 34"
2         2          4 2009-01-02 00:00:00 "Ullev\xe5lsveien 14"       
3         3          8 2009-01-03 00:00:00 "Gr\xe9trystraat 63"        
4         4         14 2009-01-06 00:00:00 "8210 111 ST NW"            
5         5         23 2009-01-11 00:00:00 "69 Salem Street"           
6         6         37 2009-01-19 00:00:00 "Berger Stra\xdfe 10"

My question is, should I change something in the configuration inside MySQL? Or is it an issue with R? How can I see the special characters? What is the meaning of \xdfe?

Please, any help will be greatly appreciated.

user_3732271 · Answer

The hexadecimal format can be converted with iconv

invoices_tbl$BillingAddress <- iconv(invoices_tbl$BillingAddress, 
        "latin1", "utf-8")

-output

invoices_tbl
  InvoiceId CustomerId         InvoiceDate          BillingAddress
1         1          2 2009-01-01 00:00:00 Theodor-Heuss-Straße 34
2         2          4 2009-01-02 00:00:00        Ullevålsveien 14
3         3          8 2009-01-03 00:00:00         Grétrystraat 63
4         4         14 2009-01-06 00:00:00          8210 111 ST NW
5         5         23 2009-01-11 00:00:00         69 Salem Street
6         6         37 2009-01-19 00:00:00        Berger Straße 10

data

invoices_tbl <- structure(list(InvoiceId = 1:6, CustomerId = c(2L, 4L, 8L, 14L, 
23L, 37L), InvoiceDate = c("2009-01-01 00:00:00", "2009-01-02 00:00:00", 
"2009-01-03 00:00:00", "2009-01-06 00:00:00", "2009-01-11 00:00:00", 
"2009-01-19 00:00:00"), BillingAddress = c("Theodor-Heuss-Stra\xdfe 34", 
"Ullev\xe5lsveien 14", "Gr\xe9trystraat 63", "8210 111 ST NW", 
"69 Salem Street", "Berger Stra\xdfe 10")), row.names = c("1", 
"2", "3", "4", "5", "6"), class = "data.frame")

Problem with retrieving a table from Chinook dataset due to character

1 Answers

data