CODEWITHSUNDEEP
rimportxls
mp_ylee
mp_ylee

Reputation: 41

How can I import multiple .xls files (with multiple sheets each) into R version 3.5.3?

I have 27 xls files to import into R, and each has multiple sheets in the workbook. I am trying to upload all files at one time, with only the first sheet in each file being imported into its own data frame(the other sheets do not need to be imported at this time).

I have seen some create a list of the files and then use the package readxl, however I am using the newest version of R (3.5.3) and it is not compatible as far as I can tell.

I hope to end up with 27 individual data frames, that I can then add a column to that identifies the specific data frame, so they can all be combined into one data frame to work with.

Upvotes: 0

Views: 114

Answers (1)

Patrick
Patrick

Reputation: 168

A list of files and readxl are working quite fine. I have created 3 excel files with

Mappe1.xlsx - Tabelle1 - A1:A4 - 1, 2, 3, 4

Mappe2.xlsx - Tabelle1 - A1:A4 - 4, 2, 3, 4

Mappe3.xlsx - Tabelle1 - A1:A4 - 4, 2, 3, 4

If you use the code

library(readxl)
library(tidyverse)

# define the names of the excel files 
excelNames <- paste0('Mappe', 1:3, '.xlsx')

lapply(1:length(excelNames), function(i) {

# get current ID and rid of the file extension   
currentID <- str_split(excelNames[i], '.xlsx', simplify = TRUE)[1]

# read excel file and add column with id
read_excel(
 excelNames[i],
 sheet = 'Tabelle1',
 col_names = FALSE,
 range = cell_limits(c(1, 1), c(4, 1))) %>%
 mutate(ID = currentID)
}) %>% 

# bind all results into one dataframe
bind_rows()

you should get

# A tibble: 12 x 2
    ...1 ID    
   <dbl> <chr> 
 1     1 Mappe1
 2     2 Mappe1
 3     3 Mappe1
 4     4 Mappe1
 5     4 Mappe2
 6     2 Mappe2
 7     3 Mappe2
 8     4 Mappe2
 9     4 Mappe3
10     2 Mappe3
11     3 Mappe3
12     4 Mappe3

Upvotes: 1

Related Questions