Encoding problem with dbWriteTable (to SQL Server)

Hi folks,

I have a concern of encoding on an export to SQL Server with dbWriteTable, here my code :

             name = "ci_diplomes", 
             value = ci_diplomes, 
             field.types = c(`code-diplome`="varchar(max)",

An overwiew of the output on SQLS :


Many thanks in advance for your assistance,

Maybe change fileEncoding statement ?


Are you sure the data in R is in the correct format before you write it to the database? Check the ci_diplomes table in your environment and see if the names are correct there. If not, the error lies in reading / processing the data before you write it to the database.


Good evening @pieterjanvc !

Check the ci_diplomes table in your environment and see if the names are correct there

You mean check the source encoding with guess_encoding function ? Is it a function useful tu check encoding of an R dataframe ? Sorry, i'm a rookie...

I am not sure what @pieterjanvc is suggesting but he may mean


which can give you some useful basic information about the data.frame.


What I mean is that the encoding issue might have arisen when you read or created the data. Often times if you read in a CSV for example with special characters, they will not be read correctly into R and when you then write them to a database the same incorrect characters are written. So in that case the dbWriteTable is not the issue, but the original data reading. I don't know if this is the case, as I don't know where ci_diplomes comes from, but it's at lest something to check. So just click the ci_diplomes in the environment pane and look at it in RStudio

Ok thanks but theses functions return only the type of columns, all are characters, not encoding of the file.

Hi there and thanks for your help.

It's an XML file. I used guess_encoding on it which retrieved that :

> guess_encoding("diplomes.xml",max(100))
# A tibble: 3 x 2
  encoding   confidence
  <chr>           <dbl>
1 UTF-8           1    
2 ISO-8859-1      0.570
3 ISO-8859-2      0.56 

So, i will try to write my table in SQLS with fileEncoding = ISO-8859-1 or ISO-8859-1 in dbWriteTable(). I tried "UTF-8" but it failed.

I tried the three encoding but the probleme still persists...

Finally I found the solution with adding : encoding = "latin1" in my SQL Server connection statement and also encoding = "latin1" in dbWritetable().
Besides, I replace "varchar(max)" by "nvarchar(max)".

Anyway, thank a lot for your help, it works fine now.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.