How to open excel with a password?

[joanq]

Hi everyone!

Could you please help me with opening by Input an excel that has a password?

I would like to use workflow in Alteryx for a number of excels with password, so I prefer possibility to remove password/or possibility to read such file in Alteryx, rather than remove password in Excel every time I want to run workflow.

I know there are similar topics to this, but I don't know how to run macro in Alteryx.

Also, there is one advice to use Directory tool and then formula and R.

But I have no idea what to put in the formula tool to open excel.

I would be very grateful, I'm working on encrypted excels all the time.

[RishiK · Accepted solution]

@joanq try the attached workflow.

Input your password protected Excel sheet, in the formula tool update your password (now the workflow has it as "alteryx"), and run.

The browse tool will show you the data in your Excel sheet.

[joanq]

thank you, can you please attached screen shots of this workflow and formula tool especially?

I can't open your Excel.yxzp file becasue I have older version of alteryx.

I'm using Designer x64.

[Alexandra_Pops]

Hellooo, RishiK! 

Thank you so very much for your answer on this topic! 

Nevertheless, I seem to be hitting a road block since when I click on Run, it flows to the R tool where it stays at 50% and this thing happens: these characters come one after another in a loop \ | / -                          no error, no nothing. I attach printscreen.

Do you think you could help with this? 

P.S. As I was writing this, suddenly my excel file opened on my computer, outside Alteryx and it prompted the little window for the password. The percentage in Alteryx stayed at 50% still looping through those characters. 

Thank you so very much! 

Pops 

[RishiK]

@Alexandra_Pops did you manage to solve this?

[TylerNa]

@RishiK thank you for providing the details and sample workflow. I've followed your example but run into a "There was an error in WriteYXDBStreaming" error. Any thoughts on how to resolve this? I'm running Alteryx 2020.3.5

[RishiK]

@TylerNa this error may be resolved as per the below:

https://community.alteryx.com/t5/Alteryx-Server-Discussions/WriteYXDBStreaming-Error-using-R-node/td...

[TylerNa]

I wanted to share my final solution that ended up working for me incase it helps anyone else in the future. The ultimate configuration in the R Tool was.

library("excel.link")

df <- read.Alteryx("#1", mode="data.frame")
filename <- as.character(df[1,"FullPath"])
excel_pwd <- as.character(df[1,"excel_password"])

excel_data <- xl.read.file(filename, password = excel_pwd, write.res.password=excel_pwd)

output <- lapply(excel_data, as.character)

output <- as.data.frame(output)

write.Alteryx(output, 1)

[harryvp]

Thanks for this! I was scratching my head for a while, as I was getting an error relating to writing the output. The final few lines of code worked for me!

Thank you for sharing.

[Smriti2107]

The below solution works for MAC & Windows

Python based solution - 

from ayx import Package

from ayx import Alteryx
Package.installPackages(['pandas','xlwings'])

import pandas as pd
import xlwings as xw

//place your path here

PATH = r'C:\Users\smrit\OneDrive\Documents\Alteryx\Book2.xlsx'

wb = xw.Book(PATH,password='12345')

// I renamed my sheet1 as sample
sheet = wb.sheets['sample']

df = sheet[sheet.used_range.address].options(pd.DataFrame, index=False, header=True).value
df

Please check the attached workflow -