R Code based excel file reading

[Ajith1]

Hi all

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

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

[Ajith1]

Hi all

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

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

[Ajith1]

Hi@PaulNo 

Could you please find out my Errors.

I think I need to be Install Python install the libraries?

[PaulNo]

Hi @Ajith1,

Could you please copy the full error message from the Results window and paste it here? 

Kind regards,

PaulN

[Ajith1]

@PaulNo 


Python (1) ---------------------------------------------------------------------------¶CalledProcessError Traceback (most recent call last)¶<ipython-input-2-8f3aae3fe254> in <module>¶----> 1 Package.installPackages(['msoffcrypto-tool','xlrd','openpyxl'])¶c:\program files\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\site-packages\ayx\Package.py in installPackages(package, install_type, debug)¶ 200 print(pip_install_result["msg"])¶ 201 if not pip_install_result["success"]:¶--> 202 raise pip_install_result["err"]¶c:\program files\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\site-packages\ayx\Utils.py in runSubprocess(args_list, debug)¶ 118 ¶ 119 try:¶--> 120 result = subprocess.check_output(args_list, stderr=subprocess.STDOUT)¶ 121 if debug:¶ 122 print("[Subprocess success!]")¶c:\program files\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\subprocess.py in check_output(timeout, *popenargs, **kwargs)¶ 409 kwargs['input'] = '' if kwargs.get('universal_newlines', False) else b''¶ 410 ¶--> 411 return run(*popenargs, stdout=PIPE, timeout=timeout, check=True,¶ 412 **kwargs).stdout¶ 413 ¶c:\program files\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\subprocess.py in run(input, capture_output, timeout, check, *popenargs, **kwargs)¶ 510 retcode = process.poll()¶ 511 if check and retcode:¶--> 512 raise CalledProcessError(retcode, process.args,¶ 513 output=stdout, stderr=stderr)¶ 514 return CompletedProcess(process.args, retcode, stdout, stderr)¶CalledProcessError: Command '['c:\\program files\\alteryx\\bin\\miniconda3\\envs\\jupytertool_venv\\python.exe', '-I', '-m', 'pip', 'install', 'msoffcrypto-tool', 'xlrd', 'openpyxl']' returned non-zero exit status 1.¶

[PaulNo]

Thanks, @Ajith1,

Thanks for sharing.

Since the message does not clearly state the origin of the error, could you please follow these steps:

1. Open a command prompt (cmd.exe) as Administrator 

2. Enter the following command and press Enter

"c:\program files\alteryx\bin\miniconda3\envs\jupytertool_venv\python.exe" -I -m pip install msoffcrypto-tool xlrd openpyxl

3. Take a screenshot (if short enough) or copy the output and paste it here, please.

I appreciate your patience with this. The workaround I suggested was expected to be straightforward. Unfortunately, since environments are different (versions of Alteryx Designer, Windows, configuration, etc.), it could make things a bit more complicated.

Thanks,

PaulN