Can't combine excel sheets together

[kuttan98]

Hi,
I have an excel workbook where there are 70 sheets inside. I want to merge them all together. I have used the code below for this. But it throws up an error regarding class of the variable.
How can I solve it?

your_path<-"C:/Users/NITHIN PRADEEP/Downloads/School_wise_assessment_sheet.xlsx"
sheets<-excel_sheets(your_path)

map_dfr(.x = sheets, .f = ~read_excel(your_path, sheet = .x) %>% mutate(sheet = .x)) %>% 
  writexl::write_xlsx("Merged_master.xlsx")

Error: Can't combine `..1$dise_code` <character> and `..35$dise_code` <double>.
Run `rlang::last_error()` to see where the error occurred

[nirgrahamuk]

analyse a single sheet to understand the column names, and column types.
Then you can amend this script to use read_excel parameters col_names to pass a vector of the column names, and col_types the corresponding column types you determined

col_types
Either NULL to guess all from the spreadsheet or a character vector containing one entry per column from these options: "skip", "guess", "logical", "numeric", "date", "text" or "list". If exactly one col_type is specified, it will be recycled. The content of a cell in a skipped column is never read and that column will not appear in the data frame output. A list cell loads a column as a list of length 1 vectors, which are typed using the type guessing logic from col_types = NULL, but on a cell-by-cell basis.

[nirgrahamuk]

in the case of dise_code you would use 'text' because at least on one of the sheets it contains text

[kuttan98]

Thanks for this. I have brought changes to read_excel function as below. But it again gives an error.

your_path<-"C:/Users/NITHIN PRADEEP/Downloads/School_wise_assessment_sheet (1).xlsx"
sheets<-excel_sheets(your_path)

map_dfr(.x = sheets, .f = ~read_excel(your_path, sheet = .x,col_names = c("school_code","school_name","dise_code",
                                                                          "selectedteachername","child_name","random_id","child_class"),
                                      col_types = "cccccci" %>% mutate(sheet = .x))) %>% 
  writexl::write_xlsx("Merged_master.xlsx")

Error in UseMethod("mutate") : 
  no applicable method for 'mutate' applied to an object of class "character"

[nirgrahamuk]

First some general coding advse. Try to use linebreaks to your advantage to prevent code from scrolling rightwards a long way.

It can be a lot more difficult to read code when presented that way.

As to your issue, I believe you made a mistake with the positioning of your brackets

map_dfr(
  .x = sheets,
  .f = ~ read_excel(your_path,
    sheet = .x,
    col_names = c(
      "school_code",
      "school_name",
      "dise_code",
      "selectedteachername",
      "child_name",
      "random_id",
      "child_class"
    ),
    col_types = "cccccci"
  ) %>%
    mutate(sheet = .x)
) %>%
  writexl::write_xlsx("Merged_master.xlsx")

[kuttan98]

Oops.. I didn't observe the bracket thing.
I revised it but it shows "illegal column type".

your_path<-"C:/Users/NITHIN PRADEEP/Downloads/School_wise_assessment_sheet (1).xlsx"
sheets<-excel_sheets(your_path)

map_dfr(
  .x = sheets,
  .f = ~ read_excel(your_path,
                    sheet = .x,
                    col_names = c(
                      "school_code",
                      "school_name",
                      "dise_code",
                      "selectedteachername",
                      "child_name",
                      "random_id",
                      "child_class"
                    ),
                    col_types = "cccccci"
  ) %>%
    mutate(sheet = .x)
  )%>%
  writexl::write_xlsx("Merged_master.xlsx")

Error: Illegal column type: 'cccccci' [1]

[nirgrahamuk]

oh, I think you were thinking of readr's "ccccii" type notation, but this is readxl, which doesnt have that.
in your case try
col_types = c(rep("text",6),"numeric")

[kuttan98]

thank you for this, it works now.