How to import many xslx files to R? (In one xlsx file there are many sheets and I need only one)

0
votes

I'm new here and I don't know how this site works. If I make mistakes, sorry. Soooo I have 23 xlsx files with many sheets in them.

  1. I have to create dataset which contains all of those files but with only one sheet. Columns and names of the sheets are the same.

  2. I have to bind them by rows. If anyone know how to do it, I will be very grateful.

    file.list <-list.files("D:/Profile/name/Desktop/Viss/foldername",pattern=".xlsx")

    df.list <- lapply(file.list, read_excel) Error: path does not exist:

    df <- rbindlist(df.list, idcol = "id")

I don't know where to put the extract of this one sheet and I don't know what to write in idcol="".

1
rexcelcsv
"I'm new here and I don't know how this site works. If I make mistakes, sorry." Rather than pre-emptively apologizing, the correct approach is to find out how the site works first. Checkout stackoverflow.com/tour - L. Scott Johnson

1 Answers

0
votes

I think your approach is correct, but you should use the full path in file.list <-list.files("D:/Profile/name/Desktop/Viss/foldername",pattern=".xlsx", full.names=TRUE)

EDIT: You should use pattern="\\.xlsx" in

list.files("D:/Profile/name/Desktop/Viss/foldername",pattern="\\.xlsx", full.names=TRUE)

EDIT2: You can always see any function help by running ? followed by your function name like ?rbindlist, or in RStudio, pressing F1 on the function name. The idcol parameter should be TRUE or FALSE, in your case, FALSE probably.

  • idcol Generates an index column. Default (NULL) is not to. If idcol=TRUE then the column is auto named .id. Alternatively the column name can be directly provided, e.g., idcol = "id". If input is a named list, ids are generated using them, else using integer vector from 1 to length of input list. See examples.*

EDIT3 if you want to specify the sheet name you can use

lapply(file.list, function(x) read_excel(x, sheet="sheetname"))