Writing Excel formatted csv using readr::write_excel_csv2

Why this post?

Currently, my team and I are building a Shiny app that serves as an interface for a forecasting model. The app allows business users to interact with predictions. However, we keep getting feature requests, such as, “Can we please have this exported to Excel.”

Our client chose to see results exported to a csv file and wants to open them in Excel. App is already running on the Linux server and the csv that can be downloaded via app are utf-8 encoded.

If you are a Linux user you may not be aware that Windows Excel is not able to recognize utf-8 encoding automatically. It turns out that a few people faced this problem in the past.

Obviously, we cannot have a solution where our users are changing options in Excel or opening the file in any other way than double clicking.

We find having a Shiny App that allows for Excel export to be a good compromise between R/Shiny and Excel. It gives the user the power of interactivity and online access, while still preserving the possibility to work with the results in the environment they are most used to. This a great way to gradually accustom users with working in Shiny.

Current available solution in R

What we want is the following, write a csv file with utf-8 encoding and BOM. sticky noteThe byte order mark (BOM) is a Unicode character which tells about the encoding of the document. This has been addressed in R by RStudio in readr package.

library(readr)

write_excel_csv(mtcars, "assets/data/readr/my_file.csv")

This is great and solves the problem with opening the file in Excel, but… supports only one type of locale.

Show me your locale

Depending on where you live you might have different locale. Locale is a set of parameters that defines the user’s language, region and any special variant preferences that the user wants to see in their user interface.

This means that number formatting can differ between different regions, for example in the USA . is used as a decimal separator, but on the other hand almost whole Europe uses ,. This article shows how countries around the world define their number formats.

This proves that there is a large need to extend the readr functionality and allow users to save Excel with European locale easily and quickly. This is not currently possible since write_excel_csv only allows one to write in the US locale.

New addition to readr

We proposed to add write_excel_csv2() to readr package that would allow the user to write a csv with , as a decimal separator and ; as column separator. To be consistent with naming convention in R for functions reading in (e.g. read.csv() and read.csv2()) or writing (e.g. write.csv() and write.csv2()) csv files with different delimiter we decided to simply add 2 to write_excel_csv().

tmp <- tempfile()
on.exit(unlink(tmp))
readr::write_excel_csv2(mtcars, tmp)

To prove that it works, let’s read the first two lines and inspect the output.

readr::read_lines(tmp, n_max = 2)

## [1] "mpg;cyl;disp;hp;drat;wt;qsec;vs;am;gear;carb"
## [2] "21,0;6;160,0;110;3,90;2,620;16,46;0;1;4;4"

write_excel_csv2() is already available for download from readr repository and should be available on CRAN with the next release.

devtools::install_github("tidyverse/readr")

We hope you and your business team will find this addition useful.