Removing Password from an Excel file and top 5 rows
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Labels:
- Developer
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
thank you. though the code gives me an error : Error: R (1): Error in output[6, ] : incorrect number of dimensions
