how can I import and convert multiple excel files in a folder into dataframe in R

Question

I have 1,000 files in a folder and want to work on them as a data frame in R. I can convert individual files but need a way to convert files all at once. Any help is needed, please. what I have so far:

my code:

my_files <- list.files()
my_files <- as.data.frame(my_files)
my_files

The class(my_files) shows a data frame but not really working as a data frame

`alldat <- lapply(my_files, read.csv)`, then work on them as a [list of frames](https://stackoverflow.com/a/24376207/3358272). — r2evans, Mar 22 '20 at 23:26
Your code is generating a frame of strings. R cannot *intuit* that a string should be interpreted as a file, that you want it to read it in, that the file might be CSV, CSV (semi-colon), TSV, etc ... and then read all of them in for you. That isn't a language, that (to me) sounds like a digital assistant (who is presuming much about what I mean given a vector of strings). — r2evans, Mar 22 '20 at 23:28
Thanks a lot, I have been able to retrieve the file the correct format. Now, I want to see how I can delete all the first column too. so so much relieved for now. Thanks a lot — Spencer, Mar 22 '20 at 23:58

score 0 · Answer 1 · answered Mar 22 '20 at 23:41

Here is the solution I use in this situation. Make changes where needed. You need to install the packages rio and data.table first.

my_files <- list.files(path = "~", # change this to the path where your files are
                       pattern = ".xlsx$", # use the file ending of your files
                       full.names = TRUE,
                       recursive = TRUE)

my_df_list <- lapply(my_files, rio::import) # imports files and produces list of data.frames
my_df <- data.table::rbindlist(my_df_list) # attempts to bind data.frames into one

score 0 · Answer 2 · answered Mar 23 '20 at 05:03

We can use list.files to get path of the files and using lapply we can iterate over file names, read files and remove the first column.

my_files <- list.files(full.names = TRUE)

all_files <- lapply(my_files, function(x) read.csv(x)[-1])

all_files is a list of dataframes without the first column in each file which can be accessed via all_files[[1]], all_files[[2]] etc.

how can I import and convert multiple excel files in a folder into dataframe in R

2 Answers2