R - SQLDF issue with special characters

Question

I'm having an issue running sqldf in R and special characters.

Here is a small detail of the code I'm running to give you an idea of what's going on:

First I read my data from an excel sheet (using R's xlsx package), the method xlsx2 seems to get the data correctly and characters seem to be showing special characters such as 'Ñ'

verif_oblig <- try(read.xlsx2("My computer/Filename.xlsx", sheetName = 'VERIF_OBLIG'))
if("try-error" %in% class(verif_oblig)) 
  verif_oblig <- Empty()

Then I start running my sql query using sqldf and the resulting table seems to replace Ñ characters for Ã‘. Here's the query:

verif_oblig_v2 <- sqldf("
  select 
  a.*,
  case when b.Estado is null then 'NO GENERADO'
  else b.Estado end as ESTADO, 
  case when resultado_operacion in ('EXITO','CORRECTO')
  then 'EXITO' 
  else 'SIN EXITO' 
  end as RESULTADO_ACTUAL
  from
  verif_oblig a left join fin2016 b 
  on
  a.CUPS = b.CUPS_Largo and a.DIVISION = b.DIVISION")

Can anyone help me find a solution for this?

Thank you very much

No it's a custom function that creates an empty dataframe with the required columns and no data. However that's not where the error is happening — Javier Semprun, Nov 08 '16 at 09:42
Changing the encoding does work, but I'm not sure it fixes the issue. It is sqldf that is introducing the error — Javier Semprun, Nov 08 '16 at 09:57
You need to create reproduceble error ( small data.frame with such error) to alow anyone copy paste and test — Batanichek, Nov 08 '16 at 09:59
I am using 2 sets of data, one I get from an xlsx file with this structure: — Javier Semprun, Nov 08 '16 at 10:23
Copy paste this into an spread sheet and save it as a .xlsx document — Javier Semprun, Nov 08 '16 at 10:25
Add data into question throuht edit http://stackoverflow.com/posts/40482718/edit — Batanichek, Nov 08 '16 at 10:25
Are you tried copy paste it into xlsx?( i get one cell with all data) — Batanichek, Nov 08 '16 at 10:26
As for the othe data fram (b side of the left join), I get data from a csv file structured like this: — Javier Semprun, Nov 08 '16 at 10:33
csv: https://drive.google.com/file/d/0ByeyRhm9JCh6alVCSzhXTEJwVlk/view?usp=sharing — Javier Semprun, Nov 08 '16 at 10:43
xlsx: https://drive.google.com/file/d/0ByeyRhm9JCh6bWRLTGhvSS1RMkE/view?usp=sharing — Javier Semprun, Nov 08 '16 at 10:43
tdc_fin2016 <- read.csv2(PATH, header = TRUE, sep = ";", quote = "\"", dec = ",", stringsAsFactors = F, fill=TRUE) — Javier Semprun, Nov 08 '16 at 10:45

score 0 · Accepted Answer · answered Nov 15 '16 at 15:34

I ended up solving it by simply replacing the characters that were bugging after the sql query using gsub like this:

clear_errors <- function(table, campo){
table <- as.data.frame(table)
table[,campo] <- c(gsub("Ã'","Ñ",c(tabla_entrada[,campo])))
table[,campo]<- c(gsub("Ã©","é",c(tabla_entrada[,campo])))
table[,campo]<- c(gsub("Ã³", "ó",c(tabla_entrada[,campo])))
table[,campo] <- c(gsub("Ãº","ú",c(tabla_entrada[,campo])))
table[,campo] <- c(gsub("Ã±","ñ",c(tabla_entrada[,campo])))
table[,campo] <- c(gsub("Ã","í",c(tabla_entrada[,campo])))
table[,campo] <- c(gsub("O","A",c(tabla_entrada[,campo])))
return(table)

}

It isn't the most elegant solution but it works.

I think the issue happens because xlsx formats characters as factors and probably uses a different encoding than sqldf does for them. If someone can find out exactly what's going on I'd very much appreciate it (just out of curiosity)

You coudl shorten this slightly using the approach given here: http://stackoverflow.com/questions/17517319/r-replacing-foreign-characters-in-a-string — G. Grothendieck, Nov 18 '16 at 16:30

R - SQLDF issue with special characters

1 Answers1