Writing Excel formatted csv using readr::write_excel_csv2

[This article was first published on Appsilon Data Science Blog, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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 BOMThe 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.

To leave a comment for the author, please follow the link and comment on their blog: Appsilon Data Science Blog.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)