readxl error on iteration for importing many sheets into .csv files

Hi #rstats and #tidyverse folks!

I was hoping someone could help me with the error I am seeing when trying to get the functions from the excellent examples on readxl?

I have multiple sheets in an .xlsx file I want to read into data frames and write (a few) of them as .csv files.

Unfortunately I keep seeing this error:

    library(tidyverse)
    library(readxl)
    library(magrittr)
    #> 
    #> Attaching package: 'magrittr'
    #> The following object is masked from 'package:purrr':
    #> 
    #>     set_names
    #> The following object is masked from 'package:tidyr':
    #> 
    #>     extract
    read_then_csv <- function(sheet, path) {
      pathbase <- path %>%
        basename() %>%
        tools::file_path_sans_ext()
      path %>%
        read_excel(sheet = sheet) %>% 
        write_csv(paste0(pathbase, "-", sheet, ".csv"))
    }
    path <- readxl_example("datasets.xlsx")
    path %>%
      excel_sheets() %>%
      set_names() %>% 
      map(read_then_csv, path = path)
    #> Error in set_names(.): 1 argument passed to 'names<-' which requires 2

<sup>Created on 2018-11-06 by the [reprex package](https://reprex.tidyverse.org) (v0.2.1)</sup>

    devtools::session_info()
    #> - Session info ----------------------------------------------------------
    #>  setting  value                       
    #>  version  R version 3.5.1 (2018-07-02)
    #>  os       Windows Server 2012 R2 x64  
    #>  system   x86_64, mingw32             
    #>  ui       RTerm                       
    #>  language (EN)                        
    #>  collate  English_United States.1252  
    #>  ctype    English_United States.1252  
    #>  tz       America/Los_Angeles         
    #>  date     2018-11-06                  
    #> 
    #> - Packages --------------------------------------------------------------
    #>  package     * version date       lib source        
    #>  assertthat    0.2.0   2017-04-11 [1] CRAN (R 3.5.1)
    #>  backports     1.1.2   2017-12-13 [1] CRAN (R 3.5.0)
    #>  base64enc     0.1-3   2015-07-28 [1] CRAN (R 3.5.0)
    #>  bindr         0.1.1   2018-03-13 [1] CRAN (R 3.5.1)
    #>  bindrcpp      0.2.2   2018-03-29 [1] CRAN (R 3.5.1)
    #>  broom         0.5.0   2018-07-17 [1] CRAN (R 3.5.1)
    #>  callr         3.0.0   2018-08-24 [1] CRAN (R 3.5.1)
    #>  cellranger    1.1.0   2016-07-27 [1] CRAN (R 3.5.1)
    #>  cli           1.0.1   2018-09-25 [1] CRAN (R 3.5.1)
    #>  colorspace    1.3-2   2016-12-14 [1] CRAN (R 3.5.1)
    #>  crayon        1.3.4   2017-09-16 [1] CRAN (R 3.5.1)
    #>  desc          1.2.0   2018-05-01 [1] CRAN (R 3.5.1)
    #>  devtools      2.0.1   2018-10-26 [1] CRAN (R 3.5.1)
    #>  digest        0.6.18  2018-10-10 [1] CRAN (R 3.5.1)
    #>  dplyr       * 0.7.7   2018-10-16 [1] CRAN (R 3.5.1)
    #>  evaluate      0.12    2018-10-09 [1] CRAN (R 3.5.1)
    #>  forcats     * 0.3.0   2018-02-19 [1] CRAN (R 3.5.1)
    #>  fs            1.2.6   2018-08-23 [1] CRAN (R 3.5.1)
    #>  ggplot2     * 3.1.0   2018-10-25 [1] CRAN (R 3.5.1)
    #>  glue          1.3.0   2018-07-17 [1] CRAN (R 3.5.1)
    #>  gtable        0.2.0   2016-02-26 [1] CRAN (R 3.5.1)
    #>  haven         1.1.2   2018-06-27 [1] CRAN (R 3.5.1)
    #>  hms           0.4.2   2018-03-10 [1] CRAN (R 3.5.1)
    #>  htmltools     0.3.6   2017-04-28 [1] CRAN (R 3.5.1)
    #>  httr          1.3.1   2017-08-20 [1] CRAN (R 3.5.1)
    #>  jsonlite      1.5     2017-06-01 [1] CRAN (R 3.5.1)
    #>  knitr         1.20    2018-02-20 [1] CRAN (R 3.5.1)
    #>  lattice       0.20-35 2017-03-25 [2] CRAN (R 3.5.1)
    #>  lazyeval      0.2.1   2017-10-29 [1] CRAN (R 3.5.1)
    #>  lubridate     1.7.4   2018-04-11 [1] CRAN (R 3.5.1)
    #>  magrittr    * 1.5     2014-11-22 [1] CRAN (R 3.5.1)
    #>  memoise       1.1.0   2017-04-21 [1] CRAN (R 3.5.1)
    #>  modelr        0.1.2   2018-05-11 [1] CRAN (R 3.5.1)
    #>  munsell       0.5.0   2018-06-12 [1] CRAN (R 3.5.1)
    #>  nlme          3.1-137 2018-04-07 [2] CRAN (R 3.5.1)
    #>  pillar        1.3.0   2018-07-14 [1] CRAN (R 3.5.1)
    #>  pkgbuild      1.0.2   2018-10-16 [1] CRAN (R 3.5.1)
    #>  pkgconfig     2.0.2   2018-08-16 [1] CRAN (R 3.5.1)
    #>  pkgload       1.0.2   2018-10-29 [1] CRAN (R 3.5.1)
    #>  plyr          1.8.4   2016-06-08 [1] CRAN (R 3.5.1)
    #>  prettyunits   1.0.2   2015-07-13 [1] CRAN (R 3.5.1)
    #>  processx      3.2.0   2018-08-16 [1] CRAN (R 3.5.1)
    #>  ps            1.2.0   2018-10-16 [1] CRAN (R 3.5.1)
    #>  purrr       * 0.2.5   2018-05-29 [1] CRAN (R 3.5.1)
    #>  R6            2.3.0   2018-10-04 [1] CRAN (R 3.5.1)
    #>  Rcpp          0.12.19 2018-10-01 [1] CRAN (R 3.5.1)
    #>  readr       * 1.1.1   2017-05-16 [1] CRAN (R 3.5.1)
    #>  readxl      * 1.1.0   2018-04-20 [1] CRAN (R 3.5.1)
    #>  remotes       2.0.2   2018-10-30 [1] CRAN (R 3.5.1)
    #>  rlang         0.3.0.1 2018-10-25 [1] CRAN (R 3.5.1)
    #>  rmarkdown     1.10    2018-06-11 [1] CRAN (R 3.5.1)
    #>  rprojroot     1.3-2   2018-01-03 [1] CRAN (R 3.5.1)
    #>  rvest         0.3.2   2016-06-17 [1] CRAN (R 3.5.1)
    #>  scales        1.0.0   2018-08-09 [1] CRAN (R 3.5.1)
    #>  sessioninfo   1.1.1   2018-11-05 [1] CRAN (R 3.5.1)
    #>  stringi       1.2.4   2018-07-20 [1] CRAN (R 3.5.1)
    #>  stringr     * 1.3.1   2018-05-10 [1] CRAN (R 3.5.1)
    #>  tibble      * 1.4.2   2018-01-22 [1] CRAN (R 3.5.1)
    #>  tidyr       * 0.8.2   2018-10-28 [1] CRAN (R 3.5.1)
    #>  tidyselect    0.2.5   2018-10-11 [1] CRAN (R 3.5.1)
    #>  tidyverse   * 1.2.1   2017-11-14 [1] CRAN (R 3.5.1)
    #>  usethis       1.4.0   2018-08-14 [1] CRAN (R 3.5.1)
    #>  withr         2.1.2   2018-03-15 [1] CRAN (R 3.5.1)
    #>  xml2          1.2.0   2018-01-24 [1] CRAN (R 3.5.1)
    #>  yaml          2.2.0   2018-07-25 [1] CRAN (R 3.5.1)
    #> 
    #> [1] C:/Users/MFrigaard/Documents/R/win-library/3.5
    #> [2] C:/Program Files/R/R-3.5.1/library

Thank you!

1 Like

Looks like magrittr masked set_names() from package purrr, which led to the error.

The set_names() function from magrittr needs you to supply the names, where you set_names() from purrr will name the vector with itself if the names are missing.

You can refer directly to function from the desired package with ::, like purrr::set_names.

path %>%
     excel_sheets() %>%
     purrr::set_names() %>% 
     map(read_then_csv, path = path)
4 Likes

One more point: you don't need to load magrittr if you only want the pipe (%>%) — tidyverse supplies that for you (ultimately pulled from magrittr, but without pulling all the other magrittr functions along with it). So your code can just start with:

library(tidyverse)
library(readxl)

and then you won't have to worry about other functions in magrittr whose names overlap with functions in some tidyverse packages.

4 Likes

:+1:

I love when the answer is something simple! Thank you!

@jcblum Thank you! I tend to use a few other magrittr functions, but I will keep this in mind (and call explicitly when loading conflicts).

In that case, it might make sense to see if the name masking works out more in line with your typical use if you load magrittr first — then tidyverse functions will mask magrittr functions (and not for nothing, tidyverse has a prettier display of what's getting masked :grin: — too bad the colors don't show up here!)

library(tidyverse)
#> ── Attaching packages ────────────────────────────────────────────────────────────────────────────────────────────── tidyverse 1.2.1 ──
#> ✔ ggplot2 3.1.0     ✔ purrr   0.2.5
#> ✔ tibble  1.4.2     ✔ dplyr   0.7.7
#> ✔ tidyr   0.8.2     ✔ stringr 1.3.1
#> ✔ readr   1.1.1     ✔ forcats 0.3.0
#> ── Conflicts ───────────────────────────────────────────────────────────────────────────────────────────────── tidyverse_conflicts() ──
#> ✖ dplyr::filter() masks stats::filter()
#> ✖ dplyr::lag()    masks stats::lag()
library(magrittr)
#> 
#> Attaching package: 'magrittr'
#> The following object is masked from 'package:purrr':
#> 
#>     set_names
#> The following object is masked from 'package:tidyr':
#> 
#>     extract

Created on 2018-11-06 by the reprex package (v0.2.1)

library(magrittr)
library(tidyverse)
#> ── Attaching packages ────────────────────────────────────────────────────────────────────────────────────────────── tidyverse 1.2.1 ──
#> ✔ ggplot2 3.1.0     ✔ purrr   0.2.5
#> ✔ tibble  1.4.2     ✔ dplyr   0.7.7
#> ✔ tidyr   0.8.2     ✔ stringr 1.3.1
#> ✔ readr   1.1.1     ✔ forcats 0.3.0
#> ── Conflicts ───────────────────────────────────────────────────────────────────────────────────────────────── tidyverse_conflicts() ──
#> ✖ tidyr::extract()   masks magrittr::extract()
#> ✖ dplyr::filter()    masks stats::filter()
#> ✖ dplyr::lag()       masks stats::lag()
#> ✖ purrr::set_names() masks magrittr::set_names()

Created on 2018-11-06 by the reprex package (v0.2.1)

2 Likes

@jcblum oh the colors are definitely not for nothing! Prettier code is more fun to read :slight_smile:

Thank you!

1 Like