Special Characters UTF-8

[cjwood]

Hello

I'm really struggling with this problem! I know that this has been raised before in a number of different posts but I can't seem to wrap my head around the problem.

Basically - We've built an app which works perfectly locally. However when transferring to ShinyappsIO - Special characters are no longer displaying correctly.

I haven't got a full reproducible example because this app is pretty big and communicates with internal databases. However the logic is as follows.

Users make selections in the dashboard then an SQL database is queried using dbplyr…

    TranslationsBU <- tbl(DBI_Connection, "Translations") %>%
      filter(Client == Client_SelectionBU) %>%
      filter(Project_ID == Project_SelectionBU) %>%
      filter(Itemisation %in% Itemisation_SelectionBU) %>%
      collect()

This is then rendered in a table...

output$TabBU <- renderTable({Preview})

Locally characters are renderedcorrectly using the UTF-8 encoding. On ShinyappsIO UTF-8 encoding is not

Aldi Süd vs Aldi Süd 

I've Tried following the Unicode advice...

(Shiny - Unicode characters in Shiny apps)

"For shinyapps users, the platform is based on Linux containers, and has a UTF-8 locale. If your app reads/writes data files that contain multi-byte characters, you are strongly recommended to be specific about the encodings when calling the I/O functions, because your local environment may not be based on UTF-8. The functions iconv() , iconvlist() , enc2native() , and enc2utf8() may be useful if you need to convert the encoding from one to another."

I have read through a number of forums but I cant seem to understand how to solve the problem on ShinyappsIO I understand that ShinyappsIO Run on Linux but can'y really grapple with how to solve this problem in the instance of ShinyappsIO (as stated - everything works fine Locally)

Can someone please explain this to me as if I was 5?

[nirgrahamuk]

@cjwood

full reproducible example

This would not be required not even desired.
A reprex should be both minimal and complete.
In your case I would suggest producing a shinyapp with little more than the auto shiny template that studio gives for make a new shiny app, declare a string with examples of UTF 8 characters and directly try to render them in a shiny output. Then you can satisfy yourself that you are getting a relevant difference between local run and deployed run, and also you can share it here so others can help support you without doing additional labour to start, and where it's clear what a starting point is and what would constitute success in fixing it.

That said, I'm wondering whether it may be a font issue.

Regards,
Nir

[nirgrahamuk]

I'm not sure what the issue is, lets try to find it.
I made the simple app that I described to you:

library(shiny)

# Define UI for application that draws a histogram
ui <- fluidPage(
            textOutput("txtout")
)

# Define server logic required to draw a histogram
server <- function(input, output) {

 output$txtout <- renderText(
     "Aldi Süd"
 )
}

# Run the application 
shinyApp(ui = ui, server = server)

note that I had to use the button in Rstudio File menu for save as encoding UTF-8 option for this code to even work locally, as my natural encoding is an ISO and my local complained otherwise... anyway. after saving with the UTF encoding, it ran locally, and I saw the text string.
Then I deployed , no problems.
https://nguk.shinyapps.io/unicodetest/

[cjwood]

Hi Nir

Thanks for this! I also produced a simple app and got the same result - no problems with the display.

This makes me think that the problem is because of importing data from my SQL database... so this is what I'm investigating at the moment.

I'm still confused why the same code importing data from the same database would display differently locally vs on ShinyApps.IO?

[nirgrahamuk]

@cjwood

TranslationsBU <- tbl(DBI_Connection, "Translations") .... etc

@cjwood

output$TabBU <- renderTable({Preview})

ok, so continuing with your shared example. I think there is a gap of processing between your TranslationsBU object, and the Preview object.

I dont know if to work forwards or backwards. but can you tell me the class() of these?
and you can also use Encoding() function to see what encoding they are ?

[cjwood]

[cjwood]

The rendered text above the table output shows what I get when I run the following two snippets of code...

  output$Info1 <- renderText({class(TranslationsBU$To)})
  output$Info2 <- renderText({Encoding(TranslationsBU$To[1:50])})

-Class is Character
-And encoding for most data is unknown except for the offending data point - which is rendering in my example incorrectly (in the simple app i made- It renders correctly when I run it locally)

[cjwood]

global.R

#global
# Load packages
library(dplyr) 
library(dbplyr) 
library(odbc) 
library(DBI) 
library(shiny)
library(data.table)
library(dplyr)

#connection arguments from config
conn_args <- config::get("dataconnection")

ui.R

