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.

R Code based excel file reading

Ajith1
8 - Asteroid

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 :

Namesal Location
Ajithraj jskf
    
weqwewwq eqw
ewqee wew

 

 

But it will be returns based on the R code

 

Namesal
Ajithraj

I have a few blank columns and rows

 

Output like the need for me 

NamesalLocation
Ajithrajjskf
weqwewwqeqw
ewqeewew

 

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

13 REPLIES 13
Ajith1
8 - Asteroid

Hi@PaulNo 

Could you please find out my Errors.

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

PaulNo
10 - Fireball

Hi @Ajith1,

 

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

 

Kind regards,

 

PaulN

Ajith1
8 - Asteroid

@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
10 - Fireball

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

Labels