2022.1.1.30569 Patch Release Update

The 2022.1.1.30569 Patch/Minor release has been removed from the Download Portal due to a missing signature in some of the included files. This causes the files to not be recognized as valid files provided by Alteryx and might trigger warning messages by some 3rd party programs. If you installed the 2022.1.1.30569 release, we recommend that you reinstall the patch.

Alteryx Designer Discussions

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

Extract Hyperlink

jdmalagon
6 - Meteoroid

Hello,

 

I have an Excel file and in the first column, I have text with a hyperlink, the macro I use it creates a column next to it and then extracts the HL from the text.

How can I do this on Alteryx?, I have tried R but it's not working. 

Thank you!

14 REPLIES 14
BenMoss
ACE Emeritus
ACE Emeritus

Is your input source an xlsx file? .xlsx files are actually a collection of different files which Excel puts together upon opening. In contrast, tools such as python, R and Alteryx will just fetch the data layer from the file.

 

My understanding is that when you input an .xlsx file into Alteryx it removes any formatting contained in the doc, such as conditional formatting, hyperlinks, text size and so on, as it is a visual layer.

However, according to the R script below (which performs this task), if we take our xlsx file and convert the extension to .zip, we can then extract the hyperlinks from the xml file for the sheet that exists within that.

Alteryx allows you to import .zip files and then you can select the sheet element, as per the script below, before performing some parsing which should pull out the element you are looking for.

The reason this is much easier in VBA is because its developed by microsoft of course and they know how there tool works and have built commands to deal with this.

 

Ben

 

library(XML)

# rename file to .zip
my.zip.file <- sub("xlsx", "zip", my.excel.file)
file.copy(from = my.excel.file, to = my.zip.file)

# unzip the file
unzip(my.zip.file)

# unzipping produces a bunch of files which we can read using the XML package
# assume sheet1 has our data
xml <- xmlParse("xl/worksheets/sheet1.xml")

# finally grab the hyperlinks
hyperlinks <- xpathApply(xml, "//x:hyperlink/@display", namespaces="x")
BenMoss
ACE Emeritus
ACE Emeritus

Actually the resulting hyperlink data sits in the 

 

\worksheets\_rels\[SheetName].xml.rels file which you can import and parse with Alteryx.

 

Ben

BenMoss
ACE Emeritus
ACE Emeritus

Fun challenge, see attached. With this you don't have to convert the file to a .zip first as we can just bring the .xlsx in with alteryx reading it as a zip (which it is).

 

It assumes that the hyperlink column is A.

 

Ben

sdevireddy
6 - Meteoroid

Hi jdmalagon,

 

Did you got any solution for this?

 

Regards,
Srinivas.

szkz
6 - Meteoroid

I have similar case where attached is the input excel file where column document name and document number is having hyperlink attached to it.  Need help in related to how to retain hyperlinks in those two column in the output excel file

Labels