# ui.R
ui <- shinyUI(fluidPage(
  titlePanel('Render Table'),
  sidebarLayout(
    sidebarPanel(
      actionButton('Render', 'Render Translation Table')
    ),
    mainPanel(
      textOutput("Info1"),
      textOutput("Info2"),
      textOutput("Info3"),
      tableOutput("TabBU")
      
      )
    )
  )
)

server.R

# server.R
server <- function(input, output) {

  
  observeEvent(input$Render, {
  
  DBI_Connection <- dbConnect(odbc(),driver = conn_args$driver,server = conn_args$server,database = conn_args$database,port = conn_args$port,UID = conn_args$uid,PWD = conn_args$pwd) 
    
  TranslationsBU <- tbl(DBI_Connection, "Translations") %>%
    filter(Client == "Lidl") %>%
    filter(Project_ID == "LIFT") %>%
    filter(Itemisation == "Retailers") %>%
    collect()
  
  output$Info1 <- renderText({class(TranslationsBU$To)})
  output$Info2 <- renderText({Encoding(TranslationsBU$To[1:50])})

  output$TabBU <- renderTable({TranslationsBU})
  
  dbDisconnect(DBI_Connection)

  
  })
  
  
}

[josh]

This odbc issue might be relevant.

Could you share some details of the database you are using:

• is it MySQL vs PostgreSQL vs something else
• what version is the database
• what is the encoding of table
• what is the encoding of the column
• what is the default encoding of connections to the database

[cjwood]

Hi Josh

I followed the link above for odbc and have tried both suggested methods but I have yet been able to get ShinyApps.io to display UTF-8 Characters correctly when querying my database. (Although it works fine when running the app locally)

In SQl Server Management Studio... Running

SELECT @@Version

... gives "Microsoft SQL Azure (RTM) - 12.0.2000.8 Dec 4 2019 21:24:18 Copyright (C) 2019 Microsoft Corporation"

The collation of the database is SQL_Latin1_General_CP1_CI_AS

However I have also tried running the following code to change the collation of specific columns to UTF-8

ALTER TABLE dbo.Translations ALTER COLUMN [To] VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8

When querying the data base I have tried setting to both latin1 and utf-8 but cant seem to find a combination which does anything other than work fine locally but not work when publishing to ShinyApps.io

DBI_Connection <- dbConnect(odbc(),driver = conn_args$driver,server = conn_args$server,database = conn_args$database,port = conn_args$port,UID = conn_args$uid,PWD = conn_args$pwd,
                              encoding = "UTF-8")

DBI_Connection <- dbConnect(odbc(),driver = conn_args$driver,server = conn_args$server,database = conn_args$database,port = conn_args$port,UID = conn_args$uid,PWD = conn_args$pwd,
                              encoding = "latin1")

I have also tried setting the encoding of the collected data using Encoding() but that doesn't seem to work.

I have a meeting with our SQL guy in a few hours (I dabble but am certainly not an expert!) so hopefully he will be able to shine some light on this issue.

[josh]

Place sessionInfo() and it's output somewhere in your application, then share the output from running locally and from deploying and running on shinyapps.io.

Did the "SQL guy" have any insight?

[cjwood]

Hello!

Yes finally solved the problem.

Basically SQL server guy helped out by checking that the database was all set up correctly... using SSMS - we checked that each column was nvarchar(255) - which he assured me was what we needed.

From my Apps perspective - Another thing it does apart from get information from a database is to append the database according to stuff user inputs. Even if the input looked to be in UTF-8 format (and indeed was in UTF-8 Format according to Notepad ++) - the database connection string needed to specify not that the encoding was 'UTF-8' but that the encoding was 'windows-1252'

  DBI_Connection <- dbConnect(odbc(),driver = conn_args$driver,server = conn_args$server,database = conn_args$database,port = conn_args$port,UID = conn_args$uid,PWD = conn_args$pwd,encoding = 'windows-1252')

NOTE: I am still unsure why UTF-8 doesn't work when running locally? Ideas would be welcome here. Also I presume that if my app was running locally on a Mac or Linux machine we'd have problems -however my company is all windows so hopefully this shouldn't be a problem (Unless windows 1252 encoding can change from one windows machine to another - I have no idea TBH)

To solve the problem of Local vs Running on ShinyApps I used a config.yml file. This means that the encoding can be changed from Windows 1252 to UTF-8.

Thanks for all the help!

[josh]

I am glad you got things working!