R By using reading a password protected XLSX

[Ajith1]

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)

[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.

[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.

[pedrodrfaria · Accepted solution]

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.

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

[pedrodrfaria]

Hi @Ajith1 

Please open a new discussion post if you have additional questions. This will make sure your questions will be answered.

See below for the workflow accomplishing the task.

[Ajith1]

@pedrodrfaria 

Thank you for the quick response.

Sure.

But it will depend on the earlier conversation In R Code it will be taking input up to Blank columns and blank rows. I need all data could you please suggest it to me.

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 based on the R code

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

[W1ll3m]

What is the correct R-code syntax to specify a specific spreadsheet range, e.g. A7-Z100, to be read into alteryx?