Import dates from XLSX

General
1

I want to import an XLSX spreadsheet containing also columns that are dates. The problem is to find a 'simple' way to import those dates correctly, since guessing the column types fails to do the job.

I tried to subset the col_types argument to effect only specific columns (those with the dates) and guess all others, but found no solution.
I ended up subsetting the imported spreadsheet into columns with data and all other columns and, in a second step, binding them together using this code structure:

df <- bind_cols(
  read_xlsx("DATA.xlsx",
            range = "C:F",
            col_types = "date"),
  read_xlsx("DATA.xlsx") %>% 
    select(!c(3:6))
  )

This, however, changes the column order, that has to be fixed afterwards.

Is there a simpler solution, like subsetting the col_types argument (and maintain original column order)?

2

Could you provide an example on how the dates are looking in your Excel spreadsheet and how they look once you've imported the .xlsx in R? The {lubridate} package has a lot of useful functions for handling dates, as well as the {readr} functions parse_date and parse_datetime (after you've imported the data into R).

3

Regarding your suggestion, I tried only to import the data and correct the dates (which were 'guessed' as chr and dbl) afterwards using readr and lubridate, but always with errors or incorrect results

Here are example of the original XLSX and of tibbles after import and after correction as mentioned above as screenshots

4
  • Original data
    XLSX_original_sample
5
  • after import
    df_imported_sample
6

-after correction as mentioned above
df_imported_sample