Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

How to open excel with a password?

joanq
7 - Meteor

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.

18 REPLIES 18
RishiK
Alteryx
Alteryx

@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
7 - Meteor

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
6 - Meteoroid

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
Alteryx
Alteryx

@Alexandra_Pops did you manage to solve this?

TylerNa
10 - Fireball

@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

 

R Error Reading Protected Excel.png

RishiK
Alteryx
Alteryx
TylerNa
10 - Fireball

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
5 - Atom

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
5 - Atom

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 - 

 

Labels