Re: [R] reading Excel file

From: Andrej Blejec <Andrej.Blejec_at_nib.si>
Date: Tue, 25 Mar 2008 22:21:37 +0100


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@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.
______________________________________________ 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.
Received on Wed 26 Mar 2008 - 00:28:07 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Wed 26 Mar 2008 - 00:30:25 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive