[R] reading Excel file

[Andrej Blejec]

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"
> dateXls

datum
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
> dateXls

datum
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 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.