CODEWITHSUNDEEP
rexcel
Ankit Bhatia
Ankit Bhatia

Reputation: 109

How to read an excel file with empty tabs in R?

I am importing an excel file in R. Some of its tabs are empty. R is not reading those tabs. It gives me an error : Skipped all Data.

library(readxl)
sheetnames = excel_sheets("Saudi Diapers.xlsx")
k = length(sheetnames)
i=1

for(i in 1:k) {

  abc = read_excel("Saudi Diapers.xlsx",i`,col_names = T)

  rownames(abc) = NULL

  tabname = paste(sheetnames[i], ".csv")

  write.csv(file=paste("Saudi Diapers",tabname,sep = "_"), x= abc, row.names = F,na="",sep = "")

}

Upvotes: 2

Views: 1177

Answers (1)

RHA
RHA

Reputation: 3872

You can do this in two ways:
1. Alter the function read_excel in such a way that it doesn't trow an error, but returns an empty dataframe 2. Catch the error with tryCatch and return an empty dataframe. I have chosen for the second option here. The tryCatch function essentially tries to execute a expression (in this case read_excel(...)) and if that generates an error, it return the function in the error-condition (in this case data.frame(NULL)). 

library(readxl)

I have made a excel sheet with one filled and two empty sheets.
filename <- "test_blanksheet.xlsx" #Put your own filename here

sheetnames = excel_sheets(filename)

for(i in 1:length(sheetnames)) {

  tempdf = tryCatch(read_excel(filename,i,col_names = T), 
                 error = function(err) { data.frame(NULL)})

  write.csv(file=paste0("Saudi_Diapers_",sheetnames[i],".csv"), x= tempdf,
  row.names = F,na="")
      }

And it works:

list.files()
[1] "Saudi_Diapers_Data2.csv" "Saudi_Diapers_Empty1.csv"
[3] "Saudi_Diapers_Empty3.csv" "test_blanksheet.xlsx"

Upvotes: 1

Related Questions