R database connection encoding mistakes with accents

February 15, 2022

I am connecting to a SQL Server database using this code:

DBI::dbConnect(odbc(), 
               Driver = "...", 
               Server = "...", 
               Database = "...", 
               UID = "...", 
               PWD = "...", 
               Port = ...)

And later I amb extracting a table using the function dbGetQuery.

The problem is that I have columns with accents (spanish language) in the SQL Server, I can see them correctly there, but when I get the table in R I cannot see them correctly, I see the symbol � in the letters with accents.

How could I fix that?

>Solution :

It should work to add the argument encoding = <the encoding of your database> to DBI::dbConnect().

Do you know the encoding your database is using? If not, I would try latin1 first, as that is rather common for western european languages (se e.g. here).

So try changing your code to:

DBI::dbConnect(
  odbc::odbc(), 
  Driver = "...", 
  Server = "...", 
  Database = "...", 
  UID = "...", 
  PWD = "...", 
  Port = "...",
  encoding = "latin1"
)