read_excel() ignore case of worksheet name?

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

read_excel() ignore case of worksheet name?

Ravi Jeyaraman
Hello All, Is there any parameter to make read_excel() ignore the case-sensitiveness of the worksheet?   I'm using the below to ready in multiple spreadsheets and it works perfectly fine if the worksheet is named 'Tables', but fails when it's named ' TABLES'.  Any thoughts?

lapply(1:nrow(SIS), function(x) readxl::read_excel(SIS$FULL_FILEPATH[x], sheet='Tables', .name_repair = fixColNames))

Thanks in advance for your response.

Cheers
Ravi



--
This email has been checked for viruses by AVG.
https://www.avg.com

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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: read_excel() ignore case of worksheet name?

Erin Hodgess-2
Here’s a thought, please.  Could you use the tolower function and make them
all lower case?

Thanks,
Erin

On Tue, May 26, 2020 at 8:21 PM Ravi Jeyaraman <[hidden email]> wrote:

> Hello All, Is there any parameter to make read_excel() ignore the
> case-sensitiveness of the worksheet?   I'm using the below to ready in
> multiple spreadsheets and it works perfectly fine if the worksheet is named
> 'Tables', but fails when it's named ' TABLES'.  Any thoughts?
>
> lapply(1:nrow(SIS), function(x) readxl::read_excel(SIS$FULL_FILEPATH[x],
> sheet='Tables', .name_repair = fixColNames))
>
> Thanks in advance for your response.
>
> Cheers
> Ravi
>
>
>
> --
> This email has been checked for viruses by AVG.
> https://www.avg.com
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> 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.
>
--
Erin Hodgess, PhD
mailto: [hidden email]

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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: read_excel() ignore case of worksheet name?

Ravi Jeyaraman
I’ve already tried that and doesn’t work

 

From: Erin Hodgess [mailto:[hidden email]]
Sent: Tuesday, May 26, 2020 10:55 PM
To: Ravi Jeyaraman <[hidden email]>
Cc: [hidden email]
Subject: Re: [R] read_excel() ignore case of worksheet name?

 

Here’s a thought, please.  Could you use the tolower function and make them all lower case?

 

Thanks,

Erin

 

On Tue, May 26, 2020 at 8:21 PM Ravi Jeyaraman <[hidden email] <mailto:[hidden email]> > wrote:

Hello All, Is there any parameter to make read_excel() ignore the case-sensitiveness of the worksheet?   I'm using the below to ready in multiple spreadsheets and it works perfectly fine if the worksheet is named 'Tables', but fails when it's named ' TABLES'.  Any thoughts?

lapply(1:nrow(SIS), function(x) readxl::read_excel(SIS$FULL_FILEPATH[x], sheet='Tables', .name_repair = fixColNames))

Thanks in advance for your response.

Cheers
Ravi



--
This email has been checked for viruses by AVG.
https://www.avg.com

______________________________________________
[hidden email] <mailto:[hidden email]>  mailing list -- To UNSUBSCRIBE and more, see
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.

--

Erin Hodgess, PhD

mailto: [hidden email] <mailto:[hidden email]>


        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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: read_excel() ignore case of worksheet name?

Erin Hodgess-2
What about getSheets, please?

That will get the sheet names.

On Tue, May 26, 2020 at 8:59 PM Ravi Jeyaraman <[hidden email]> wrote:

