Load specific columns from all sheets in an Excel file in R

Question

I am attempting to use the methodology document here to load all the sheets in an Excel document: Read all worksheets in an Excel workbook into an R list with data.frames

The issue, however, is that I only want to load specific columns from those sheets, rather than loading everything and subsetting the columns I want.

I have tried the following:

library(readxl)
read_excel_allsheets<-function(filename){
    sheets<-readxl::excel_sheets(filename)
    x<-lapply(sheets, function(X) readxl::read_excel(filename, 
                                                     sheet=X, 
                                                     range = cell_cols(c("A", "B", "C"))))
    names(x)<-sheets
    x
}
dat<-read_excel_allsheets(myexcelsheet.xlsx)

The script works, but it is still reading all the columns. The named columns are present and in the same position in every sheet. Any thoughts? Thanks!

To use `cell_cols` you may have to actually load `cellranger` — Jake Kaupp, Jan 23 '18 at 19:44

score 1 · Accepted Answer · answered Jan 23 '18 at 21:26

I think you want to set the col_type parameter to "blank" for those columns. check out the example, the first loads 12 cols and the second 2. You will have to change the file path for the code to work.

library( xlsx )

write.xlsx( mtcars , "C:/Users/matthewr/Desktop/New folder/mtcars.xlsx") 

library( readxl )
a <- read_excel(  "C:/Users/matthewr/Desktop/New folder/mtcars.xlsx" , sheet= 1 ) 
b <- read_excel(  "C:/Users/matthewr/Desktop/New folder/mtcars.xlsx" , sheet=1 , col_type= c( "text","numeric", rep("blank", 10) ) )

length( names( a ))
length( names( b ))

Load specific columns from all sheets in an Excel file in R

1 Answers1