Read all worksheets in an Excel workbook into an R list with data frames

Question

I understand that XLConnect can be used to read an Excel worksheet into R  For example  this would read the first worksheet in a workbook called test xls into R   library XLConnect  readWorksheetFromFile  test xls   sheet   1    I have an Excel Workbook with multiple worksheets    How can all worksheets in a workbook be imported into a list in R where each element of the list is a data frame for a given sheet  and where the name of each element corresponds to the name of the worksheet in Excel

User · Answer

You can load the work book and then use lapply  getSheets and readWorksheet and do something like this   wb mtcars  lt - loadWorkbook system file  demoFiles mtcars xlsx                              package    XLConnect    sheet names  lt - getSheets wb mtcars  names sheet names   lt - sheet names  sheet list  lt - lapply sheet names  function  sheet       readWorksheet object wb mtcars   sheet

User · Answer

I tried the above and had issues with the amount of data that my 20MB Excel I needed to convert consisted of  therefore the above did not work for me   After more research I stumbled upon openxlsx and this one finally did the trick  and fast  Importing a big xlsx file into R   https   cran r-project org web packages openxlsx openxlsx pdf

User · Answer

Adding to Paul s answer  The sheets can also be concatenated using something like this   data   path   gt    excel sheets     gt    set names     gt    map df   read excel path   path  sheet    x    id    Sheet     Libraries needed   if  require pacman  install packages  pacman   pacman  p load  tidyverse   readxl   purrr

User · Answer

From official readxl  tidyverse  documentation  changing first line    path  lt -  data datasets xlsx   path   gt      excel sheets     gt      set names     gt      map read excel  path   path    Details at  http   readxl tidyverse org articles articles readxl-workflows html iterate-over-multiple-worksheets-in-a-workbook

User · Answer

Updated answer using readxl  22nd June 2015   Since posting this question the readxl package has been released  It supports both xls and xlsx format  Importantly  in contrast to other excel import packages  it works on Windows  Mac  and Linux without requiring installation of additional software   So a function for importing all sheets in an Excel workbook would be   library readxl      read excel allsheets  lt - function filename  tibble   FALSE          I prefer straight data frames       but if you like tidyverse tibbles  the default with read excel        then just pass tibble   TRUE     sheets  lt - readxl  excel sheets filename      x  lt - lapply sheets  function X  readxl  read excel filename  sheet   X       if  tibble  x  lt - lapply x  as data frame      names x   lt - sheets     x     This could be called with   mysheets  lt - read excel allsheets  foo xls     Old Answer  Building on the answer provided by  mnel  here is a simple function that takes an Excel file as an argument and returns each sheet as a data frame in a named list   library XLConnect   importWorksheets  lt - function filename          filename  name of Excel file     workbook  lt - loadWorkbook filename      sheet names  lt - getSheets workbook      names sheet names   lt - sheet names     sheet list  lt - lapply sheet names  function  sheet           readWorksheet object workbook   sheet        Thus  it could be called with   importWorksheets  test xls

User · Answer

I stumbled across this old question and I think the easiest approach is still missing   You can use rio to import all excel sheets with just one line of code   library rio  data list  lt - import list  test xls     If you re a fan of the tidyverse  you can easily import them as tibbles by adding the setclass argument to the function call    data list  lt - import list  test xls   setclass    tbl     Suppose they have the same format  you could easily row bind them by setting the rbind argument to TRUE    data list  lt - import list  test xls   setclass    tbl   rbind   TRUE

User · Answer

excel link will do the job   I actually found it easier to use compared to XLConnect  not that either package is that difficult to use   Learning curve for both was about 5 minutes   As an aside  you can easily find all R packages that mention the word  Excel  by browsing to http   cran r-project org web packages available packages by name html

User · Answer

Note that most of XLConnect s functions are already vectorized  This means that you can read in all worksheets with one function call without having to do explicit vectorization   require XLConnect  wb  lt - loadWorkbook system file  demoFiles mtcars xlsx   package    XLConnect    lst   readWorksheet wb  sheet   getSheets wb     With XLConnect 0 2-0 lst will already be a named list

User · Answer

Since this is the number one hit to the question  Read multi sheet excel to list   here is the openxlsx solution   filename  lt - myFilePath   sheets  lt - openxlsx  getSheetNames filename  SheetList  lt - lapply sheets openxlsx  read xlsx xlsxFile filename  names SheetList   lt - sheets

User · Answer

To read multiple sheets from a workbook  use readxl package as follows  library readxl  library dplyr   final dataFrame  lt - bind rows path to workbook   gt                                 excel sheets     gt                                 set names     gt                                 map read excel  path   path to workbook    Here  bind rows  dplyr  will put all data rows from all sheets into one data frame  and path to workbook is the location of your data   quot dir of the data workbook quot

[r] Read all worksheets in an Excel workbook into an R list with data.frames

Examples related to r

Examples related to excel

Examples related to xlconnect