> I’ve already tried that and doesn’t work
>
>
>
> *From:* Erin Hodgess [mailto:[hidden email]]
> *Sent:* Tuesday, May 26, 2020 10:55 PM
> *To:* Ravi Jeyaraman <[hidden email]>
> *Cc:* [hidden email]
> *Subject:* Re: [R] read_excel() ignore case of worksheet name?
>
>
>
> Here’s a thought, please.  Could you use the tolower function and make
> them all lower case?
>
>
>
> Thanks,
>
> Erin
>
>
>
> On Tue, May 26, 2020 at 8:21 PM Ravi Jeyaraman <[hidden email]> wrote:
>
> Hello All, Is there any parameter to make read_excel() ignore the
> case-sensitiveness of the worksheet?   I'm using the below to ready in
> multiple spreadsheets and it works perfectly fine if the worksheet is named
> 'Tables', but fails when it's named ' TABLES'.  Any thoughts?
>
> lapply(1:nrow(SIS), function(x) readxl::read_excel(SIS$FULL_FILEPATH[x],
> sheet='Tables', .name_repair = fixColNames))
>
> Thanks in advance for your response.
>
> Cheers
> Ravi
>
>
>
> --
> This email has been checked for viruses by AVG.
> https://www.avg.com
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> 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.
>
> --
>
> Erin Hodgess, PhD
>
> mailto: [hidden email]
>
>
> <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient> Virus-free.
> www.avg.com
> <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
> <#m_-1767373775547039939_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
--
Erin Hodgess, PhD
mailto: [hidden email]

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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: read_excel() ignore case of worksheet name?

Ista Zahn
In reply to this post by Ravi Jeyaraman
How about

read_excel_table <- function(x) {
  readxl::read_excel(
    x,
    sheet=grep("tables",
               excel_sheets(x),
               ignore.case = TRUE,
               value = TRUE),
    .name_repair = fixColNames
  )
}

lapply(SIS$FULL_FILEPATH, read_excel_table)


--Ista

On Tue, May 26, 2020 at 11:05 PM Ravi Jeyaraman <[hidden email]> wrote:

>
> I’ve already tried that and doesn’t work
>
>
>
> From: Erin Hodgess [mailto:[hidden email]]
> Sent: Tuesday, May 26, 2020 10:55 PM
> To: Ravi Jeyaraman <[hidden email]>
> Cc: [hidden email]
> Subject: Re: [R] read_excel() ignore case of worksheet name?
>
>
>
> Here’s a thought, please.  Could you use the tolower function and make them all lower case?
>
>
>
> Thanks,
>
> Erin
>
>
>
> On Tue, May 26, 2020 at 8:21 PM Ravi Jeyaraman <[hidden email] <mailto:[hidden email]> > wrote:
>
> Hello All, Is there any parameter to make read_excel() ignore the case-sensitiveness of the worksheet?   I'm using the below to ready in multiple spreadsheets and it works perfectly fine if the worksheet is named 'Tables', but fails when it's named ' TABLES'.  Any thoughts?
>
> lapply(1:nrow(SIS), function(x) readxl::read_excel(SIS$FULL_FILEPATH[x], sheet='Tables', .name_repair = fixColNames))
>
> Thanks in advance for your response.
>
> Cheers
> Ravi
>
>
>
> --
> This email has been checked for viruses by AVG.
> https://www.avg.com
>
> ______________________________________________
> [hidden email] <mailto:[hidden email]>  mailing list -- To UNSUBSCRIBE and more, see
> 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.
>
> --
>
> Erin Hodgess, PhD
>
> mailto: [hidden email] <mailto:[hidden email]>
>
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> 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 -- To UNSUBSCRIBE and more, see
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: read_excel() ignore case of worksheet name?

Erin Hodgess-2
Nice!

On Tue, May 26, 2020 at 9:37 PM Ista Zahn <[hidden email]> wrote:

