<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/going-from-a-human-readable-excel-file-to-a-machine-readable-csv-with-tidyxl/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.brodrigues.co/blog/2018-09-11-human_to_machine/"> Econometrics and Free Software</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<div>
<p><a rel="nofollow" href="https://www.youtube.com/watch?v=m-PE3OkJ8XE" target="_blank"><br>
<img src="https://i2.wp.com/www.brodrigues.co/blog/2018-09-11-human_to_machine/img/1a9.gif?w=456" data-recalc-dims="1"></a></p>
</div>
<p>I won’t write a very long introduction; we all know that Excel is ubiquitous in business, and that<br>
it has a lot of very nice features, especially for business practitioners that do not know any<br>
programming. However, when people use Excel for purposes it was not designed for, it can be a<br>
hassle. Often, people use Excel as a reporting tool, which it is not; they create very elaborated<br>
and complicated spreadsheets that are human readable, but impossible to import within any other tool.</p>
<p>In this blog post (which will probably be part of a series), I show you how you can go from this:</p>
<p><img src="https://i2.wp.com/www.brodrigues.co/blog/2018-09-11-human_to_machine/img/time_use.png?w=456" data-recalc-dims="1"></p>
<p>to this:</p>
<p><img src="https://i2.wp.com/www.brodrigues.co/blog/2018-09-11-human_to_machine/img/time_use2.png?w=456" data-recalc-dims="1"></p>
<p>You can find the data I will use <a rel="nofollow" href="http://statistiques.public.lu/stat/ReportFolders/ReportFolder.aspx?IF_Language=eng&MainTheme=3&FldrName=1&RFPath=14306" target="_blank">here</a>. Click on the “Time use” folder and you can download the workbook.</p>
<p>The Excel workbook contains several sheets (in French and English) of the amount of time Luxembourguish<br>
citizens spend from Monday to Sunday. For example, on average, people that are in employment spend<br>
almost 8 hours sleeping during the week days, and 8:45 hours on Saturday.</p>
<p>As you can see from the screenshot, each sheet contains several tables that have lots of headers<br>
and these tables are next to one another. Trying to import these sheets with good ol’ <code>readxl::read_excel()</code><br>
produces a monster.</p>
<p>This is where <code>{tidyxl}</code> comes into play. Let’s import the workbook with <code>{tidyxl}</code>:</p>
<pre><code>library(tidyverse)
library(tidyxl)

time_use_xl &lt;- xlsx_cells(&quot;time-use.xlsx&quot;)</code></pre>
<p>Let’s see what happened:</p>
<pre><code>head(time_use_xl)</code></pre>
<pre><code>## # A tibble: 6 x 21
##   sheet address   row   col is_blank data_type error logical numeric
##   </code></pre>