Get Inspire insights from former attendees in our AMA discussion thread on Inspire Buzz. ACEs and other community members are on call all week to answer!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Using R to Read in Password Protected XLSX

aaronindm
8 - Asteroid

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"

6 REPLIES 6
PaulNo
10 - Fireball

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")

 

PaulNo_0-1592502388025.png

 

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

 

Thanks,

 

PaulN

aaronindm
8 - Asteroid

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

 

aaronindm_0-1592506127357.png

 

 

 

aaronindm
8 - Asteroid

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

AndrewKramer
Alteryx Alumni (Retired)
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
8 - Asteroid

@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
Alteryx Alumni (Retired)

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)

 

 

 

 

 

 

 

 

 

 

Labels