4

I have a vector in R that contains special characters, e.g. x = "Köln".

I have trouble exporting that back to either excel or csv format (preferably in Excel).

Here is what I tried:

#Exporting to excel
library(excel.link)
myExcelProcess = COMCreate("Excel.Application")
xl.workbook.add()
excelRange = myExcelProcess[["ActiveSheet"]]$Range("A1:A1")
xl.write(x, excelRange, row.names = FALSE) # this creates an excel file with "ruined" characters (Köln)`

#Exporting to csv
write.csv(x = routesCities, file = "out.csv", fileEncoding = "UTF-8") # This creates a csv containing "Koln" - the Umlaut has disappeared.

Any help would be greatly appreciated.

EDIT (Regarding the flagging as possible duplicate): imho the question is rather different than the proposed duplicate, since it refers to encoding issues during data transfer between R and Excel, not failing to run a write.xlsx() function. Furthermore, my attempts do not result in an error (there is no error message), but the results are not the desired ones. It might be the case that the same answer (using a different library) could apply here, too, but the question itself is quite different.

Ruli
  • 2,592
  • 12
  • 30
  • 40
GerasimosPanagiotakopoulos
  • 577
  • 7
  • 22
  • 1
    There are no special characters in Excel *or* UTF8. Excel uses Unicode too - `xlsx` is a zip containing XML files. If you get ruined characters it's because you tried to use ANSI, not UTF8 text using the wrong codepage – Panagiotis Kanavos Oct 17 '18 at 11:56
  • Possible duplicate of [write.xlsx function not working](https://stackoverflow.com/questions/47942984/write-xlsx-function-not-working) – Panagiotis Kanavos Oct 17 '18 at 11:58
  • *Don't* use interop to create Excel files. Use a package that creates real xlsx files without requiring a local Excel installation, eg `openxlsx`, or `writexl` – Panagiotis Kanavos Oct 17 '18 at 11:59
  • Thank you for posting; To clarify: `stri_enc_isutf8(x)` returns TRUE (how would you edit the code to avoid possibly using ANSI?). – GerasimosPanagiotakopoulos Oct 17 '18 at 12:01
  • Indeed I would have liked to use openxlsx, but I get another error there during saving. Maybe this is an issue for another thread though. – GerasimosPanagiotakopoulos Oct 17 '18 at 12:02
  • First, I wouldn't use interop. That alone solves the problem for Excel. As for CSV, are you sure the *data* is UTF8? If the data is a string in your script, is the *script* saved as UTF8? If not, the data is read or executed using whatever the system's codepage is. If that doesn't match the original codepage, you'll get garbled text – Panagiotis Kanavos Oct 17 '18 at 12:04
  • Post the *actual* code used to read the data and create the Excel file in the question please. It's not possible to help otherwise. – Panagiotis Kanavos Oct 17 '18 at 12:05
  • I am reading an Excel file which contains UTF-8 characters. The file is being read with `openxlsx::read.xlsx()`. After performing some calculations, I am trying to save the file back as xlsx. Please let me know if indeed more information is required. – GerasimosPanagiotakopoulos Oct 17 '18 at 12:14
  • 1
    fyi, the suggestion of `library(writexl)` actually worked - thanks! Please post it, so that I can use it as an accepted answer. (However, what does that imply about the other failed attempts? If openxl handles performs the task correctly, then why does my saving to csv attempt fail?) – GerasimosPanagiotakopoulos Oct 17 '18 at 12:17

1 Answers1

4

The readr package that is part of the tidyverse has a function write_excel_csv that exports data frames to csv in UTF-8 encoding in such a way that opening them directly in excel preserves special characters. While I haven't tried exhaustively, I had a similar issue with exporting data with the "greater than or equal to" symbol ≥, represented in string variables as the string \u2265. It came out perfectly using this function, even when my previous attempts at using write.csv, even with the option "fileEncoding="UTF-8", had still left strange symbols in the file when opened in excel.

hreed7
  • 41
  • 3