I am reading dates in Excel2007 into R.
Here are the functions I used:
library(RODBC)
channel<-odbcConnectExcel2007("myfile.xlsx")
tmp<-sqlFetch(channel,"1",as.is=T)
The dates in myfile.xlsx are all in this format: mm/dd/yyyy. But when I read it
to R, some columns look like "yyyy-mm-dd 00:00:00", some columns look
like "yyyy-mm-dd", and some columns are numbers. I do not know how I
can control this.
Any help? Thanks!
_________________________________________________________________
Hotmail is redefining busy with tools for the New Busy. Get more from your
inbox.
N:WL:en-US:WM_HMP:042010_2
[[alternative HTML version deleted]]
Tena koe What do you want to control? You can govern the format used in R using the appropriate R functions. I doubt it would be useful to have dates read from Excel depend on the format set for displaying those dates in Excel. HTH .... Peter Alspach> -----Original Message----- > From: r-help-bounces at r-project.org [mailto:r-help-bounces at r- > project.org] On Behalf Of Hongying Li > Sent: Friday, 30 July 2010 5:33 a.m. > To: r-help at r-project.org > Subject: [R] reading dates in Excel into R > > > I am reading dates in Excel2007 into R. > > Here are the functions I used: > > library(RODBC) > channel<-odbcConnectExcel2007("myfile.xlsx") > tmp<-sqlFetch(channel,"1",as.is=T) > > > The dates in myfile.xlsx are all in this format: mm/dd/yyyy. But when I > read it to R, some columns look like "yyyy-mm-dd 00:00:00", some > columns look like "yyyy-mm-dd", and some columns are numbers. I do not > know how I can control this. > > > > Any help? Thanks! > > > > _________________________________________________________________ > Hotmail is redefining busy with tools for the New Busy. Get more from > your inbox. > > N:WL:en-US:WM_HMP:042010_2 > [[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.
Does function ?strptime help you in any way? -- View this message in context: http://r.789695.n4.nabble.com/reading-dates-in-Excel-into-R-tp2306830p2307744.html Sent from the R help mailing list archive at Nabble.com.
To read the data into R 1) I format my dates as YYYY-MM-DD in Excel (or OpenOffice Calc), 2) (change =na() to NA) 3) Check that required number of significant decimal places are displayed, 4) export as csv and 5) Use readSeries() function from Rmetrics timeSeries package to read the data into R. For what it is worth I have found this approach flexible . Best Regards John On 29 July 2010 22:18, Peter Alspach <Peter.Alspach at plantandfood.co.nz> wrote:> Tena koe > > What do you want to control? ?You can govern the format used in R using the appropriate R functions. ?I doubt it would be useful to have dates read from Excel depend on the format set for displaying those dates in Excel. > > HTH .... >,,,> Peter Alspach > >> -----Original Message----- >> From: r-help-bounces at r-project.org [mailto:r-help-bounces at r- >> project.org] On Behalf Of Hongying Li >> Sent: Friday, 30 July 2010 5:33 a.m. >> To: r-help at r-project.org >> Subject: [R] reading dates in Excel into R >> >> >> I am reading dates in Excel2007 into R. >> >> Here are the functions I used: >> >> library(RODBC) >> channel<-odbcConnectExcel2007("myfile.xlsx") >> tmp<-sqlFetch(channel,"1",as.is=T) >> >> >> The dates in myfile.xlsx are all in this format: mm/dd/yyyy. But when I >> read it to R, some columns look like "yyyy-mm-dd 00:00:00", some >> columns look like "yyyy-mm-dd", and some columns are numbers. I do not >> know how I can control this. >> >> >> >> Any help? Thanks! >> >> >> >> _________________________________________________________________ >> Hotmail is redefining busy with tools for the New Busy. Get more from >> your inbox. >> >> N:WL:en-US:WM_HMP:042010_2 >> ? ? ? [[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. > > ______________________________________________ > 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. >-- John C Frain Economics Department Trinity College Dublin Dublin 2 Ireland www.tcd.ie/Economics/staff/frainj/home.html mailto:frainj at tcd.ie mailto:frainj at gmail.com
RExcel (available as an Excel Addin in Windows only) tries very hard to transfer dates and times faithfully to R. The package RExcelInstaller on CRAN will install it. On Jul 29, 2010, at 7:32 PM, Hongying Li wrote:> > I am reading dates in Excel2007 into R. > > Here are the functions I used: > > library(RODBC) > channel<-odbcConnectExcel2007("myfile.xlsx") > tmp<-sqlFetch(channel,"1",as.is=T) > > > The dates in myfile.xlsx are all in this format: mm/dd/yyyy. But when I read it to R, some columns look like "yyyy-mm-dd 00:00:00", some columns look like "yyyy-mm-dd", and some columns are numbers. I do not know how I can control this. > > > > Any help? Thanks! > > > > _________________________________________________________________ > Hotmail is redefining busy with tools for the New Busy. Get more from your inbox. > > N:WL:en-US:WM_HMP:042010_2 > [[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. >-- Erich Neuwirth, University of Vienna Faculty of Computer Science Center for Computer Science Didactics and Learning Research Visit our SunSITE at http://sunsite.univie.ac.at Phone: +43-1-4277-39902 Fax: +43-1-4277-39459