Writing Excel formatted csv using readr::write_excel_csv2

Estimated time:
time
min

<h2 id="why-this-post">Why this post?</h2> 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 <strong>utf-8</strong> 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 <a href="https://stackoverflow.com/questions/6002256/is-it-possible-to-force-excel-recognize-utf-8-csv-files-automatically/6002338#6002338" target="_blank" rel="noopener noreferrer">few people</a> 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. <h2 id="current-available-solution-in-r">Current available solution in R</h2> What we want is the following, write a csv file with <strong>utf-8</strong> encoding and BOM.<label class="margin-toggle" style="font-size: 0.8em; text-decoration: underline;" for="‘BOM’"><i class="fa fa-sticky-note" aria-hidden="true"></i> sticky note</label><input id="‘BOM’" class="margin-toggle" type="checkbox" /><span class="marginnote">The byte order mark (BOM) is a Unicode character which tells about the encoding of the document. </span> This has been <a href="https://github.com/tidyverse/readr/issues/375" target="_blank" rel="noopener noreferrer">addressed in R by RStudio</a> in <code class="highlighter-rouge">readr</code> package. <figure class="highlight"> <pre><code class="language-r" data-lang="r"><span class="n">library</span><span class="p">(</span><span class="n">readr</span><span class="p">)</span> <br><span class="n">write_excel_csv</span><span class="p">(</span><span class="n">mtcars</span><span class="p">,</span> <span class="s2">"assets/data/readr/my_file.csv"</span><span class="p">)</span></code></pre> </figure> This is great and solves the problem with opening the file in Excel, but… supports only one type of locale. <h2 id="show-me-your-locale">Show me your locale</h2> Depending on where you live you might have different locale. <a href="https://en.wikipedia.org/wiki/Locale_(computer_software)" target="_blank" rel="noopener noreferrer">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. This means that number formatting can differ between different regions, for example in the USA <code class="highlighter-rouge">.</code> is used as a decimal separator, but on the other hand almost whole Europe uses <code class="highlighter-rouge">,</code>. This <a href="https://en.wikipedia.org/wiki/Decimal_mark" target="_blank" rel="noopener noreferrer">article</a> shows how countries around the world define their number formats. This proves that there is a large need to extend the <code class="highlighter-rouge">readr</code> functionality and allow users to save Excel with European locale easily and quickly. This is not currently possible since <code class="highlighter-rouge">write_excel_csv</code> only allows one to write in the US locale. <h2 id="new-addition-to-readr">New addition to readr</h2> We proposed to add <code class="highlighter-rouge">write_excel_csv2()</code> to <code class="highlighter-rouge">readr</code> package that would allow the user to write a csv with <code class="highlighter-rouge">,</code> as a decimal separator and <code class="highlighter-rouge">;</code> as column separator. To be consistent with naming convention in R for functions reading in (e.g. <code class="highlighter-rouge">read.csv()</code> and <code class="highlighter-rouge">read.csv2()</code>) or writing (e.g. <code class="highlighter-rouge">write.csv()</code> and <code class="highlighter-rouge">write.csv2()</code>) csv files with different delimiter we decided to simply add <code class="highlighter-rouge">2</code> to <code class="highlighter-rouge">write_excel_csv()</code>. <figure class="highlight"> <pre><code class="language-r" data-lang="r"><span class="n">tmp</span> <span class="o">&lt;-</span> <span class="n">tempfile</span><span class="p">()</span> <span class="nf">on.exit</span><span class="p">(</span><span class="n">unlink</span><span class="p">(</span><span class="n">tmp</span><span class="p">))</span> <span class="n">readr</span><span class="o">::</span><span class="n">write_excel_csv2</span><span class="p">(</span><span class="n">mtcars</span><span class="p">,</span> <span class="n">tmp</span><span class="p">)</span></code></pre> </figure> To prove that it works, let’s read the first two lines and inspect the output. <figure class="highlight"> <pre><code class="language-r" data-lang="r"><span class="n">readr</span><span class="o">::</span><span class="n">read_lines</span><span class="p">(</span><span class="n">tmp</span><span class="p">,</span> <span class="n">n_max</span> <span class="o">=</span> <span class="m">2</span><span class="p">)</span></code></pre> </figure> <figure class="highlight"> <pre><code class="language-text" data-lang="text">## [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"</code></pre> </figure> <code class="highlighter-rouge">write_excel_csv2()</code> is already available for download from <code class="highlighter-rouge">readr</code> repository and should be available on CRAN with the next release. <figure class="highlight"> <pre><code class="language-r" data-lang="r"><span class="n">devtools</span><span class="o">::</span><span class="n">install_github</span><span class="p">(</span><span class="s2">"tidyverse/readr"</span><span class="p">)</span></code></pre> </figure> We hope you and your business team will find this addition useful.

Contact us!
Damian's Avatar
Damian Rodziewicz
Head of Sales
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
r
excel
excel alternatives
tutorials