When I read excel files using the read.xlsx() command any cells that have formulas in them come up as NA. Is there a way to read just the numeric value of the cell without using the "paste value" command in Excel? I need to read in hundreds of Excel spreadsheets and compile them into one large super spreadsheet automatically. Hence the reason I cannot reformat each sheet manually. [[alternative HTML version deleted]]
It sounds like this might actually make more sense to do in VBA and not R (gasp!) if your only goal is to have excel in --> excel out. [UNTESTED] As far as a workaround if you really want to do this in R, perhaps you can specify that you want character vectors everywhere and your connection client won't try to evaluate them. I don't really know if this actually works though...If you are again willing to step outside of R, there are clients [I'm thinking perl and Python] that convert things to character csv's which would certainly make this work. Best, Michael On Tue, May 15, 2012 at 4:11 PM, Mike Smith <ziggy55311 at gmail.com> wrote:> When I read excel files using the read.xlsx() command any cells that have > formulas in them come up as NA. > > Is there a way to read just the numeric value of the cell without using the > "paste value" command in Excel? ?I need to read in hundreds of Excel > spreadsheets and compile them into one large super spreadsheet > automatically. ?Hence the reason I cannot reformat each sheet manually. > > ? ? ? ?[[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list > 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.
On Tue, May 15, 2012 at 4:11 PM, Mike Smith <ziggy55311 at gmail.com> wrote:> When I read excel files using the read.xlsx() command any cells that have > formulas in them come up as NA. > > Is there a way to read just the numeric value of the cell without using the > "paste value" command in Excel? ?I need to read in hundreds of Excel > spreadsheets and compile them into one large super spreadsheet > automatically. ?Hence the reason I cannot reformat each sheet manually. >Try read.xls in gdata. -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com
Tena koe Mike Another alternative to those already given is to use the RODBC package. HTH .... Peter Alspach. -----Original Message----- From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf Of Mike Smith Sent: Wednesday, 16 May 2012 8:11 a.m. To: r-help at r-project.org Subject: [R] Reading Excel Formulas as values When I read excel files using the read.xlsx() command any cells that have formulas in them come up as NA. Is there a way to read just the numeric value of the cell without using the "paste value" command in Excel? I need to read in hundreds of Excel spreadsheets and compile them into one large super spreadsheet automatically. Hence the reason I cannot reformat each sheet manually. [[alternative HTML version deleted]] ______________________________________________ R-help at r-project.org mailing list 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. The contents of this e-mail are confidential and may be subject to legal privilege. If you are not the intended recipient you must not use, disseminate, distribute or reproduce all or any part of this e-mail or attachments. If you have received this e-mail in error, please notify the sender and delete all material pertaining to this e-mail. Any opinion or views expressed in this e-mail are those of the individual sender and may not represent those of The New Zealand Institute for Plant and Food Research Limited.
I can't replicate your problem. I created a spreadsheet in Excel 2007 consisting of three columns. Numbers from 1 - 15, rand(), and the sum of the first two columns. Using all the defaults with read.xlsx() (package: xlsx), I get the values of each column and using keepFormulas=TRUE, I get the formulas as factors. I don't get any NA's. I can also place a formula on the second sheet that accesses data from the first sheet without any problems. I haven't tried, Excel 2010. Could your formulas be accessing data from another spreadsheet? ---------------------------------------------- David L Carlson Associate Professor of Anthropology Texas A&M University College Station, TX 77843-4352> -----Original Message----- > From: r-help-bounces at r-project.org [mailto:r-help-bounces at r- > project.org] On Behalf Of Mike Smith > Sent: Tuesday, May 15, 2012 3:11 PM > To: r-help at r-project.org > Subject: [R] Reading Excel Formulas as values > > When I read excel files using the read.xlsx() command any cells that > have > formulas in them come up as NA. > > Is there a way to read just the numeric value of the cell without using > the > "paste value" command in Excel? I need to read in hundreds of Excel > spreadsheets and compile them into one large super spreadsheet > automatically. Hence the reason I cannot reformat each sheet manually. > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list > 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.