Invalid UTF-8 byte sequence in SQL Server data prevents write.xlsx from working

tbaker818 0
Viewed 31

I'm trying to pull some data from SQL Server into Excel via a data frame in R using dbGetQuery and I'm getting the following error:

Error in stri_length(newStrs) : 
  invalid UTF-8 byte sequence detected; try calling stri_enc_toutf8()

The best solution I've found is to convert the data with the following code:

sapply(data, iconv, to = "UTF-8")

However, when I do that, my data frame goes from looking like this:

> data

1   Womens Healthcare Nurse
2 Women's Health Care - NCC
3 Women's Health Care - NCC
4 Women's Health Care - NCC
5 Women's Health Care - NCC

to this:

> sapply(data, iconv, to = "UTF-8")

[1,] "Womens Healthcare Nurse"  
[2,] "Women's Health Care - NCC"
[3,] "Women's Health Care - NCC"
[4,] "Women's Health Care - NCC"
[5,] "Women's Health Care - NCC"

I'm not totally clear on what the difference is, but I can write the first data frame to a spreadsheet using openxlsx no problem, but when using the second data frame, the spreadsheet ends up including only the last record. I'm using:

write.xlsx(x = data, file = file)

How can I do this conversion and end up with the same data frame that I can use to write to a spreadsheet correctly?

utf-8 openxlsx r
1 Answers

This more a general R question. One way in base R is:

# create input
dat <- data.frame(num = 1:26, chr = letters)

# select the characters
is_chr <- vapply(dat, inherits, what = "character", NA)

# modify the output
dat[is_chr] <- lapply(dat[is_chr], iconv, to = "UTF-8")
Jan Marvin0
Related