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