user2504063 user2504063 - 1 year ago 79
R Question

How to read multiple xlsx file in R using loop with specific rows and columns

I have to read multiple xlsx file with random names into single dataframe. Structure of each file is same. I have to import specific columns only.

I tried this:

dat <- read.xlsx("FILE.xlsx", sheetIndex=1,
sheetName=NULL, startRow=5,

But this is for only one file at a time and I couldn't specify my particular columns.
I even tried :


but after that loop isn't working. How to do it? Thanks in advance.

Answer Source

I would read each sheet to a list:

Get file names:

f = list.files("./")

Read files:

dat = lapply(f, function(i){
    x = read.xlsx(i, sheetIndex=1, sheetName=NULL, startRow=5,
        endRow=NULL,, header=T)
    # Get the columns you want, e.g. 1, 3, 5
    x = x[, c(1, 3, 5)]
    # You may want to add a column to say which file they're from
    x$file = i
    # Return your data

You can then access the items in your list with:


Or do the same task to them with:

lapply(dat, colmeans)

Turn them into a data frame (where your file column now becomes useful):

dat ="", dat)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download