Read multiple Excel sheets and append to one dataframe


I have an Excel document with multiple sheets that I want to read and append to a single data frame. I want to take the first row of each sheet and use it as the column names. I tried this code:

sheets <- data.frame()
sheetlist <- excel_sheets(path="myfile.xlsx")

for(i in 1:length(sheetlist)){
  d <- read.xlsx("myfile.xlsx", sheetName = sheetlist[i])
  colnames(d) <- d[1,]
  d <- d[-1,]
  sheets[i] <- rbind(d)

But not surprisingly, it didn't work. Gave me an empty dataframe with some numbers as my column names. Anyone know of a way to do this?

Thanks so much!

1 Like

Does this give your intended result?


sheetlist = excel_sheets('myfile.xlsx')

myfun = function(i) {
  d = read_xlsx('myfile.xlsx', sheet = i)
  colnames(d) = d[1,]
  d = d[-1,]

sheets = map(sheetlist, myfun) %>% bind_rows()
1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.