Problem with retrieving a table from Chinook dataset due to character

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              
      <int>      <int> <chr>               <chr>                       
1         1          2 2009-01-01 00:00:00 "Theodor-Heuss-Straxdfe 34"
2         2          4 2009-01-02 00:00:00 "Ullevxe5lsveien 14"       
3         3          8 2009-01-03 00:00:00 "Grxe9trystraat 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 Straxdfe 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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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-Straxdfe 34", 
"Ullevxe5lsveien 14", "Grxe9trystraat 63", "8210 111 ST NW", 
"69 Salem Street", "Berger Straxdfe 10")), row.names = c("1", 
"2", "3", "4", "5", "6"), class = "data.frame")


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x