Skip to content

read_excel() ignore case of worksheet name?

9 messages · Ravi Jeyaraman, Erin Hodgess, Ista Zahn +2 more

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
Erin Hodgess
# 
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 <ravi76 at gmail.com> wrote:

            

      
      
      
    

        

      
      
    

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Ravi Jeyaraman
    

    

      
      #
    

  


  

      
I?ve already tried that and doesn?t work

 

From: Erin Hodgess [mailto:erinm.hodgess at gmail.com] 
Sent: Tuesday, May 26, 2020 10:55 PM
To: Ravi Jeyaraman <ravi76 at gmail.com>
Cc: r-help at r-project.org
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 <ravi76 at gmail.com <mailto:ravi76 at gmail.com> > 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

______________________________________________
R-help at r-project.org <mailto:R-help at r-project.org>  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
    

    

      
      #
    

  


  

      
What about getSheets, please?

That will get the sheet names.

        
On Tue, May 26, 2020 at 8:59 PM Ravi Jeyaraman <ravi76 at gmail.com> wrote:

        

            

      
      
      
    

        

      
      
    

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Ista Zahn
    

    

      
      #
    

  


  

      
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 <ravi76 at gmail.com> wrote:

            

      
      
      
    

        

      
      
    

  
  


  


      

    

    

      
      

        


  

        

      Erin Hodgess
    

    

      
      #
    

  


  

      
Nice!

        
On Tue, May 26, 2020 at 9:37 PM Ista Zahn <istazahn at gmail.com> wrote:

        

            

      
      
      
    

        

      
      
    

            

      
      
      
    

        

      
      
    

        

      
      
    

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Rolf Turner
    

    

      
      #
    

  


  

      
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:

            

      
      
      
    

  
  


  


      

    

    

      
      

        


  

        

      Jim Lemon
    

    

      
      #
    

  


  

      
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 <ravi76 at gmail.com> wrote:

            

      
      
      
    

        

      
      
    

  
  


  


      

    

    

      
      

        


  

        

      Ravi Jeyaraman
    

    

      
      #
    

  


  

      
Ista, With few tweaks this worked beautifully.  Thank you so much.  

-----Original Message-----
From: Ista Zahn [mailto:istazahn at gmail.com] 
Sent: Tuesday, May 26, 2020 11:38 PM
To: Ravi Jeyaraman <ravi76 at gmail.com>
Cc: Erin Hodgess <erinm.hodgess at gmail.com>; r-help at r-project.org
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 <ravi76 at gmail.com> wrote: