6

I have an excel file which contains 400 sheets. How can I load this excel file to R using read.xls function? Please provide sample code for this.

Lasse V. Karlsen
  • 380,855
  • 102
  • 628
  • 825
akash
  • 123
  • 2
  • 2
  • 4
  • 5
    Hi and welcome to Stack Overflow. Please consult the [ask] and [faq] pages on how to write good questions, and please don't post duplicate questions, instead you should edit the question to provide more information. This question here, however, is in the risk zone of being closed as "not a real question" or "not constructive" because of its poor quality. You should try to provide more information about what you tried, ask for pointers, and get rid of the text at the end that basically reads like "hey, can someone do my work? kthxbaizomglol!" to most people. – Lasse V. Karlsen Aug 01 '11 at 09:06
  • 2
    As Lasse said : Please show us what you tried. This ain't "Hire a coder for Free" – Joris Meys Aug 01 '11 at 09:46
  • 7
    As said on Biostar: It is rather rude to crosspost your question to different sites multiple times and hope you get an answer somewhere. (see http://biostar.stackexchange.com/questions/10771/how-to-read-multiple-excel-sheets-in-r-programming and http://biostar.stackexchange.com/questions/10773/how-to-read-multiple-excel-sheets-in-r-programming-closed ). You ain't doing yourself a favor here... – Joris Meys Aug 01 '11 at 10:29

1 Answers1

12

I'm just assuming you want it as all one data.frame() and that all the sheets contain the same data.

library(xlsReadWrite) 
sheets <- c("Sheet 1","Sheet 2", "Sheet 3")

df <- data.frame()

for (x in 1:400) 
df <- rbind(df, read.xls("filename.xls", sheet=sheets[x]))
}

If each sheet is it's own unique data.frame() you'll probably want to put them in a list. Otherwise you can use assign() if you want them as objects in the environment.

sheet_list <- list()
for(x in 1:400) {
sheet_list[[x]] <- read.xls("filename.xls", sheet=sheets[x])
}

Or, without a for loop: 

sheet_list <- lapply(sheets, function(x) read.xls("filename.xls",sheets=x))
Konrad Rudolph
  • 530,221
  • 131
  • 937
  • 1,214
Brandon Bertelsen
  • 43,807
  • 34
  • 160
  • 255
  • 4
    Also note the `sheetCount` function in the gdata package which gives the number of sheets in a spreadsheet and the `sheetNames` function which gives all their names. – G. Grothendieck Aug 01 '11 at 14:43
  • 2
    This `rbind` thing is so bad. 400 times call for `rbind`? For the `list` way I give +1, and `do.call(rbind, sheets)` will create `data.frame`. – Marek Aug 05 '11 at 07:19
  • 2
    Marek, I hear ya - but sometimes we don't care about the difference between 1 second and 10 seconds. – Brandon Bertelsen Aug 06 '11 at 14:30
  • Where is the i coming from in the list answer above? Does not work for me because i is not found – Z_D Apr 06 '15 at 20:17