reading Excel file

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

reading Excel file

Utkarsh Singhal
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}}

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: reading Excel file

Hans-Peter Suter
>
> 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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: reading Excel file

Gabor Grothendieck
In reply to this post by Utkarsh Singhal
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
<[hidden email]> 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}}
>
> ______________________________________________
> [hidden email] 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.
>

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: reading Excel file

Earl F. Glynn
In reply to this post by Utkarsh Singhal
"Utkarsh Singhal" <[hidden email]> wrote in message
news:[hidden email]...
> 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)

> d
  Index 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

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: reading Excel file

ssefick
why can't you know the number of columns if it is an excel file?

On Tue, Mar 25, 2008 at 2:36 PM, Earl F. Glynn
<[hidden email]> wrote:

> "Utkarsh Singhal" <[hidden email]> wrote in message
>  news:[hidden email]...
>
> > 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)
>
>  > d
>   Index 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
>
>
>
>  ______________________________________________
>  [hidden email] 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.
>



--
Let's not spend our time and resources thinking about things that are
so little or so large that all they really do for us is puff us up and
make us feel like gods. We are mammals, and have not exhausted the
annoying little problems of being mammals.

        -K. Mullis

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: reading Excel file

ablejec
In reply to this post by Utkarsh Singhal
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: [hidden email]
[mailto:[hidden email]]

> On Behalf Of Utkarsh Singhal
> Sent: Tuesday, March 25, 2008 3:00 PM
> To: [hidden email]
> 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}}
>
> ______________________________________________
> [hidden email] 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.

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: reading Excel file

Utkarsh Singhal
In reply to this post by Utkarsh Singhal
 

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:[hidden email]]
Sent: Tuesday, March 25, 2008 8:43 PM
To: Utkarsh Singhal
Cc: [hidden email]
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

<[hidden email]> 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}}

>

> ______________________________________________

> [hidden email] 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.

>

This e-mail may contain confidential and/or privileged i...{{dropped:13}}

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: reading Excel file

jholtman
If you want to read in the numeric values from EXCEL and the use them,
here is a function I use to go the other way -- take a POSIXct value
and convert it to what EXCEL wants.  So you can just write the inverse
of this function:

unix2EXCEL <- function (time) time/86400 + 25569


On Tue, Mar 25, 2008 at 11:41 PM, Utkarsh Singhal
<[hidden email]> wrote:

>
>
> 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:[hidden email]]
> Sent: Tuesday, March 25, 2008 8:43 PM
> To: Utkarsh Singhal
> Cc: [hidden email]
> 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
>
> <[hidden email]> 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}}
>
> >
>
> > ______________________________________________
>
> > [hidden email] 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.
>
> >
>
> This e-mail may contain confidential and/or privileged i...{{dropped:13}}
>
> ______________________________________________
> [hidden email] 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.
>



--
Jim Holtman
Cincinnati, OH
+1 513 646 9390

What is the problem you are trying to solve?

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: reading Excel file

Hans-Peter Suter
yet another possibility...

if you look at 'help(package=xlsReadWrite)' you'll find a function
'dateTimeToStr' by which you can convert your date-numbers from column 3
into a string. Something along (untested):

myRead <- function( fn ) {
  dat <- read.xls( file = fn, dateTimeAs = "numeric" )
  dat[,3] <- dateTimeToStr( dat[,3] )  # or any other conversion function
  dat
}

'dateTimeAs' argument is (probably) needed to retrieve plain numbers and
override the default conversion to date string.

--
Regards,
Hans-Peter

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] 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.