Reading the data from each sheet from the password protected excel file.

Question

Hi all,

I have an excel file which has two sheets in it. This file is being protected with a password = 0213.

I need to read the data from the each sheet present in the excel file.

I'm able to read only from sheet1. But sometimes, I get error as 'R (3) There was an error in WriteYXDBStreaming'.

I'm unable to read the data from Sheet2.

I tried to do the casting even then it didn't work.

I need to extract the data from the two sheets separately. Sheet1 data should come at 1 section of R-Tool and Sheet2 data should come at 2 section of R-Tool.

How do we do this within the same code? Please check the code completely.

@Harbinger

Kindly help and provide the solution.

Thank you.

Best Regards,

Teja.

reading data from password protected file..yxzp

JohnJPS · Answer

@stj1120

Sorry, as noted I no longer have Desktop Designer so cannot assess this hands-on. It sounds like an issue with the data in the 2nd Sheet; you have "as.character" already, so maybe also look for tips on getting the encoding (e.g. utf-8 or maybe ascii) as well.

You can also try experimenting in raw R outside of Alteryx to see if you can gain any insight that way.

Cheerd,

John

stj1120 · Answer

Hi @JohnJPS

Thank you for your prompt response.

I used the below code today to read the worksheets present in the protected excel file. Alteryx is reading the excel password '0213' as string.

The first sheet worksheet is displaying in the 1-ouput section of R-tool. But, the second sheet data is not displaying in the 2-ouput section of R-tool.

# The set of possible repositories to use
options(repos = c(CRAN = "http://mran.revolutionanalytics.com/snapshot/2015-06-20"))

#Setting the library path

.libPaths("C:/Users/YBTQ/OneDrive - NN/R/win-library/3.6")

message("Using library: ", .libPaths()[1])

#Loading the library
library("excel.link")

#Read the excel file

df <- read.Alteryx("#1", mode="data.frame")
filename <- as.character(df[1,"FullPath"])
excel_pwd <- as.character(df[1,"excel_password"])

#Reading the excel file with the respective sheet names
assy <- xl.read.file(filename,xl.sheet="Assy-2021", password = excel_pwd,
write.res.password=excel_pwd)

pack <- xl.read.file(filename,xl.sheet="Pack-2021", password = excel_pwd,
write.res.password=excel_pwd)

#Setting the sheet data as a data frame and downloading the data in Alteryx
assyOutput <- as.data.frame(assy)
write.Alteryx(assy, 1)

packOutput <- as.data.frame(pack)
write.Alteryx(pack, 2)

The error occurs at last write function for the pack.

Please check and help me with the solution.

Thank you.

Best Regards,

Teja.

JohnJPS · Answer

@stj1120,

I no longer have a copy of Designer at my disposal, however I would have a number of questions:

* Are the filename and pwd being pulled into R correct?* e.g. the pwd looks like "0213" ... is R thinking it's an integer and dropping the leading zero?

* I'm assuming that's working... since the error is in WriteYXDBStreaming...* meaning you did read the file... you're getting your error in write.Alteryx(df, 1)

* You say it can read Sheet1... but sometimes not?* That suggests an issue with the data... unless it sometimes fails and sometimes succeeds with the exact same file
* In which case I wouldn't know... maybe try messing with character encodings? utf-8 or etc...?

PS, I don't think you need the write.res.password argument when reading the Excel file; that's needed when you write it... I don't think that is causing your issue though.