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)

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

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

[danilang]

Hi @Ajith1 

Use the R code in a R Tool to open the file and then add @echuong1 cleansing workflow after it, so that your workflow looks like this

Dan

[Ajith1]

Hi @danilang 

It can't be working could you please find attached screenshots FYR.

[PaulNo]

Dear @Ajith1,

Thank you for your question.

Unfortunately, the library excel.link will not allow you to load empty columns (aka columns with no names). As per package documentation "Orphaned rows/columns located apart from the main data will be ignored." (source: https://cran.r-project.org/web/packages/excel.link/excel.link.pdf, page 22).

I would have gladly suggested XLConnect as an alternative package (https://cran.r-project.org/web/packages/XLConnect/vignettes/XLConnect.pdf) but I was not able to make it work on Windows.

If you are happy with an alternative solution, replace the R tool with a Python tool and copy and paste the following code. It uses the Python library msoffcrypto-tool (https://pypi.org/project/msoffcrypto-tool/) to decrypt the file.

The following example is provided as-is. It expects 3 input fields: FullPath, excel_password and Sheet_Name. 

from ayx import Package
from ayx import Alteryx

# Install libraries to load unprotected file
Package.installPackages(['msoffcrypto-tool','xlrd','openpyxl'])

# Example adapted from https://github.com/nolze/msoffcrypto-tool, Basic usage (in-memory)
import msoffcrypto
import io
import pandas as pd

with open(input["FullPath"][0],"rb") as excel_file:
    file = msoffcrypto.OfficeFile(excel_file)

    # Use password
    file.load_key(password=input["excel_password"][0])

    decrypted = io.BytesIO()
    file.decrypt(decrypted)

    df = pd.read_excel(decrypted,sheet_name=input["Sheet_Name"][0],engine="openpyxl")
    
Alteryx.write(df,outgoing_connection_number=1)

This will return the following:

Then, use a Data Cleansing tool, as per @echuong1's suggestion, and you should have your result.

Hope this helps!

PaulN

[Ajith1]

Hi @PaulNo 

Cold you please help I'm getting the below Error from Python tool.

Error: Python (6): ---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
<ipython-input-1-8c5ce3816e06> in <module>
1 # Example adapted from https://github.com/nolze/msoffcrypto-tool, Basic usage (in-memory)
----> 2 import msoffcrypto
3 import io
4 import pandas as pd
5
ModuleNotFoundError: No module named 'msoffcrypto'

[PaulNo]

Hi @Ajith1,

From your screenshot, you are missing the part of the screenshot to install the required Python libraries.

Could you please add the following lines:

from ayx import Package
from ayx import Alteryx

# Install libraries to load unprotected file
Package.installPackages(['msoffcrypto-tool','xlrd','openpyxl'])

before:

# Example adapted from https://github.com/nolze/msoffcrypto-tool, Basic usage (in-memory)
import msoffcrypto

So, the full script should look like this:

from ayx import Package
from ayx import Alteryx

# Install libraries to load unprotected file
Package.installPackages(['msoffcrypto-tool','xlrd','openpyxl'])

# Example adapted from https://github.com/nolze/msoffcrypto-tool, Basic usage (in-memory)
import msoffcrypto
import io
import pandas as pd

with open(input["FullPath"][0],"rb") as excel_file:
    file = msoffcrypto.OfficeFile(excel_file)

    # Use password
    file.load_key(password=input["excel_password"][0])

    decrypted = io.BytesIO()
    file.decrypt(decrypted)

    df = pd.read_excel(decrypted,sheet_name=input["Sheet_Name"][0],engine="openpyxl")
    
Alteryx.write(df,outgoing_connection_number=1)

Note that this will require write permissions to your Alteryx directory.

Let me know if this helps.

Kind regards,

PaulN

[Ajith1]

Hi @PaulNo 

Could you please attached my Requirement file.

I need to be load a file Row 18 before Row data  I don't want it

Note: Source file I have a Password (Encrypted File)

Please Suggest to me........How can I install Python libraries

[PaulNo]

Dear @Ajith1,

Unfortunately, I am not sure to understand your question.

I have attached an example (Excel file and workflow) for your convenience. 

The Python tool contains all the instructions to install the libraries. Feel free to look at How To: Use Alteryx.installPackages() in Python tool for additional details.

As mentioned previously, you may need to run Designer as Administrator to install any Python modules if you have an Admin version of Alteryx Designer. This would be only required during the installation.

Should you face any specific problems or error messages, or should you have any questions, feel free to post them so we could help.

Kind regards,

PaulN