Import multiple excel tables at the same time

Question

I want to import 27 Excel tables into R without having to type 27 times the import command already, that the table names go from tab101 to tab127. I tried that way, but you made a mistake:

library(readxl)
n = c()
for (i in 1:27){ # fazendo um vetor com os nomes dos arquivos
a = "tab"
a = paste(a, 100+i, sep = "")
a = paste(a, ".xls", sep = "")
n[i] = a
}
t =lapply(n, read_excel) #aplicando read_excel para importar cada arquivo e  
#juntando tudo em uma lista

Until then I thought I was successful. The list of 27 elements is created, but when I ask to display the first element of the list the following appears:

t[1]  
[[1]]
Error in gsub(ansi_regex, "", string, perl = TRUE) : 
 input string 1 is invalid UTF-8  

If I call the str(t) it shows that the data were imported correctly. I am not knowing how to access each element of the list. But the real focus is to be able to import all tables at once, you don’t necessarily have to create a list with them. I tried to do just with the for, putting the read_table(a) inside, but doing nothing.

Answer 1

First, let’s create a vector with the names of all your files .xls:

arquivos <- list.files()
arquivos <- arquivos[grep(".xls", arquivos)]

The first line above lists all the files in your working directory. How can there be files with extension other than .xls, the second line replaces the contents of the original vector with the names only of the files that have the string .xls somewhere in your name.

Then just rotate one for using the command assign. This command will cause objects to be created inside the R. These objects will have the name and content of your files .xls:

library(readxl)
for (j in arquivos){
  assign(j, read_excel(j))
}

For example, if the files are called tab101.xls, ..., tab127.xls, the objects within the R will be created with the names tab101.xls, ..., tab127.xls.

If you do not wish the objects read inside the R have the suffix .xls (which is only an aesthetic concern), just run the code below:

for (j in arquivos){
  assign(strsplit(j, split="\\.")[[1]][1], read_excel(j))
}

So objects will have names like tab101, ..., tab127, without the extension .xls.