Read multiples files in R allocated in different directories but with the same name

Question

I am trying to solve a little problem in R about reading multiples files with the same name but allocated in different directories.

I have 100 files named R04 and with extension .xlsx, but they are allocated in 100 different directories, like this:

file 1: C:\General Data\Month1\R04.xlsx

file 2: C:\General Data\Month2\R04.xlsx

.

file 100: C:\General Data\Month2\R04.xlsx

My problem is I can't get to read these files. Maybe it is possible to read they with for, and due to the same name in 100 files I don't know if is possible to name each one with a number related to the month, for example in the case of first file the name should be 01 due to month 1, etc.

Did you try reading a single file first?If yes, can you please post the code. — Metrics, Aug 18 '13 at 21:58
@Metrics @agstudy OK, When I read one file I make this, and all is fine: `setwd("D:/Documentos/Files/Month1") library(RODBC) Base="R04.xlsx" conexion=odbcConnectExcel2007(Base) Month1_R04=sqlQuery(channel=conexion,"select * from [Hoja1$]",as.is=TRUE) close(conexion)` The problem is I have 100 different directories like `D:/Documentos/Files/Month1` with 2,3, etc. and I look for a way to make this with a loop with for because the number of months change and I will have 101, 102, etc. And you can see I will have another problem with the `Month1_R04` name structure for each file — Duck, Aug 19 '13 at 00:28

score 1 · Answer 1 · answered Aug 18 '13 at 22:39

I would use list.files to list my files by pattern. Then some regular expression to name my files.

For example:

library(XLConnect)
files.path <- list.files(pattern=".*R04.xlsx",full.names=TRUE)
setNames(lapply(files.path, function(x) read.xlsx(x,1)),
         gsub('.*/(.*)/R04.*','\\1_R04',files.path))

Using some data to show how I am using gsub here:

ll <- c("C:/General Data/Month1/R04.xlsx",
         "C:/General Data/Month2/R04.xlsx",
         "C:/General Data/Month3/R04.xlsx")
gsub('.*/(.*)/R04.*','\\1_R04',ll)
[1] "Month1_R04" "Month2_R04" "Month3_R04"

But I have the `R04` files in a diferents folders but not in the same! — Duck, Aug 19 '13 at 01:46

score 0 · Answer 2 · answered Aug 18 '13 at 22:03

0

Try using the XLConnect package.

library(XLConnect)
file1 <- readWorksheet(loadWorkbook("C:\General Data\Month1\R04.xlsx"),sheet=1)

answered Aug 18 '13 at 22:03

Metrics

15,172
7
54
83

Read multiples files in R allocated in different directories but with the same name

2 Answers2