> How about
>
> read_excel_table <- function(x) {
>   readxl::read_excel(
>     x,
>     sheet=grep("tables",
>                excel_sheets(x),
>                ignore.case = TRUE,
>                value = TRUE),
>     .name_repair = fixColNames
>   )
> }
>
> lapply(SIS$FULL_FILEPATH, read_excel_table)
>
>
> --Ista
>
> On Tue, May 26, 2020 at 11:05 PM Ravi Jeyaraman <[hidden email]> wrote:
> >
> > I’ve already tried that and doesn’t work
> >
> >
> >
> > From: Erin Hodgess [mailto:[hidden email]]
> > Sent: Tuesday, May 26, 2020 10:55 PM
> > To: Ravi Jeyaraman <[hidden email]>
> > Cc: [hidden email]
> > Subject: Re: [R] read_excel() ignore case of worksheet name?
> >
> >
> >
> > Here’s a thought, please.  Could you use the tolower function and make
> them all lower case?
> >
> >
> >
> > Thanks,
> >
> > Erin
> >
> >
> >
> > On Tue, May 26, 2020 at 8:21 PM Ravi Jeyaraman <[hidden email]
> <mailto:[hidden email]> > wrote:
> >
> > Hello All, Is there any parameter to make read_excel() ignore the
> case-sensitiveness of the worksheet?   I'm using the below to ready in
> multiple spreadsheets and it works perfectly fine if the worksheet is named
> 'Tables', but fails when it's named ' TABLES'.  Any thoughts?
> >
> > lapply(1:nrow(SIS), function(x) readxl::read_excel(SIS$FULL_FILEPATH[x],
> sheet='Tables', .name_repair = fixColNames))
> >
> > Thanks in advance for your response.
> >
> > Cheers
> > Ravi
> >
> >
> >
> > --
> > This email has been checked for viruses by AVG.
> > https://www.avg.com
> >
> > ______________________________________________
> > [hidden email] <mailto:[hidden email]>  mailing list -- To
> UNSUBSCRIBE and more, see
> > 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.
> >
> > --
> >
> > Erin Hodgess, PhD
> >
> > mailto: [hidden email] <mailto:[hidden email]>
> >
> >
> >         [[alternative HTML version deleted]]
> >
> > ______________________________________________
> > [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> > 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.
>
--
Erin Hodgess, PhD
mailto: [hidden email]

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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: [FORGED] Re: read_excel() ignore case of worksheet name?

Rolf Turner
In reply to this post by Ravi Jeyaraman

There is a function excel_sheets() in the readxl package which will tell
you the names of the sheets.

Using that you should probably be able to take the appropriate evasive
action.

cheers,

Rolf Turner

On 27/05/20 2:59 pm, Ravi Jeyaraman wrote:

> I’ve already tried that and doesn’t work
>
>  
>
> From: Erin Hodgess [mailto:[hidden email]]
> Sent: Tuesday, May 26, 2020 10:55 PM
> To: Ravi Jeyaraman <[hidden email]>
> Cc: [hidden email]
> Subject: Re: [R] read_excel() ignore case of worksheet name?
>
>  
>
> Here’s a thought, please.  Could you use the tolower function and make them all lower case?
>
>  
>
> Thanks,
>
> Erin
>
>  
>
> On Tue, May 26, 2020 at 8:21 PM Ravi Jeyaraman <[hidden email] <mailto:[hidden email]> > wrote:
>
> Hello All, Is there any parameter to make read_excel() ignore the case-sensitiveness of the worksheet?   I'm using the below to ready in multiple spreadsheets and it works perfectly fine if the worksheet is named 'Tables', but fails when it's named ' TABLES'.  Any thoughts?
>
> lapply(1:nrow(SIS), function(x) readxl::read_excel(SIS$FULL_FILEPATH[x], sheet='Tables', .name_repair = fixColNames))
>
> Thanks in advance for your response.
>
> Cheers
> Ravi

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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: read_excel() ignore case of worksheet name?

Jim Lemon-4
In reply to this post by Ravi Jeyaraman
Hi Ravi,
The simplest way seems to be the excel_sheets function in the readxl
package. If you know that the sheet name will be some form of "Table",
something like this may do it:

getSheetCase<-function(filepath,sheetname) {
 localnames<-c(sheetname,
  paste0(toupper(substr(sheetname,1,1)),substr(sheetname,2,nchar(sheetname))),
  toupper(sheetname),tolower(sheetname))
 xlnames<-readxl::excel_sheets(filepath)
 namepos<-0
 for(pos in 1:length(localnames)) {
  if(length(grep(localnames[pos],xlnames))) namepos<-pos
  cat(localnames[pos],namepos,"\n")
 }
 if(is.null(namepos)) return(NULL)
 else return(read_excel(filepath,
  sheet=localnames[namepos]))
}
getSheetCase("GS_SS2.xlsx","intent")

This example works on an excel spreadsheet I have as in the last line.
Just as I was about to send this, three messages came in. One was
Ista's excellent solution that blew mine away. Maybe next time.

Jim

On Wed, May 27, 2020 at 1:05 PM Ravi Jeyaraman <[hidden email]> wrote:

>
> I’ve already tried that and doesn’t work
>
>
>
> From: Erin Hodgess [mailto:[hidden email]]
> Sent: Tuesday, May 26, 2020 10:55 PM
> To: Ravi Jeyaraman <[hidden email]>
> Cc: [hidden email]
> Subject: Re: [R] read_excel() ignore case of worksheet name?
>
>
>
> Here’s a thought, please.  Could you use the tolower function and make them all lower case?
>
>
>
> Thanks,
>
> Erin
>
>
>
> On Tue, May 26, 2020 at 8:21 PM Ravi Jeyaraman <[hidden email] <mailto:[hidden email]> > wrote:
>
> Hello All, Is there any parameter to make read_excel() ignore the case-sensitiveness of the worksheet?   I'm using the below to ready in multiple spreadsheets and it works perfectly fine if the worksheet is named 'Tables', but fails when it's named ' TABLES'.  Any thoughts?
>
> lapply(1:nrow(SIS), function(x) readxl::read_excel(SIS$FULL_FILEPATH[x], sheet='Tables', .name_repair = fixColNames))
>
> Thanks in advance for your response.
>
> Cheers
> Ravi
>
>
>
> --
> This email has been checked for viruses by AVG.
> https://www.avg.com
>
> ______________________________________________
> [hidden email] <mailto:[hidden email]>  mailing list -- To UNSUBSCRIBE and more, see
> 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.
>
> --
>
> Erin Hodgess, PhD
>
> mailto: [hidden email] <mailto:[hidden email]>
>
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> 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 -- To UNSUBSCRIBE and more, see
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: read_excel() ignore case of worksheet name?

Ravi Jeyaraman
In reply to this post by Ista Zahn
Ista, With few tweaks this worked beautifully.  Thank you so much.  

-----Original Message-----
From: Ista Zahn [mailto:[hidden email]]
Sent: Tuesday, May 26, 2020 11:38 PM
To: Ravi Jeyaraman <[hidden email]>
Cc: Erin Hodgess <[hidden email]>; [hidden email]
Subject: Re: [R] read_excel() ignore case of worksheet name?

How about

read_excel_table <- function(x) {
  readxl::read_excel(
    x,
    sheet=grep("tables",
               excel_sheets(x),
               ignore.case = TRUE,
               value = TRUE),
    .name_repair = fixColNames
  )
}

lapply(SIS$FULL_FILEPATH, read_excel_table)


--Ista

On Tue, May 26, 2020 at 11:05 PM Ravi Jeyaraman <[hidden email]> wrote:

>
> I’ve already tried that and doesn’t work
>
>
>
> From: Erin Hodgess [mailto:[hidden email]]
> Sent: Tuesday, May 26, 2020 10:55 PM
> To: Ravi Jeyaraman <[hidden email]>
> Cc: [hidden email]
> Subject: Re: [R] read_excel() ignore case of worksheet name?
>
>
>
> Here’s a thought, please.  Could you use the tolower function and make them all lower case?
>
>
>
> Thanks,
>
> Erin
>
>
>
> On Tue, May 26, 2020 at 8:21 PM Ravi Jeyaraman <[hidden email] <mailto:[hidden email]> > wrote:
>
> Hello All, Is there any parameter to make read_excel() ignore the case-sensitiveness of the worksheet?   I'm using the below to ready in multiple spreadsheets and it works perfectly fine if the worksheet is named 'Tables', but fails when it's named ' TABLES'.  Any thoughts?
>
> lapply(1:nrow(SIS), function(x)
> readxl::read_excel(SIS$FULL_FILEPATH[x], sheet='Tables', .name_repair
> = fixColNames))
>
> Thanks in advance for your response.
>
> Cheers
> Ravi
>
>
>
> --
> This email has been checked for viruses by AVG.
> https://www.avg.com
>
> ______________________________________________
> [hidden email] <mailto:[hidden email]>  mailing list -- To
> UNSUBSCRIBE and more, see 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.
>
> --
>
> Erin Hodgess, PhD
>
> mailto: [hidden email] <mailto:[hidden email]>
>
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> 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 email has been checked for viruses by AVG.
https://www.avg.com

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.