>From your example, it appears you are reading in the same excel file for each function to get a value. I would look at creating a function that extracts what you need from each file all at once, rather than separate reads.
Stephen C. Upton SEED (Simulation Experiments & Efficient Designs) Center for Data Farming SEED Center website: https://harvest.nps.edu -----Original Message----- From: R-help [mailto:r-help-boun...@r-project.org] On Behalf Of PIKAL Petr Sent: Wednesday, August 26, 2020 3:50 AM To: Thomas Subia <tgs...@yahoo.com> Cc: r-help@r-project.org Subject: Re: [R] readxl question NPS WARNING: *external sender* verify before acting. Hi Are you sure that your command read values from respective cells? I tried it and got empty data frame with names > WO <- lapply(files, read_excel, sheet=1, range=("B3")) > as.data.frame(WO) [1] ano TP303 X96 [4] X0 X3.7519999999999998 X26.7 <0 rows> (or 0-length row.names) To get data, col_names argument should be set to FALSE WO <- lapply(files, read_excel, sheet=1, range=("B3"), col_names=FALSE) WO2 <- lapply(files, read_excel, sheet=1, range=("B5"), col_names=FALSE) After that unlist and one rbind together with t should be enough to give you one table WO <- unlist(WO) WO2 <- unlist(WO2) result <- t(rbind(WO, WO2)) result WO WO2 ...1 "ano" "ano" ...1 "TP303" "261119/2" ...1 "96" "288" ...1 "0" "192" ...1 "3.752" "25.92094" ...1 "26.7" "38.6" > And instead txt document you could do write.table(result, "result.xls", sep = "\t", row.names = F) And now "result.xls" is directly readable with Excel Cheers Petr > > -----Original Message----- > From: R-help <r-help-boun...@r-project.org> On Behalf Of Thomas Subia > via R-help > Sent: Saturday, August 22, 2020 6:25 AM > To: r-help@r-project.org > Subject: [R] readxl question > > Colleagues, > > > > I have 250 Excel files in a directory. Each of those files has the > same layout. > The problem is that the data in each Excel data is not in rectangular form. I've > been using readxl to extract the data which I need. > Each of my metrics are stored in a particular cell. For each metric, I create text > files which stores my metrics. > > > > library(plyr) > > library(readxl) > > > > files <- list.files(pattern="*.xls", full.names = FALSE) > > > > # Extract Work Order > > WO <- lapply(files, read_excel, sheet="Sheet1", range=("B9")) WO_list > <- > as.data.frame(WO) trans_WO <- t(WO_list) write.table(trans_WO > ,"WO.txt") > > > > # Extract bubble 14_1 > > BUBBLE_14_1 <- lapply(files, read_excel, sheet="Sheet1", > range=("c46")) BUBBLE_14_1_list <- as.data.frame(BUBBLE_14_1) > > trans_BUBBLE_14_1 <- t(BUBBLE_14_1_list) > > > > write.table(trans_BUBBLE_14_1,"BUBBLE_14_1.txt") > > > > > > # Extract bubble 14_2 > > BUBBLE_14_2 <- lapply(files, read_excel, sheet="Sheet1", > range=("c62")) BUBBLE_14_2_list <- as.data.frame(BUBBLE_14_2) > > trans_BUBBLE_14_2 <- t(BUBBLE_14_2_list) > > write.table(trans_BUBBLE_14_2,"BUBBLE_14_2.txt") > > > > After the text files have been created, I cut and paste the contents > of each > text file to Excel. > > This has worked fine if the number of cells I am extracting from a > file is small. > > If the number gets larger, this method is inefficient. > > > > Any advice on how to do this would be appreciated. > > > > All the best, > > > > Thomas Subia > > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting- > guide.html and provide commented, minimal, self-contained, > reproducible code.
______________________________________________ R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
