R- Reading excel files with loop and creating data frames

Question

I have a bunch of files (number continuously growing), each file shoud get a seperate data frame.

To simplify the reading I would like to use a Loop to read all files. The new data frames should be named after a string from "Filename"

In the last row i would like to create an data Frame, the Name of the new data frame should be the Content of "Filename".

for(x in 1:nrow(Namen)) # creation of the loop

{

  Filename<- Namen[x,1] #Takes the Filename from the the DF

  einlesepfad <- "path for reading the xlsm files" 
   einlesepfad <- paste(einlesepfad,Filename,".xlsm", sep="") # creation of the path to read the xlsm file
   Filename <- read_excel(einlesepfad) #The Content of "Filename" should be the Name of the new data frame
}

Please clarify your question. Have a look here on how to create a minimal working example. — psychOle
– psychOle, Commented Sep 7, 2018 at 7:44

Liudvikas Akelis · Accepted Answer · 2018-09-07 12:42:34Z

1

If I understand correctly, you want to read many files from a list into individual data frames? I'm offering a slightly different solution:

results <- list()
for(x in 1:nrow(Namen)) # creation of the loop
{
  Filename<- Namen[x,1] #Takes the Filename from the the DF

  einlesepfad <- "path for reading the xlsm files" 
  einlesepfad <- paste(einlesepfad,Filename,".xlsm", sep="") # creation of the path to read the xlsm file
  results[Filename] <- read_excel(einlesepfad) # The list gets a new item, named whatever value Filename had, that contains your data
}

This way, each of your files is in a separate data frame, and all the data frames are in one list - results. To access the data frame from file "datafile1.xlsm" do this:

results['datafile1']

or even

results$datafile1

What you were trying to do previously was to give a separate variable to each data frame - possible (you could construct statements with paste and then eval them, I think), but a list of dataframes is almost always a better idea.

edited Sep 7, 2018 at 12:42

answered Sep 7, 2018 at 8:12

Liudvikas Akelis

1,4011 gold badge11 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rowdyfromtakatuka Over a year ago

I have a bunch of files (number continuously growing), each file shoud get a seperate data frame. To simplify the reading I would like to use a Loop to read all files. The new data frames should be named after a string from "Filename"

Steve · Accepted Answer · 2018-09-07 10:00:07Z

Using your packages and example you can do the following:

Pass the filenames in a list:

Namen <- list(c('fname1', 'fname2') # your names list (i am guessing without .xlsx)
einlesepfad <- "path for reading the xlsm files"

Push each file into a dataframe

df.list <- list() #empty data frame list
df.list= lapply(Namen, function(i){
  fname <- paste0(einlesepfad, i, '.xlsm') # get the full filename
  x = read_excel(fname) # read the excel file in
  # Edit: Removed the name column part
  # Return your data
  return (x)
})

Edit:
Just saw your edit with the request for named list by the elements. Here is a very fast (non-elegant) solution to add the names:

names(df.list) <- lapply(Namen, function(x){x})

You can then access every dataframe of the list by either df.list['name'] or by index df.list[1]

Edit2:
Also, since i noticed you say your filename list is continuously growing, provided that you store all the files in the same dir you could do the following:

Namen <– list.files(einlesepfad, '*.xlsm')

and just remember to change the fname inside the function and remove the '.xlsm' part as such:

fname <- paste0(einlesepfad, i)

Collectives™ on Stack Overflow

R- Reading excel files with loop and creating data frames

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related