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 = "")
}
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"