Apostrophe caused XML problems in workbook about writexl HOT 5 CLOSED

Can you give us an example of a data frame that you imported with a stray comma, so that we can reproduce the problem?

from writexl.

I've found it really hard to reproduce - so I've trimmed the problematic csv and zipped it up (attached). This produces the error:
library(tidyverse)
library(writexl)
df <- read_csv("problem.csv")
list <- df%>% split(.$SHEET)
write_xlsx(list, "problem.xlsx")

problem.zip

I think this is a very niche issue. I'm not sure how this csv was generated.

from writexl.

I should have mentioned - you don't see the error until you open the resulting xlsx file in Excel.

from writexl.

Yes I see it. The problem actually already appears in read_csv. Line 15 gets read incorrectly:

> read_csv("~/Downloads/problem.csv")[15,]
Parsed with column specification:
cols(
  SHEET = col_integer(),
  NAME = col_character()
)
# A tibble: 1 x 2
  SHEET NAME                
  <int> <chr>               
1     1 "Ward\x92s Prolific"

The word Wardís has not been correctly read from the csv and the string is corrupt. I'm not sure how excel reads this csv file actually.

from writexl.

So the problem is that your csv file is encoded in Windows-1252 but R assumes UTF-8. Try this:

library(tidyverse)
library(writexl)
df <- read_csv("~/Downloads/problem.csv")
df$NAME <- iconv(df$NAME, "Windows-1252", "UTF-8")
write_xlsx(df, "works.xlsx")

Note that wikipedia explains why we can't use latin1:

It is very common (on the Internet) to mislabel Windows-1252 text with the charset label ISO-8859-1. A common result was that all the quotes and apostrophes (produced by "smart quotes" in word-processing software) were replaced with question marks or boxes on non-Windows operating systems

from writexl.