<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/readxl-1-0-0/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://blog.rstudio.org/2017/04/19/readxl-1-0-0/"> RStudio Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>I’m pleased to announce that <a target="_blank" rel="nofollow" href="http://readxl.tidyverse.org/">readxl</a> 1.0.0 is available on CRAN. <a target="_blank" rel="nofollow" href="http://readxl.tidyverse.org/">readxl</a> makes it easy to bring tabular data out of Excel and into R, for modern <code>.xlsx</code> files and the legacy <code>.xls</code> format. <a target="_blank" rel="nofollow" href="http://readxl.tidyverse.org/">readxl</a> does not have any tricky external dependencies, such as Java or Perl, and is easy to install and use on Mac, Windows, and Linux.</p>
<p>You can install it with:</p>
<pre><code>install.packages(&quot;readxl&quot;)</code></pre>
<p>As well as fixing many bugs, this release:</p>
<ul>
<li>Allows you to target specific cells for reading, in a variety of ways</li>
<li>Adds two new column types: <code>&quot;logical&quot;</code> and <code>&quot;list&quot;</code>, for data of disparate type</li>
<li>Is more resilient to the wondrous diversity in spreadsheets, e.g., those written by 3rd party tools</li>
</ul>
<p>You can see a full list of changes in the <a target="_blank" rel="nofollow" href="http://readxl.tidyverse.org/news/index.html">release notes</a>. This is the first release maintained by Jenny Bryan.</p>
<div>
<h2>Specifying the data rectangle</h2>
<p>In an ideal world, data would live in a neat rectangle in the upper left corner of a spreadsheet. But spreadsheets often serve multiple purposes for users with different priorities. It is common to encounter several rows of notes above or below the data, for example. The new <code>range</code> argument provides a flexible interface for describing the data rectangle, including Excel-style ranges and row- or column-only ranges.</p>
<pre><code>library(readxl)
read_excel(
  readxl_example(&quot;deaths.xlsx&quot;),
  <span>range = &quot;arts!A5:F15&quot;</span>
)
#> # A tibble: 10 × 6
#>            Name Profession   Age `Has kids` `Date of birth`
#>                                  
#> 1   David Bowie   musician    69       TRUE      1947-01-08
#> 2 Carrie Fisher      actor    60       TRUE      1956-10-21
#> 3   Chuck Berry   musician    90       TRUE      1926-10-18
#> 4   Bill Paxton      actor    61       TRUE      1955-05-17
#> # ... with 6 more rows, and 1 more variables: `Date of death` 

