Reading a file with multiple passwords into R

[pbiondi]

Is there a way to set multiple password arguments for xl.read.file?

[gdemin]

Could you describe use case for multiple passwords for single Excel file? As far as I know single Excel file has single password.

[pbiondi]

Hi Gregory,

The file is locked by two different departments for my project. It is the same password for both locks.

The first is for access to the file, and the second decided whether or not I will have the ability to edit the file vs. read-only

[pbiondi]

Gregory-

To try and help you visualize the problem just a little better: When I have the password set in the arguments, as well as excel.visible = TRUE, each excel file in the directory pops up and closes on its own (the password argument giving me access), but there are two files that stop the code until I re-enter the password.

Here is my code:

create file list

for (i in 1:length(data.files)){ total_data[[i]] = xl.read.file(data.files[i], header = FALSE, password = real_file_password, top.left.cell = data_location, excel.visible = TRUE) total_data[[i]][total_data[[i]]==" "] = NA }

In this case, real_file_password and data_location are environment variables.

[gdemin]

Hi! Try the quick workaround below. If it works I will include it in the future version of excel.link. Second password should be provided as write.res.password argument.

xl.read.file2 = function (filename, header = TRUE, row.names = NULL, col.names = NULL, 
          xl.sheet = NULL, top.left.cell = "A1", na = "", password = NULL,
          write.res.password = NULL,
          excel.visible = FALSE) 
{
    xl_temp = COMCreate("Excel.Application", existing = FALSE)
    on.exit(xl_temp$quit())
    xl_temp[["Visible"]] = excel.visible
    xl_temp[["DisplayAlerts"]] = FALSE
    if (isTRUE(grepl("^(http|ftp)s?://", filename))) {
        path = filename
    }
    else {
        path = normalizePath(filename, mustWork = TRUE)
    }
    passwords =paste(!is.null(password), !is.null(write.res.password), sep = "_") 
    xl_wb = switch(passwords, 
                   FALSE_FALSE = xl_temp[["Workbooks"]]$Open(path),
                   TRUE_FALSE = xl_temp[["Workbooks"]]$Open(path, 
                                                            password = password
                                                            ),
                   FALSE_TRUE = xl_temp[["Workbooks"]]$Open(path, 
                                                           writerespassword = write.res.password
                   ),
                   TRUE_TRUE = xl_temp[["Workbooks"]]$Open(path, 
                                                            password = password, 
                                                            writerespassword = write.res.password
                   )

    )
    if (!is.null(xl.sheet)) {
        if (!is.character(xl.sheet) & !is.numeric(xl.sheet)) 
            stop('Argument "xl.sheet" should be character or numeric.')
        sh.count = xl_wb[["Sheets"]][["Count"]]
        sheets = sapply(seq_len(sh.count), function(sh) xl_wb[["Sheets"]][[sh]][["Name"]])
        if (is.numeric(xl.sheet)) {
            if (xl.sheet > length(sheets)) 
                stop("too large sheet number. In workbook only ", 
                     length(sheets), " sheet(s).")
            xl_wb[["Sheets"]][[xl.sheet]]$Activate()
        }
        else {
            sheet_num = which(tolower(xl.sheet) == tolower(sheets))
            if (length(sheet_num) == 0) 
                stop("sheet ", xl.sheet, " doesn't exist.")
            xl_wb[["Sheets"]][[sheet_num]]$Activate()
        }
    }
    if (is.null(row.names) && is.null(col.names)) {
        if (header) {
            col.names = TRUE
            temp = excel.link:::xl.read.range(xl_temp[["ActiveSheet"]]$range(top.left.cell), 
                                 na = "")
            row.names = is.na(temp) || all(grepl("^([\\\\s\\\\t]+)$", 
                                                 temp, perl = TRUE))
        }
        else {
            row.names = FALSE
            col.names = FALSE
        }
    }
    else {
        if (is.null(row.names)) 
            row.names = FALSE
        if (is.null(col.names)) 
            col.names = FALSE
    }
    top_left_corner = xl_temp$range(top.left.cell)
    xl.rng = top_left_corner[["CurrentRegion"]]
    if (tolower(top.left.cell) != "a1") {
        bottom_row = xl.rng[["row"]] + xl.rng[["rows"]][["count"]] - 
            1
        right_column = xl.rng[["column"]] + xl.rng[["columns"]][["count"]] - 
            1
        xl.rng = xl_temp$range(top_left_corner, xl_temp$cells(bottom_row, 
                                                              right_column))
    }
    excel.link:::xl.read.range(xl.rng, drop = FALSE, na = na, row.names = row.names, 
                  col.names = col.names)
}

[pbiondi]

It works, thank you SO much!

Cheers, Peter

[gdemin]

Fixed in version 0.9.8