Error when trying to export a data frame to .xlsx file using write.xlsx function in "openxlsx" package

Hello all,

I’m trying to export a data frame to .xlsx file using the write.xlsx function in the openxlsx package. I keep receiving the error below and not sure how to get around it. Do you know what the issue is?

write.xlsx (qry_Claims_Transactions,
'/mnt_Hf1fi0002_Everyone/Actuarial/Core Ad Hoc/R files/Claims Transactions/Reconciliation/qry_Claims_Transactions (R) v2019.08.13.xlsx',
sheetName = "qry_Claims_Transactions",
col.names = TRUE,
row.names = FALSE)

Error -
Error in gsub ("&", "&", v, fixed = TRUE) :
input string 3478246 is invalid UTF -8

Hi,

Since I can't see the dataset, I can only make an educated guess, but I think the important part of the error is: input string 3478246 is invalid UTF -8

This means there are unsupported characters in the text that cannot be handled by the file format. It seems that the path sting is all right (although some systems have issues with spaces in path names) so I guess one of the columns in the dataframe qry_Claims_Transactions contains unsupported characters. Could this be the case? If so, you should remove the characters, replace them or save in a format that can handle them.

Hope this helps,
PJ

The iconv function can be used to convert character columns to valid UTF-8. For example (adapted from here):

# Example data with wrong encoding
x <- "fa\xE7ile"
Encoding(x) <- "latin1"

# Check encoding
Encoding(x)

# Convert to valid UTF-8
xx <- iconv(x, from=Encoding(x), to="UTF-8", sub='')

Encoding(xx)
1 Like