read_excel(
  readxl_example(&quot;deaths.xlsx&quot;),
  sheet = &quot;other&quot;,
  <span>range = cell_rows(5:15)</span>
)
#> # A tibble: 10 × 6
#>           Name Profession   Age `Has kids` `Date of birth`
#>                                           
#> 1   Vera Rubin  scientist    88       TRUE      1928-07-23
#> 2  Mohamed Ali    athlete    74       TRUE      1942-01-17
#> 3 Morley Safer journalist    84       TRUE      1931-11-08
#> 4 Fidel Castro politician    90       TRUE      1926-08-13
#> # ... with 6 more rows, and 1 more variables: `Date of death`</code></pre>
<p>There is also a new argument <code>n_max</code> that limits the number of data rows read from the sheet. It is an example of <a target="_blank" rel="nofollow" href="http://readxl.tidyverse.org/">readxl</a>’s evolution towards a <a target="_blank" rel="nofollow" href="http://readr.tidyverse.org/">readr</a>-like interface. The <a target="_blank" rel="nofollow" href="http://readxl.tidyverse.org/articles/sheet-geometry.html">Sheet Geometry vignette</a> goes over all the options.</p>
</div>
<div>
<h2>Column typing</h2>
<p>The new ability to target cells for reading means that <a target="_blank" rel="nofollow" href="http://readxl.tidyverse.org/">readxl</a>’s automatic column typing will “just work”” for most sheets, most of the time. Above, the <code>Has kids</code> column is automatically detected as <code>logical</code>, which is a new column type for <a target="_blank" rel="nofollow" href="http://readxl.tidyverse.org/">readxl</a>.</p>
<p>You can still specify column type explicitly via <code>col_types</code>, which gets a couple new features. If you provide exactly one type, it is recycled to the necessary length. The new type <code>&quot;guess&quot;</code> can be mixed with explicit types to specify some types, while leaving others to be guessed.</p>
<pre><code>read_excel(
  readxl_example(&quot;deaths.xlsx&quot;),
  range = &quot;arts!A5:C15&quot;,
  <span>col_types = c(&quot;guess&quot;, &quot;skip&quot;, &quot;numeric&quot;)</span>
)
#> # A tibble: 10 × 2
#>            Name   Age
#>            
#> 1   David Bowie    69
#> 2 Carrie Fisher    60
#> 3   Chuck Berry    90
#> 4   Bill Paxton    61
#> # ... with 6 more rows</code></pre>
<p>The new argument <code>guess_max</code> limits the rows used for type guessing. Leading and trailing whitespace is trimmed when the new <code>trim_ws</code> argument is <code>TRUE</code>, which is the default. Finally, thanks to <a target="_blank" rel="nofollow" href="https://github.com/jmarshallnz">Jonathan Marshall</a>, multiple <code>na</code> values are accepted. The <a target="_blank" rel="nofollow" href="http://readxl.tidyverse.org/articles/cell-and-column-types.html">Cell and Column Types vignette</a> has more detail.</p>
<div>
<h3><code>&quot;list&quot;</code> columns</h3>
<p>Thanks to <a target="_blank" rel="nofollow" href="https://github.com/gergness">Greg Freedman Ellis</a> we now have a <code>&quot;list&quot;</code> column type. This is useful if you want to bring truly disparate data into R without the coercion required by atomic vector types.</p>
<pre><code>(df &lt;- read_excel(
  readxl_example(&quot;clippy.xlsx&quot;),
  <span>col_types = c(&quot;text&quot;, &quot;list&quot;)</span>
))
#> # A tibble: 4 × 2
#>                   name      value
#>                  &lt;chr>     &lt;list>
#> 1                 Name  &lt;chr [1]>
#> 2              Species  &lt;chr [1]>
#> 3 Approx date of death &lt;dttm [1]>
#> 4      Weight in grams  &lt;dbl [1]>

tibble::deframe(df)
#> $Name
#> [1] &quot;Clippy&quot;
#> 
#> $Species
#> [1] &quot;paperclip&quot;
#> 
#> $`Approx date of death`
#> [1] &quot;2007-01-01 UTC&quot;
#> 
#> $`Weight in grams`
#> [1] 0.9</code></pre>
</div>
</div>
<div>
<h2>Everything else</h2>
<p>To learn more, read the <a target="_blank" rel="nofollow" href="http://readxl.tidyverse.org/articles/index.html">vignettes and articles</a> or <a target="_blank" rel="nofollow" href="http://readxl.tidyverse.org/news/index.html">release notes</a>. Highlights include:</p>
<ul>
<li>General rationalization of sheet geometry, including detection and treatment of empty rows and columns.</li>
<li>Improved behavior and messaging around coercion and mismatched cell and column types.</li>
<li>Improved handling of datetimes with respect to 3rd party software, rounding, and the <a target="_blank" rel="nofollow" href="https://support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-leap-year">Lotus 1-2-3 leap year bug</a>.</li>
<li><code>read_xls()</code> and <code>read_xlsx()</code> are now exposed, so that files without an <code>.xls</code> or <code>.xlsx</code> extension can be read. Thanks <a target="_blank" rel="nofollow" href="https://github.com/jirkalewandowski">Jirka Lewandowski</a>!</li>
<li><a target="_blank" rel="nofollow" href="http://readxl.tidyverse.org/articles/articles/readxl-workflows.html">readxl Workflows</a> showcases patterns that reduce tedium and increase reproducibility when raw data arrives in a spreadsheet.</li>
</ul>
</div>
<p> <a target="_blank" rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/rstudioblog.wordpress.com/4371/"><img alt="" src="http://feeds.wordpress.com/1.0/comments/rstudioblog.wordpress.com/4371/" border="0"></a> <img alt="" src="https://i1.wp.com/pixel.wp.com/b.gif?resize=1%2C1&ssl=1" border="0" data-recalc-dims="1"></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="https://blog.rstudio.org/2017/04/19/readxl-1-0-0/"> RStudio 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 title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/search/data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/search/map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/search/animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/search/sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/search/SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/search/git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/search/hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/search/PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/search/time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/search/trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/rXL63IR8W5s"></a></div>