Hi R,
I have an excel file in which the third column is "date" and others
are
"character" and "numeric".
Number of columns are 12
If I use this to read the file in R: x = read.xls("D:\\file.xls")
The problem is that my date column is read in julian dates.
So I am using: x = read.xls("D:\\file.xls",
colClasses=
c(rep("character",2),"isodate",rep("character",9)))
But what can I do in case I don't know the number of columns in my
file??
I mean is there any way I can specify the colClass of only third column
and for other columns it can take the default classes??
Regards
Utkarsh
This e-mail may contain confidential and/or privileged i...{{dropped:13}}
> > But what can I do in case I don't know the number of columns in my > file?? >I mean is there any way I can specify the colClass of only third column> and for other columns it can take the default classes??Not supported, sorry (I put it on the list). x = read.xls( "D:\\file.xls", colClasses = c( rep( NA , 2 ),"isodate", rep( NA, 9 ) ) ) should work. But you still have to know the total number of columns. (The pro version does have a xls.info command and/or you could read in just the first row and check its content). -- Regards, Hans-Peter [[alternative HTML version deleted]]
You can convert the days to Date class after reading it in. See R News 4/1. On Tue, Mar 25, 2008 at 10:00 AM, Utkarsh Singhal <utkarshs at ambaresearch.com> wrote:> Hi R, > > > > I have an excel file in which the third column is "date" and others are > "character" and "numeric". > > Number of columns are 12 > > > > If I use this to read the file in R: x = read.xls("D:\\file.xls") > > > > The problem is that my date column is read in julian dates. > > > > So I am using: x = read.xls("D:\\file.xls", > colClasses= c(rep("character",2),"isodate",rep("character",9))) > > > > But what can I do in case I don't know the number of columns in my > file?? > > > > I mean is there any way I can specify the colClass of only third column > and for other columns it can take the default classes?? > > > > > > Regards > > Utkarsh > > > > > > This e-mail may contain confidential and/or privileged i...{{dropped:13}} > > ______________________________________________ > 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. >
"Utkarsh Singhal" <utkarshs at ambaresearch.com> wrote in message news:A36876D3F8A5734FA84A4338135E7CC3033BF22A at BAN-MAILSRV03.Amba.com...> Hi R,> I have an excel file in which the third column is "date" and others are > "character" and "numeric". > > If I use this to read the file in R: x = read.xls("D:\\file.xls") > > The problem is that my date column is read in julian dates.RDOBC has its problems when all data in a column are not exactly the same data type, but can read dates: Consider an Excel file with the following: (view with fixed-width font): Index Label1 Date Number Label2 Mixed1 Mixed2 1 A 3/1/2008 12.45 X 1 A 2 B 3/2/2008 14.76 Y 2 B 3 C 3/3/2008 10.99 Z A 1 4 D 3/4/2008 3.14 B 2 Use this to read the excel worksheet "Sheet1": library(RODBC) connection <- odbcConnectExcel("C:/temp/Sample.xls") d <- sqlFetch(connection, "Sheet1") odbcClose(connection) d class(d$Date)> dIndex Label1 Date Number Label2 Mixed1 Mixed2 1 1 A 2008-03-01 12.45 X 1 NA 2 2 B 2008-03-02 14.76 Y 2 NA 3 3 C 2008-03-03 10.99 Z NA 1 4 4 D 2008-03-04 3.14 <NA> NA 2> class(d$Date)[1] "POSIXt" "POSIXct" efg Earl F. Glynn Bioinformatics Stowers Institute for Medical Research
Even if you don't know the number of columns, you probably know the name of the date variables. You can read the Excel file "as is" and later convert dates in R: Reading the date from Excel gives the daynumber. There is the difference between day zero for R (1. 1. 1970) and Excel (31. 12. 1899) and one needs to correct that:> library("xlsReadWrite") > deltaDate <- as.numeric((as.Date("1970-1-1") - as.Date("1899-12-31") +1))> deltaDate[1] 25569 # Consider file 'datumi.xls' with three dates: datum 1.1.1901 1.1.1970 24.7.1953 # When you read the file, you get daynumbers which give strange dates after # conversion to class date.> (dateXls <- read.xls("datumi.xls"))datum 1 367 2 25569 3 19564> class(dateXls$datum) <- "Date" > dateXlsdatum 1 1971-01-03 2 2040-01-03 3 2023-07-26 # Subtracting the 25569 corrects for the difference in R and Excel day zero.> dateXls$datum <- dateXls$datum - deltaDate > dateXlsdatum 1 1901-01-01 2 1970-01-01 3 1953-07-24 # Now you can format the dates as needed, # for example to the current locale format> format(dateXls, "%x")datum 1 1.1.1901 2 1.1.1970 3 24.7.1953 Hope this helps, some more examples are in the file http://ablejec.nib.si/r/Date.pdf (with comments in Slovenian, sorry for that) Andrej -- Andrej Blejec National Institue of Biology Ljubljana, Slovenia> -----Original Message----- > From: r-help-bounces at r-project.org[mailto:r-help-bounces at r-project.org]> On Behalf Of Utkarsh Singhal > Sent: Tuesday, March 25, 2008 3:00 PM > To: r-help at stat.math.ethz.ch > Subject: [R] reading Excel file > > Hi R, > > > > I have an excel file in which the third column is "date" and othersare> "character" and "numeric". > > Number of columns are 12 > > > > If I use this to read the file in R: x = read.xls("D:\\file.xls") > > > > The problem is that my date column is read in julian dates. > > > > So I am using: x = read.xls("D:\\file.xls", > colClasses= c(rep("character",2),"isodate",rep("character",9))) > > > > But what can I do in case I don't know the number of columns in my > file?? > > > > I mean is there any way I can specify the colClass of only thirdcolumn> and for other columns it can take the default classes?? > > > > > > Regards > > Utkarsh > > > > > > This e-mail may contain confidential and/or privilegedi...{{dropped:13}}> > ______________________________________________ > 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.
Sorry but I was interested in reading as date format from the excel itself. Is there any way of doing this? Regards Utkarsh -----Original Message----- From: Gabor Grothendieck [mailto:ggrothendieck@gmail.com] Sent: Tuesday, March 25, 2008 8:43 PM To: Utkarsh Singhal Cc: r-help@stat.math.ethz.ch Subject: Re: [R] reading Excel file If d is the number of days since the Epoch then now <- Sys.Date() now - julian(now) + d will give you a "Date" class variable. There is some info on dates in R News 4/1. On Tue, Mar 25, 2008 at 10:00 AM, Utkarsh Singhal <utkarshs@ambaresearch.com> wrote:> Hi R,>>>> I have an excel file in which the third column is "date" and othersare> "character" and "numeric".>> Number of columns are 12>>>> If I use this to read the file in R: x = read.xls("D:\\file.xls")>>>> The problem is that my date column is read in julian dates.>>>> So I am using: x = read.xls("D:\\file.xls",> colClasses= c(rep("character",2),"isodate",rep("character",9)))>>>> But what can I do in case I don't know the number of columns in my> file??>>>> I mean is there any way I can specify the colClass of only thirdcolumn> and for other columns it can take the default classes??>>>>>> Regards>> Utkarsh>>>>>> This e-mail may contain confidential and/or privilegedi...{{dropped:13}}>> ______________________________________________> R-help@r-project.org mailing list> https://stat.ethz.ch/mailman/listinfo/r-help> PLEASE do read the posting guidehttp://www.R-project.org/posting-guide.html> and provide commented, minimal, self-contained, reproducible code.>This e-mail may contain confidential and/or privileged i...{{dropped:13}}