R By using reading a password protected XLSX

Hi Experts,

Could you please help me R Langauge code for the attached Code

Password :

alteryx

I need a where ever we want to change the R code why means, I need a change my input file.

could you please highlight the below code where we need a change for the path, file name, and password

# install.packages(c("excel.link"),repos='https://cran.revolutionanalytics.com/',dependencies = TRUE)
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)

write.Alteryx(excel_data, 1)

Excel.yxmd

Capture.PNG

Developer

Developer Tools

Input

R Tool

Accepted answers

pedrodrfaria

HI @Ajith1

You need to install the R Package for it. I've attached a workflow below to do this. Please follow these steps to be able to install it.

1. Open the workflow and run it as an Analytic App.

2. Type out the name of the library you need to install, which is excel.link

3. This will let you know it was installed. Close it out and run the password locked workflow again and you should be able to run it without problems.

Please close the discussion topic if your questions were answered by marking the correct answer in the post.

Pedro.

Install R Packages.yxwz

All comments

pedrodrfaria

Hi @Ajith1

The code is right, you do not need to change it in order to use it.

In order to update the input file you just need to update the input file inside of the data input or the directory tool. To update the password, use the formula tool.

If you want to change the code itself:

filename <- as.character(df[1,"FullPath"]) -> Update the "FullPath" variable

excel_pwd <- as.character(df[1,"excel_password"]) -> Update the "excel_password" variable

Please update the discussion post to finished and mark the response as the answer to the post if your questions were answered. Feel free to reach out if you have additional questions.

Pedro.

Screenshot 2020-12-30 144929.png

Screenshot 2020-12-30 144948.png

Ajith1

@pedrodrfaria

Thanks for the Response.
I'm getting These errors from the R tool. could you please suggest to me.

R (2) Error in library("excel.link") : there is no package called 'excel.link'
R (2) Execution halted
R (2) The R.exe exit code (1) indicated an error.

Capture.PNG

pedrodrfaria

HI @Ajith1

You need to install the R Package for it. I've attached a workflow below to do this. Please follow these steps to be able to install it.

1. Open the workflow and run it as an Analytic App.

2. Type out the name of the library you need to install, which is excel.link

3. This will let you know it was installed. Close it out and run the password locked workflow again and you should be able to run it without problems.

Please close the discussion topic if your questions were answered by marking the correct answer in the post.

Pedro.

Install R Packages.yxwz

Ajith1

Hi @pedrodrfaria

Could you please suggest to me below R code

Input :

Name	sal	Location
Ajith	raj	jskf

weqw	ewwq	eqw
ewqe	e	wew

But it will be returns

Name	sal
Ajith	raj

I have a few blank columns and rows

Output like the need for me

Name	sal	Location
Ajith	raj	jskf
weqw	ewwq	eqw
ewqe	e	wew

# install.packages(c("excel.link"),repos='https://cran.revolutionanalytics.com/',dependencies = TRUE)
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_sheet <- as.character(df[1,"Sheet_Name"])

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

write.Alteryx(excel_data, 1)

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167