CODEWITHSUNDEEP
r
Adi.sr
Adi.sr

Reputation: 161

Import all Sheets of an Excel in a folder, without mentioning Excel Name

I need to import all sheets with their respective names with mentioning only folder path.

filename <-"D:/Dash/"
sheets <- openxlsx::getSheetNames(filename)
 SheetList <- lapply(sheets,openxlsx::read.xlsx,xlsxFile=filename)
 names(SheetList) <- sheets

But it shows error as the file name is not mentioned.

What I need is Importing those sheets from Excel in the folder with only folder path mentioned.

Upvotes: 1

Views: 111

Answers (1)

niko
niko

Reputation: 5281

As indicated by @MichaelChirico, you can do that using list.files

path <- "D:/Dash/"
excelFiles <- list.files(path = path, pattern = '.*\\.xlsx', full.names = TRUE) # or xls, xlsm, xlsb, etc.

Now extracting the data

method 1

sheetNamesList <- lapply(seq_along(excelFiles), 
                         function (k) c(file = excelFiles[k], 
                                        sheet = openxlsx::getSheetNames(excelFiles[k]))
                         )
sheetData <- lapply(seq_along(sheetNamesList), 
                    function (k) openxlsx::read.xlsx(xlsxFile = sheetNamesList[[k]]['file'], 
                                                     sheet = sheetNamesList[[k]]['sheet'])
                    )

method 2

sheetData <- lapply( seq_along(excelFiles), 
                     function (k) {
                       tmpSheets <- openxlsx::getSheetNames(excelFiles[k])
                       tmpData <- lapply(seq_along(tmpSheets), function (n) openxlsx::read.xlsx(excelFiles[k], sheet = tmpSheets[n]))
                       tmpData
                     })

Upvotes: 1

Related Questions