Error with read.xlsx when knit in Rmarkdown

I'm trying to knit a html document in rmarkdown with a code that works perfect in a script.

In the script I load a lot of excel files that's why I use a function named input_files, then list.files and run it with lapply.

This is the code in rmarkdown, the same from script. In my original script I have to use absolute path to get the excel files.

---
title: "Energías"
date: "5/31/2021"
output:
  html_document: default
---


```{r include=FALSE}
rm(list=ls()) # borrar todo lo que haya en memoria         
gc()
options(scipen=999)   # evitar notacion cientifica
options(encoding = "Latin1")

#Instalar librerias necesarias
if(!require(knitr)){install.packages("knitr")} #compilar en html
if(!require(rmdformats)){install.packages("rmdformats")}#compilar en html
if(!require(tidyverse)){install.packages("tidyverse")}
if(!require(readr)){install.packages("openxlsx")}
if(!require(RPostgreSQL)){install.packages("lubridate")}
if(!require(DBI)){install.packages("reshape2")}


library(knitr)
library(rmdformats)
library(openxlsx)
library(tidyverse)
library(lubridate)
library(reshape2)
library(ggplot2)
```




```{r include=FALSE}
# UBICACION ARCHIVOS----
path <- "/Datos/real"
#path <- "C:/Google Drive/CELEC/Despachos_2020/Datos/real" #compu trabajo

# FUNCION PARA CARGAR DE ARCHIVOS----
input_files <- function(path) {
  require(stringr)
  ernc <- read.xlsx(xlsxFile = path,
                   sheet = "Generación Renovable",
                   rows = 5:54,
                   fillMergedCells = TRUE, 
                   colNames = TRUE, detectDates = T)
  names(ernc)[2] <- "HORA"
  names(ernc)[1] <- "Fecha"
  f <- ernc[2,1]
  ernc$Fecha <- f
  ernc <- ernc[-1, 1:(length(ernc)-1)]
  
  should_prefix <- function(x) any(stringr::str_detect(x, 
                                                       "^X[0-9]"))
  apply_prefix <- function(colname, prefix) paste0(prefix, colname)
  
  ernc <- ernc %>%
    dplyr::rename_if(
      map_lgl(names(.), should_prefix),
      ~apply_prefix(., "MVAR_")
    )
  
  ernc <- rename_at(ernc, vars(contains("MVAR")), 
                   ~ paste0(colnames(ernc)[parse_number(.) -1], "_MVAR"))
  ernc <- melt(ernc, id.vars = c("Fecha", "HORA"))
  return(ernc)
}

# PATH COMPLETO DE ARCHIVOS----
paths <- list.files(path=path, 
                    pattern="\\.xlsx$",
                    full.names=TRUE)

# CARGA DE ARCHIVOS A UNA LISTA----
out.lst <- lapply(paths, input_files)

# GENERACION DE DATA FRAME A PARTIR DE LISTA----
ernc <- bind_rows(out.lst, .id = "ID")
ernc$value <- as.numeric(ernc$value)
ernc$Fecha <- as.Date(ernc$Fecha)
ernc$tecnologia <- "renovables"
```
``` {r}
head(ernc, 4)
```

When I use relative path, It doesn't load any file and I get this as output:

> # Energías
> 5/31/2021

> head(ernc, 4)

> A tibble: 0 x 3
> ... with 3 variables: value <dbl>, Fecha <date>, tecnologia <chr>

There's 0 data and 3 variables when it's supposed to be 242592 observations with 6 variables.

And when I use absolute path I get this error:

Quitting from lines 37-85 (Energias.Rmd) 
Error in read.xlsx.default(xlsxFile = path, sheet = "Generación Renovable",  : 
  Workbook has no worksheets
Calls: <Anonymous> ... eval -> lapply -> FUN -> read.xlsx -> read.xlsx.default

All the files have worksheets, as I said the same code works if I run it in a normal script, the problem came with rmarkdown.

I've found some similar errors in other posts but the solutions where changing relative path to absolute path or in the other way. In my case, it doesn't work.

Please some advice.

I´m not sure why in rmarkdown the read.xlsx doesn't recognize the name "Generación Renovable" in the sheet = argument.

I changed the name for the sheet index and it worked, rmarkdown knited my code.

Just to clarify I share the line with the read.xlsx function with the change.

  ernc <- read.xlsx(xlsxFile = path,
                   sheet = 4,         # here goes the sheet index
                   rows = 5:54,
                   fillMergedCells = TRUE, 
                   colNames = TRUE, detectDates = T)

I think it's because the sheet name is in spanish with the special character ´ so I tried passing options(encoding = "UTF-8") also with double "" but nothing worked until I change the sheet name for it´s index.

I hope that someone can tell me how to pass argument sheet = with the name with special characters, just to know.

And I hope this helps to someone else.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.