In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Removing Password from an Excel file and top 5 rows

SR
8 - Asteroid

HI All,

 

I have a password encrypted file and the file also contains 5 rows of junk information which i need to remove before the 6th row should be the column headers.

 

I am using the R to remove the encryption. how ever my R code is failing and i am not sure why.  i was hoping the code would output everything with the junk rows decrypted and then i would clean up the top 5 rows and headers using dynamic rename.

 

here is the code i am using:

 

 

#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_data <- xl.read.file(filename, password = excel_pwd, write.res.password=excel_pwd)

 

output <- lapply(excel_data, as.character)

 

output <- as.data.frame(output)

write.Alteryx(output, 1)

 

not sure what i am missing or needs to be done to get the correct output

 

The file screenshot is attached. as you can see the top 5 rows r just random information that needs to be removed and then row 6 is the actual headers of the file once encrypted

 

 

 

 

 

2 REPLIES 2
Raj
16 - Nebula

@SR 
try using this in R

# Load required libraries
library("excel.link")

# Read the metadata from Alteryx
df <- read.Alteryx("#1", mode="data.frame")

# Extract file path and password from metadata
filename <- as.character(df[1,"FullPath"])
excel_pwd <- as.character(df[1,"excel_password"])

# Read Excel file with encryption
excel_data <- xl.read.file(filename, password = excel_pwd, write.res.password=excel_pwd)

# Convert Excel data to character
output <- lapply(excel_data, as.character)

# Convert to data frame
output <- as.data.frame(output)

# Remove the first 5 rows
output <- output[-c(1:5), ]

# Set the column headers using the 6th row
colnames(output) <- unlist(output[6, ])

# Remove the row used as headers
output <- output[-6, ]

# Write the processed data to Alteryx
write.Alteryx(output, 1)

SR
8 - Asteroid

thank you. though the code gives me an error : Error: R (1): Error in output[6, ] : incorrect number of dimensions

Labels
Top Solution Authors