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]]
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-bounces at r-project.org> On Behalf Of Thomas Subia via > R-help > Sent: Saturday, August 22, 2020 6:25 AM > To: r-help at r-project.org > Subject: [R] readxl question > > Colleagues, > > > > I have 250 Excel files in a directory. Each of those files has the samelayout.> The problem is that the data in each Excel data is not in rectangularform. 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, Icreate 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 ofeach> text file to Excel. > > This has worked fine if the number of cells I am extracting from a file issmall.> > 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 at 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.
>From your example, it appears you are reading in the same excel file foreach 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-bounces at r-project.org] On Behalf Of PIKAL Petr Sent: Wednesday, August 26, 2020 3:50 AM To: Thomas Subia <tgs77m at yahoo.com> Cc: r-help at 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-bounces at r-project.org> On Behalf Of Thomas Subia > via R-help > Sent: Saturday, August 22, 2020 6:25 AM > To: r-help at r-project.org > Subject: [R] readxl question > > Colleagues, > > > > I have 250 Excel files in a directory. Each of those files has the > samelayout.> The problem is that the data in each Excel data is not in rectangularform. 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, Icreate 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 > ofeach> text file to Excel. > > This has worked fine if the number of cells I am extracting from a > file issmall.> > 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 at 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.