<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/writing-excel-formatted-csv-using-readrwrite_excel_csv2/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://appsilondatascience.com/blog/rstats/2017/12/08/readr.html"> Appsilon Data Science Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<h2>Why this post?</h2>
<p>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.”</p>
<p>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 <strong>utf-8</strong> encoded.</p>
<p>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 <a rel="nofollow" href="https://stackoverflow.com/questions/6002256/is-it-possible-to-force-excel-recognize-utf-8-csv-files-automatically/6002338#6002338" target="_blank">few people</a> faced this problem in the past.</p>
<p>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.</p>
<p>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.</p>
<h2>Current available solution in R</h2>
<p>What we want is the following, write a csv file with <strong>utf-8</strong> encoding and BOM<label for="‘BOM’"><i></i> sticky note</label><span>The byte order mark (BOM) is a Unicode character which tells about the encoding of the document. </span>. This has been <a rel="nofollow" href="https://github.com/tidyverse/readr/issues/375" target="_blank">addressed in R by RStudio</a> in <code>readr</code> package.</p>
<figure>
<pre><code data-lang="r"><span>library</span><span>(</span><span>readr</span><span>)</span><span>
</span><span>write_excel_csv</span><span>(</span><span>mtcars</span><span>,</span><span> </span><span>&quot;assets/data/readr/my_file.csv&quot;</span><span>)</span></code></pre>
</figure>
<p>This is great and solves the problem with opening the file in Excel, but… supports only one type of locale.</p>
<h2>Show me your locale</h2>
<p>Depending on where you live you might have different locale. <a rel="nofollow" href="https://en.wikipedia.org/wiki/Locale_(computer_software)" target="_blank">Locale</a> 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.</p>
<p>This means that number formatting can differ between different regions, for example in the USA <code>.</code> is used as a decimal separator, but on the other hand almost whole Europe uses <code>,</code>. This <a rel="nofollow" href="https://en.wikipedia.org/wiki/Decimal_mark" target="_blank">article</a> shows how countries around the world define their number formats.</p>
<p>This proves that there is a large need to extend the <code>readr</code> functionality and allow users to save Excel with European locale easily and quickly. This is not currently possible since <code>write_excel_csv</code> only allows one to write in the US locale.</p>
<h2>New addition to readr</h2>
<p>We proposed to add <code>write_excel_csv2()</code> to <code>readr</code> package that would allow the user to write a csv with <code>,</code> as a decimal separator and <code>;</code> as column separator. To be consistent with naming convention in R for functions reading in (e.g. <code>read.csv()</code> and <code>read.csv2()</code>) or writing (e.g. <code>write.csv()</code> and <code>write.csv2()</code>) csv files with different delimiter we decided to simply add <code>2</code> to <code>write_excel_csv()</code>.</p>
<figure>
<pre><code data-lang="r"><span>tmp</span><span> </span><span>&lt;-</span><span> </span><span>tempfile</span><span>()</span><span>
</span><span>on.exit</span><span>(</span><span>unlink</span><span>(</span><span>tmp</span><span>))</span><span>
</span><span>readr</span><span>::</span><span>write_excel_csv2</span><span>(</span><span>mtcars</span><span>,</span><span> </span><span>tmp</span><span>)</span></code></pre>
</figure>
<p>To prove that it works, let’s read the first two lines and inspect the output.</p>
<figure>
<pre><code data-lang="r"><span>readr</span><span>::</span><span>read_lines</span><span>(</span><span>tmp</span><span>,</span><span> </span><span>n_max</span><span> </span><span>=</span><span> </span><span>2</span><span>)</span></code></pre>
</figure>
<figure>
<pre><code data-lang="text">## [1] &quot;mpg;cyl;disp;hp;drat;wt;qsec;vs;am;gear;carb&quot;
## [2] &quot;21,0;6;160,0;110;3,90;2,620;16,46;0;1;4;4&quot;</code></pre>
</figure>
<p><code>write_excel_csv2()</code> is already available for download from <code>readr</code> repository and should be available on CRAN with the next release.</p>
<figure>
<pre><code data-lang="r"><span>devtools</span><span>::</span><span>install_github</span><span>(</span><span>&quot;tidyverse/readr&quot;</span><span>)</span></code></pre>
</figure>
<p>We hope you and your business team will find this addition useful.</p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://appsilondatascience.com/blog/rstats/2017/12/08/readr.html"> Appsilon Data Science Blog</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/Iy67wSAlgDA" height="1"></a></div>