Import multiple excel tables at the same time

Question

Import multiple excel tables at the same time

Navigation

#1 by (7 votes)

5

I want to import 27 Excel tables into R without typing the import command 27 times, since table names range from tab101 to tab127 . I tried this way, but got wrong:

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

So far as I thought it was successful. The list of 27 elements is created, but when I ask to show 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

Being that if I call the str(t) it shows that the data has been imported correctly. I'm not sure how to access each element in the list. But the very focus is on being able to import all tables at once, you do not necessarily have to create a list with them. I tried to do only with for, putting read_table(a) in, but did nothing.

r excel

asked by anonymous 13.05.2018 / 03:06

1 answer

Change InputQuery box size What is the Java equivalent of this lambda in Ruby?

score 7 · Accepted Answer

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

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

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

Then just rotate a for using the assign command. This command will cause objects to be created within the R workspace. 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 named tab101.xls , ..., tab127.xls , the objects inside R will be created with the names tab101.xls , ..., tab127.xls . >

If you do not want the objects read inside R to have the suffix .xls (which is a concern only aesthetic), 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 .xls extension.