extract worksheet names from an Excel file

[Shi, Tao]

Hi list,

Is there a R function I can use to extract the worksheet names from an Excel file?  If no, any other automatic ways (not using R) to do this?

thanks!

...Tao


______________________________________________
[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.

[Bill.Venables]

Package XLConnect appears to provide this kind of thing.

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Shi, Tao
Sent: Friday, 24 June 2011 2:42 PM
To: [hidden email]
Subject: [R] extract worksheet names from an Excel file

Hi list,

Is there a R function I can use to extract the worksheet names from an Excel file?  If no, any other automatic ways (not using R) to do this?

thanks!

...Tao


______________________________________________
[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.

[David Scott-6]

  On 24/06/11 16:55, [hidden email] wrote:

> Package XLConnect appears to provide this kind of thing.
>
> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]] On Behalf Of Shi, Tao
> Sent: Friday, 24 June 2011 2:42 PM
> To: [hidden email]
> Subject: [R] extract worksheet names from an Excel file
>
> Hi list,
>
> Is there a R function I can use to extract the worksheet names from an Excel file?  If no, any other automatic ways (not using R) to do this?
>
> thanks!
>
> ...Tao
>
>
> ______________________________________________
> [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.

If you use RODBC to connect to an xls or xlsx file you can use sqlTables
to show not only the worksheet names, but the presence of any named data
ranges.

Sample code from a student exercise:

require(RODBC)
channel <- odbcConnectExcel("bikesWithDate.xls")
sqlTables(channel)



David

--
_________________________________________________________________
David Scott Department of Statistics
                The University of Auckland, PB 92019
                Auckland 1142,    NEW ZEALAND
Phone: [phone], or [phone] ext 85055
Email: [hidden email],  Fax: [phone]

______________________________________________
[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.

[Shi, Tao]

Thank you, David and Bill!  I'll try that.

...Tao



----- Original Message -----

> From: David Scott <[hidden email]>
> To: [hidden email]
> Cc: [hidden email]; [hidden email]
> Sent: Thursday, June 23, 2011 10:11 PM
> Subject: Re: [R] extract worksheet names from an Excel file
>
>   On 24/06/11 16:55, [hidden email] wrote:
>>  Package XLConnect appears to provide this kind of thing.
>>
>>  -----Original Message-----
>>  From: [hidden email] [mailto:[hidden email]] On
> Behalf Of Shi, Tao
>>  Sent: Friday, 24 June 2011 2:42 PM
>>  To: [hidden email]
>>  Subject: [R] extract worksheet names from an Excel file
>>
>>  Hi list,
>>
>>  Is there a R function I can use to extract the worksheet names from an
> Excel file?  If no, any other automatic ways (not using R) to do this?
>>
>>  thanks!
>>
>>  ...Tao
>>
>>
>>  ______________________________________________
>>  [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.
>
> If you use RODBC to connect to an xls or xlsx file you can use sqlTables
> to show not only the worksheet names, but the presence of any named data
> ranges.
>
> Sample code from a student exercise:
>
> require(RODBC)
> channel <- odbcConnectExcel("bikesWithDate.xls")
> sqlTables(channel)
>
>
>
> David
>
> --
> _________________________________________________________________
> David Scott    Department of Statistics
>         The University of Auckland, PB 92019
>         Auckland 1142,    NEW ZEALAND
> Phone: [phone], or [phone] ext 85055
> Email:    [hidden email],  Fax: [phone]
>

______________________________________________
[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.

[Gabor Grothendieck]

On Fri, Jun 24, 2011 at 12:41 AM, Shi, Tao <[hidden email]> wrote:
> Hi list,
>
> Is there a R function I can use to extract the worksheet names from an Excel file?  If no, any other automatic ways (not using R) to do this?
>
> thanks!
>

Many or all of the Excel interfaces listed here:

http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows&s=excel

can do that but be careful since one or more the packages listed there
always returns the sheet names sorted in alphabetical order which
means that you can't tell which is the first sheet, which is the
second, etc. (if that is important).

gdata has a specific function to do it:

library(gdata)
sheetNames("myfile.xls")

--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com

______________________________________________
[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.

[Shi, Tao]

Thanks, Gabor!  That's really helpful!

...Tao



----- Original Message -----

> From: Gabor Grothendieck <[hidden email]>
> To: "Shi, Tao" <[hidden email]>
> Cc: "[hidden email]" <[hidden email]>
> Sent: Friday, June 24, 2011 5:02 AM
> Subject: Re: [R] extract worksheet names from an Excel file
>
> On Fri, Jun 24, 2011 at 12:41 AM, Shi, Tao <[hidden email]> wrote:
>>  Hi list,
>>
>>  Is there a R function I can use to extract the worksheet names from an
> Excel file?  If no, any other automatic ways (not using R) to do this?
>>
>>  thanks!
>>
>
> Many or all of the Excel interfaces listed here:
>
> http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows&s=excel
>
> can do that but be careful since one or more the packages listed there
> always returns the sheet names sorted in alphabetical order which
> means that you can't tell which is the first sheet, which is the
> second, etc. (if that is important).
>
> gdata has a specific function to do it:
>
> library(gdata)
> sheetNames("myfile.xls")
>
> --
> Statistics & Software Consulting
> GKX Group, GKX Associates Inc.
> tel: 1-877-GKX-GROUP
> email: ggrothendieck at gmail.com
>

______________________________________________
[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.