Encoding problem with dbWriteTable (to SQL Server)

[Fabian_CO]

Hi folks,

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

 dbWriteTable(con, 
             name = "ci_diplomes", 
             value = ci_diplomes, 
             field.types = c(`code-diplome`="varchar(max)",
             `type-complet`="varchar(max)",
             `validation-alfa`="varchar(max)"),
             fileEncoding="UTF-8",
             overwrite=TRUE)

An overwiew of the output on SQLS :

Many thanks in advance for your assistance,

[Fabian_CO]

Maybe change fileEncoding statement ?

[pieterjanvc]

Hi,

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.

PJ

[Fabian_CO]

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...

[jrkrideau]

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

names(ci_diplomes)
str(ci_diplomes)

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

[pieterjanvc]

Hi,

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

[Fabian_CO]

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

[Fabian_CO]

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.

[Fabian_CO]

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

[Fabian_CO]

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.