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 LocationAjithraj jskf weqwewwq eqwewqee wew But it will be returns based on the R code NamesalAjithraj I have a few blank columns and rows Output like the need for me NamesalLocationAjithrajjskfweqwewwqeqwewqeewew # 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) Input.PNG Output.PNG

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

@PaulNo Python (1) ---------------------------------------------------------------------------¶CalledProcessError Traceback (most recent call last)¶ in ¶----> 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.¶

Hi @Ajith1, Could you please copy the full error message from the Results window and paste it here? Kind regards, PaulN

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)

Input.PNG

Output.PNG

Developer

Developer Tools

R Tool

Accepted answers

All comments

echuong1

Does this need to be achieved with R, or will Designer functionality work as well?

If the latter, you can use the data cleansing. At the top there are selections to remove columns and rows that are completely null. Keep in mind this will only work if the values are null, not empty. I added a multifield tool to change any instance of empty to null prior to catch everything.

Hope this helps!

New Workflow1.yxmd

Ajith1

@echuong1

Thank you for the response.

I need to do this thing by using R code why means I have a source Excel file encrypted with a password then I need to use R code.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/R-By-using-reading-a-password-protected-XLSX/m-p/687784#M162641

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335