Using R to Read in Password Protected XLSX

[aaronindm]

Having difficulty reading some fields from a password protected XLSX file using the R Tool.  Please see attached workflow and the spreadsheet it is attempting to read from.

Attempt #1 Read in Entire "Open" Tab: Failed with "There was an error in WriteYXDBStreaming"

Attempt #2 Read "Open" Tab starting with Column U: Failed with "There was an error in WriteYXDBStreaming"

Attempt #3 Read "Open" Tab starting with Column V: WORKS

Attempt #4 Read "Column" Tab (all dates): WORKS

It appears that the failure comes with the date field(s) on the "Open" tab, though the "Column" tab is all dates.

Spreadsheet password is "zap"

[PaulNo]

Hi @aaronindm,

Could you please verify your encrypted file? When I try to open it with excel (with password "zap"), I receive an error message ("The file is corrupt and cannot be opened")

When I run your workflow with a custom file (same worksheets and password as yours), it works fine.

Thanks,

PaulN

[aaronindm]

@PaulNo trying again - this one has the password removed because I got this when I tried to upload the unzipped password protected file.

[aaronindm]

You will have to add back in the password at the view and modify level.

[AndrewKramer]

The dates are the issue here. We don't support the following format when writing back to Alteryx:

$ Forms Rec'd                    : POSIXct, format: "2019-08-28"

I recommend converting to character before writing back. For example:

excel_data$`Next Review Date` <- as.character(excel_data$`Next Review Date`)

The workflow runs without issue for me after these changes.

[aaronindm]

@AndrewKramer thank you!  I had tried unsuccessfully to use as.character but will head down that path again.

My data table has multiple dates - is there a way to just import all fields as characters?

[AndrewKramer · Accepted solution]

We don't have this out of the box, but you can do it with a few lines of R code before writing the data back out to Alteryx:

date_cols <- colnames(excel_data)[lapply(excel_data,function(x) class(x)[1]) == "POSIXct"]
excel_data[date_cols] <- lapply(excel_data[date_cols], as.character)