how to read multipleexcel files with all sheets at once.Column names are same in each sheet.?

General
1

i am new to R studio. i have folder with 5 excel files. and in each excel file has 3 sheets with same column names almost 13 columns and 1405 rows in each sheet.
i want to combine it in single data frame so that i can call my required column according to my need.
i am waiting for your reply.

2

Hi,

You might try the answer here: Reading multiple xlsx files each with multiple sheets - purrr to help you get started. Feel free to come back if you run into problems.

1 Like
3

This is how to do it using purrr

library(tidyverse)
library(readxl)

list_of_files <- list.files(path = "path/to/your/files",
                            pattern = "\\.xlsx$",
                            full.names = TRUE)
list_of_files %>%
    map_dfr(~{.x %>% 
            excel_sheets() %>% 
            map_dfr(read_excel, path = .x)})

Also, if you want to keep track of the source of the data you can use the .id parameter

list_of_files %>%
    set_names() %>%
    map_dfr(~{.x %>% 
            excel_sheets() %>% 
            set_names() %>% 
            map_dfr(read_excel, path = .x, .id = "sheet")}, .id = "workbook")
2 Likes
4

Thanks it worked. but how would i know about which column belongs to which sheet. because i have to access each/some column with its name from respective sheet. and column names are also same in each sheet.

5

Sorry but I don't understand your question, you have said that al sheets have the same column names, so what you're saying makes no sense to me, could you please clarify?, ideally, could you please prepare a reproducible example (reprex) illustrating your issue? Please have a look at this guide, to see how to create one:

6

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