Extract data from specific cells in multiple excel files - R

Question

I need to extract data (date, time) from two cells (E6:E7) from multiple excel files and then store that data into a data frame (with separate columns for date and time).

See below for the code I've tried.

setwd("C:/Users/tsamuels/Desktop/PDRA - Collins/growth_rate_folder")
file.list <- list.files(path=".", pattern="\\d.xlsx$")
df.list <- lapply(file.list, read_excel(path=".", sheet = 1, range = "E6:E7", col_names = FALSE,
                                        col_types = NULL))

Any help appreciated!

score 0 · Accepted Answer · answered Feb 09 '20 at 22:07

0

I am not using read_excel and you did not provide a MRE so I could not test it, but you could try this.

df.list <- lapply(file.list, read_excel, sheet=1, range="E6:E7", col_names=FALSE, col_types = NULL))

answered Feb 09 '20 at 22:07

dario

6,162
2
10
25

That partially worked! I've now got a tibble for the data from each excel file, how would I get all of that data into a single tibble? – Toby Samuels Feb 09 '20 at 22:37
[this should help](https://stackoverflow.com/questions/8091303/simultaneously-merge-multiple-data-frames-in-a-list) or [this (row bind)](https://www.rdocumentation.org/packages/data.table/versions/1.12.8/topics/rbindlist) – dario Feb 09 '20 at 22:44

score 0 · Answer 2 · answered Feb 09 '20 at 22:37

Making some assumptions about your files and how you want your output:

files <- list.files('.', pattern = '*.xlsx', recursive = TRUE)
df1 <- lapply(files, 
              function(x) {readxl::read_xlsx(x, sheet = 1, range = "E6:E7", col_names = FALSE)}) %>% 
  bind_cols %>% 
  t %>% 
  data.frame

colnames(df1) <- c('date', 'time')
rownames(df1) <- files

Here bind_cols (or do you need bind_rows?) will take your list of dataframes and combine them

Extract data from specific cells in multiple excel files - R

2 Answers2