1

I have 13 xlsx files in a folder that have the same columns. So i'd like to open them in a row in R and append them together. Two of the columns (Fecha 1, Fecha 2) contain dates. Fecha 1 is in format yyyy-mm-dd and fecha 2 in the format dd/mm/yyyy.

Here is what i did :


setwd("/Volumes/WD_BLACK/Data/V")

library(readxl)
file.list <- list.files(pattern='*.xlsx')
df.list <- lapply(file.list, read_excel)

df <- bind_rows(df.list)

I get the following error message :

Error : Column Fecha 2 can't be converted from POSIXct, POSIXt to character

So i imported directly one of my file and typed :

typeof(v1$`Fecha 2`)

[1] "double"

The type is the same for Fecha 1.

Does someone know how i can get rid of this error message please, as i work with a list ?

(I underline that i cannot use xlsx package, it doesn't work anymore i think i have issues with Java as i cannot install rJava too.)

katdataecon
  • 185
  • 8
  • you might try setting the `read_excel(..., col_types=)` argument to character or something less specific than POSIX. The error arrises from read excel guessing a certain files Fecha column was a date and another files Fecha column was guesses as not a data, so when you go to stack those two intermediate data frames, dplyr complains about the type mis-match – Nate Apr 29 '21 at 15:54
  • Is there a way to write the col_types for only this two columns for example ? Or i'm obligated to set all of my variables as character ? – katdataecon Apr 29 '21 at 16:05
  • you can't unless you do a second step like `read_excel() %>% mutate(Fecha = as.character(Fecha)`. I usually just say `col_types='character'` then coherce types out when I need them for something else – Nate Apr 29 '21 at 16:08
  • Okay i'll try and tell you, thanks ! – katdataecon Apr 29 '21 at 16:09
  • FWIW I usually make a function like `my_read_in_func()` that does all of the file level steps like the secondary mutate, so I can still use the lapply %>% bind_rows pattern you were already using – Nate Apr 29 '21 at 16:15
  • My bad honestly i'm not enough advanced yet in programming skills to create a function by my own you know. But i'll try anyway, i have no choice lol ! – katdataecon Apr 29 '21 at 16:21
  • you're good, learning code is hard (but worth it). It would look something like this: `my_func <- function(x){read_excel(x) %>% mutate(Fecha = as.character(Fecha))}; lapply(file.list, my_func)` – Nate Apr 29 '21 at 18:39
  • Hello, i tried your code, but i have the following error message : Error in eval(cols[[col]], .data, parent.frame()) : object 'Fecha' not found – katdataecon Apr 30 '21 at 13:26
  • yea you need to figure out what the actuall column name is, it saying Fecha isnt found – Nate Apr 30 '21 at 19:59

0 Answers0